query the date a form's COMPLETION status changed

[Tom Maloney]

Hello Experts,

Is there a straightforward method to export the dates (as in the project LOGGING entries) a form's status changed to (or from) COMPLETED?

Thanks!

-Tom

[Jeff Theobald]

Hi Tom,

As best I have figured, it is mostly a matter of exporting a subset of all logging data, and then further narrowing that export down to only show exactly what you care about. 

As you probably know, each form has an automatically-created status variable (the REDCap name for the form + "_complete"). This variable can be: blank ("gray" form status , "0" (Incomplete), "1" (Unverified), or "2" (Complete).

Here are a couple of thoughts:

• Manual approach: 

1. 1. From the Logging page, select appropriate filters. 
      • For this, you'd want the "Filter by event" setting changed to "Record created-updated-deleted" ("_complete" variables can be changed when records are created or updated)
      • Adjust filters for time range, DAGs, records, users as needed.
   2. When the Logging page has refreshed with your filter settings, use the export button for "All pages using current filters"
   3. You now have a 5-column CSV with logged actions meeting your  REDCap filter criteria. You can open or import that into Excel or other CSV editor, then:
   • • Apply a text filter on the 4th column ("List of Data Changes OR Fields Exported") to only show you rows in which this column contains this string: _complete = . This should show you the date/time, user, and record ID for any time ANY of the "_complete" variables were changed. 
   1. 1. • Note that once a form's "_complete" status has been recorded, it's value will not be logged again unless the status variable is changed. This means that, after a form is saved as Complete, any of the other variables on the form may later be edited indefinitely, without the log showing anything for the "_complete" variable.
         • This column contains ALL of the data changes made by the user at this time (when form saved), so the cell could be quite full of extraneous (and potentially private) information.
         • You can adjust the text filter to be for a specific form and/or form status (e.g., demographics_complete = '2')
         • Caveat: it appears that records created via Data Import do NOT show the "_complete" variable being updated, unless the column was explicitly included in the import file. This may/may not need to be taken into account for your use case.

• API approach: 

1. 1. You can do steps 1 and 2 above through the API. The API Playground will give you the parameters, after you select what you want (equivalent to the filters on the Logging page). 
   2. Use Excel or some other tool to read and filter the exported log data to find what you want.

In short, it seems like what you want will always require some "offline" processing to find instances of exactly what you want (and this can be tricky and non-intuitive) -- the exported logs will almost always give you way more than you need, but it IS in there. I'm sure there are opportunities for making the "offline" processing better/more automated, etc. -- I don't love relying on Excel filters, etc. but this is the kind of thing I only need to do occasionally, so I haven't invested much time in trying to improve it. Maybe somebody else in the group has a better way to do this!

Jeff