SQL fields in REDCap

A useful, but specialised, REDCap field type is the 'sql' type. It allows for a database query to populate a dropdown selection element.

Full details on the REDCap wiki at ​https://iwg.devguard.com/trac/redcap/wiki/SQLFieldType

Implementing the sql field requires that the table housing the data is stored in the same database as the REDCap tables.

Examples from the documentation include the following:

select value from redcap_data where project_id = 390 and field_name='institution' order by value

• Returns just one value per result, which will form both the label and the value for the drop-down selection element.

select record, value from redcap_data where project_id = 390 and field_name = 'institution' and record in (select distinct record from redcap_data where project_id = 390 and field_name = 'consortium_status' and value != '4') order by value 

• Returns two values per result, where the 'record' will become the value, and the 'value' will become the label of each option in the drop-down selection element.

There are essentially two types of sql field instances: (a) as an extensible, scalable version of the 'dropdown' data type, where the data to be read by the select query is essentially static, and (b) where the select query addresses data held in the REDCap_data table and generated as a result of REDCap data capture processes.

The former type allows for psuedo-dropdown elements in REDCap questionnaires, where the options can be added to without re-editing the data collection instrument. Remember that changes to the source SQL table would not be applied retrospectively to data collected through the REDCap questionnaire, so editing existing options once data collection is underway is NOT RECOMMENDED.

The second type allows for complex relationships between different studies in REDCap, where entries in one study can be dynamically linkes to entries in another study.

SQL fields in LCBRU REDCap inplementations

For those SQL fields created in LCBRU REDCap implementations which are not populated from other REDCap-derived data (i.e. not referencing the redcap_data table), the question arises of where to store the source data.

To avoid proliferation of tables within the LCBRU implementations of REDCap, it is proposed that a single additional table be created for all stored source data for sql fields. The table created will need to have at a minimum four columns: an object ID, a key to indicate which data collection instrument and which question the object relates to, the value and the label. Only the last two columns can be returned to the REDCap sql query.