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 form 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.

Might be better to have separate columns for study, form and question (field, in REDCap parlance).

DRAFT database table definition:

CREATE TABLE sql_fields (
id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
project VARCHAR(100),
form VARCHAR(100),
field VARCHAR(100),
value VARCHAR(100),
label VARCHAR(100),
sort_order TINYINT UNSIGNED,
INDEX reference (project,form,field),
INDEX sort_order_1 (sort_order),
INDEX sort_order_2 (sort_order,label),
KEY value_key (project,form,field,value),
KEY label_key (project,form,field,label)
);

Entries into the table should therefore use the project_name from the REDCap redcap_projects database table (usually an all-lower-case rendition of the project title with underscores for spaces) for 'study', form_name and field_name from the redcap_metadata table for 'form' and 'field' respectively.

project_name is easy, because you can search for it in the redcap_projects database where project_id matches (look at the URL for the project to see the "pid=XX" attribute. field_name is kind of easy, because you can search for it in the redcap_metadata table, where project_id matches and the human-readable string is in element_label.

To find the form_name, you need to look for entries in the redcap_metadata where the project_id matches, the form_menu_description is not null (it will have the name of the forms in human readable format). Basically the first element in each form has a value in this column, and all other fields do not.

By using foreign keys we could force adherence to the REDCap object model and maintain the references even if the source data changes (although the SQL query itself would need to be updated in this situation, unless you built a very complex query).

But I think you can only use a foreign key if the corresponding column holds unique values, and that isn't true for the form name or field name columns in redcap_metadata - fields and forms only have to be unique within a particular project, not across the whole database. So maybe we can't have foreign keys for all three.

And foreign keys cause all sorts of MySQL errors unless the columns are identical in hidden ways. Easier for the time being not to use them.

Need to check with the REDCap data model. I've asked for more information. In the meantime the above would work, but in a limited way, with no updating for field or form names.

The other approach is to create a project, with a data collection form to collect values and labels, and then query the redcap_data table for those entries. This is popular with the REDCap community but I (NH) really dislike it, as it puts metadata in the data table, which seems to be contrary to the database model of REDCap.

To begin with, we will use the table definition above, and do manual lookups for the project, form, and field names.

Re-use of option list

Of course, sometimes you want to use the same list more than once. In this case, the form and field names in the database should relate to the question which makes FIRST use of the list.

Loading data into the table

If you have more than a couple of elements to add, use LOAD DATA LOCAL INFILE. This isn't entirely straightforward.

Prepare the text file. Match up the columns and populate with the correct project, form and field names. Save it from excel as 'tab delimited text' (comma separated doesn't work because there are commas in the text. Then open it in an editor and remove any / all quote marks. Now upload to the server.

Firstly, because it represents a security risk, you have to enable local loading, by starting your mysql client connection with the option " --local-infile".

Secondly, from within mysql connection, do something like:

LOAD DATA LOCAL INFILE '/home/nick/sql_insert_medications.txt' INTO TABLE sql_fields 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
(id,project,form,field,value,label)
;

Display order

As per #173 (the first use case / ticket for SQL fields in our REDCap) Daisy asked to have one specific option (in this case 'Other') elevated to first in the list. This seems a generalisable requirement (e.g. country might well offer UK before an alphabetised list. Best way to deliver would be to have a 'sort order' column, and use that as the first order by column, reverting to alphabetical as a second order by column, if desired.

Table definition above has been edited. Amendment in test.