Optimizing Real World Data Collection: Routine Maintenance of Prospective Records


Farees Saqlain, Sophia Z. Shalhout, David M. Miller


April 27, 2020

A proposed workflow for monitoring completion of retrospective records in a patient registry hosted on REDCap

Objectives for the Optimizing Real World Data Collection Tutorial Series


Overview of REDCap

Routine Maintenance of Prospective Records: Tutorial Overview

  • In this post we provide a workflow for regular maintenance of prospective records in a patient registry on REDCap.
  • After this lesson, you will:
    • Understand the utility of creating fields in a separate data metrics instrument for monitoring updates to clinical data instruments in prospective records.
    • Understand how to implement a system of quarterly screens to ensure prospective records remain up-to-date.
  • Skill Level: Advanced

Challenges with Prospective Records

  • Recall that prospective records are associated with patients who are under active follow-up during data collection.
    • Data collection can never truly be completed while active follow-up continues.
    • Instead, the record can only be brought up-to-date. After time has elapsed, and the clinical course has continued, the record must be updated once again.
  • This creates a significant challenge for the Data Manager of a large patient registry:
    • Without a systematic approach to prospective record maintenance, potentially hundreds of prospective records may contain at best incomplete and at worst invalid data as clinical courses progress and evolve.

Date of Validity of Prospective Records

  • The Data Manager needs a way to track the overall Date of Validity for a record.
    • This is the most recent date as of which all the data contained in the prospective record was fully updated.
    • However, since individual instruments may have been updated at different times, the Date of Validity for the record equals the earliest of the various dates of update among these individual instruments.
      • Consider a prospective record in our generic registry illustrated in the figure below. The Patient Characteristics, Presentation and Initial Staging, and Lesion Information instruments were entered and validated on Date A, prior to the patient undergoing a surgical resection. After the procedure, the Data Abstractor enters the newly generated pathology reports into the Pathology Instrument, which the Data Manager then validates on Date B. However, the Data Manager must certify that the other instruments are still valid to bring the Date of Validity forward from Date A to Date B.
  • How involved the instrument validation process is will vary by record and by instrument. If the Data Manager is not aware of any changes required for an instrument (for example the Patient Characteristics instrument may rarely need updating in the middle of the clinical course for a typical patient) updating the status of the instrument will be as simple as a single click, as described below.

Data Metrics Instrument

  • To track Date of Validity for the overall record, and date of update of individual instruments, we developed a module of fields in a separate Data Metrics Instrument.
    • Designating the record as prospective opens a series of fields corresponding to each instrument in the registry.
    • As demonstrated above, the Data Manager inputs a datetime whenever they validate a particular instrument. This is as simple as clicking the “Now” button.
      • For example, Patient Characteristics was last validated on January 7th, 2020, at 9:30 AM.
    • If all the instruments have been updated at some point, the instrument generates an overall Date of Validity for the record, displayed in the purple notification rectangle.
      • This equates to the earliest date of updating across all 4 instruments.

A Limitation of REDCap

  • A limitation of REDCap at present is the lack of support for date manipulations in calculations.

  • Since dates cannot be directly compared, a workaround is to compare dates as intervals from a common reference timepoint using the datediff() function.

    • datediff() calculates the interval between two dates.
      • datediff(Date1, Date2, "units", "dateformat", returnSignedValue)
      • units
        seconds "s"
        minutes "m"
        hours "h"
        days "d"
        months "M"
        years "y"
      • dateformat
        Year-Month-Day "ymd"
        Month-Day-Year "mdy"
        Day-Month-year "dmy"
      • returnSignedValue
        function will returned signed interval, ex. negative if Date2 precedes Date1 true
        function will return absolute value of interval; default setting if parameter omitted false
      • Source: REDCap Help & FAQ
  • For example, the calculation to assess the Date of Validity of this record is given below, using New Year 2020 as the reference timepoint (the min() function, which returns the minimum of a series of values is also used below):

    [validityinterval] = if(([pro_pt_update] <> '' AND [pro_pr_update] <> '' AND [pro_li_update] <> '' AND [pro_path_update] <> ''), min(datediff("01-01-2020 00:00",[pro_pt_update],"m","mdy"),datediff("01-01-2020 00:00",[pro_pr_update],"m","mdy"),datediff("01-01-2020 00:00",[pro_li_update],"m","mdy"),datediff("01-01-2020 00:00",[pro_path_update],"m","mdy")), '')
  • To display the Date of Validity in the purple notification box requires creation of descriptive fields for each instrument, each with the piped-in date of update for that instrument. The display of these notification fields is then conditioned on whether or not the overall calculated minimum time interval (equating to the Date of Validity) equals the time interval for that instrument’s date of update (i.e. whether or not that particular instrument was updated the earliest).

  • Workarounds for REDCap’s internal limitations are at times cumbersome, but the advantage they confer is the ability to view basic metrics and interact with dashboards internally within the REDCap interface, rather than through data exports with external packages. This can streamline the workflow of the Data Manager.

Conclusion of Follow-up

  • Documented clinical follow-up for prospectively collected patients may conclude due to a variety of reasons including patient expiry or moving residence.
  • Follow-up status for prospective patients is also tracked in the Data Metrics Instrument.
  • If follow-up has concluded, the Data Manager can complete a final validation check and designate the record as finally complete and validated (i.e. “sealed”).
    • The filtering parameter [pro_complete] = 1 can be used to select out prospective patients with sealed records.

Interval Screening Dashboards

  • With the Date of Validity calculation, we can generate dashboards using the Custom Record Dashboard feature in REDCap to screen for records that may need updating.
  • For example, you might execute a Quarterly Screen, with four dashboards to cover a calendar year.
  • The filtering logic for each quarterly dashboard compares the Quarter End Date (3/31/2020 for Quarter 1, 2020) with the Date of Validity of each prospective record.
    • If the the Date of Validity is earlier than the Quarter End Date, the record is included in the dashboard.

    • Also included in the dashboard are prospective records that do not have an associated Date of Validity as they contain instruments that have never been brought up-to-date at any point.

    • As soon as the Date of Validity has been brought forwards to at least the Quarter End Date by Data Manager validation, the record will fall off the dashboard.

    • The code for filtering logic for a Quarter 1, 2020 screening dashboard is given below as an example:

      [capture_type] = 1 AND ([validityinterval] < datediff("01-01-2020 00:00", "03-31-2020 00:00","m","mdy") OR [validityinterval] = '') AND [pro_complete] <> 1
      • `[capture_type] = 1 selects prospective patients only.
      • OR [validityinterval] = '' includes records who do not yet have a Date of Validity, since all of their instruments have not yet undergone an initial data validation.
      • AND [pro_complete] <> 1 screens out records that have been completed and sealed.


  • Maintenance of prospectively collected records is a complex and resource intensive undertaking. One strategy for ensuring validity of records as clinical courses move forward in time is tracking an overall Date of Validity for each individual record. While doing this internally in REDCap requires a workaround, the ability for the Data Manager to view metrics and construct dashboards while in REDCap provides a powerful means for quality control.

Next in our series of tutorial posts on Optimizing Real World Data Collection: Flagging Prospective Records for Future Updates within a Patient Registry in REDCap