Transcript Dynamic SQL

Dynamic SQL Field Type
Dynamic SQL Field Type
•
•
•
•
•
•
Create using the Online Designer or Data Dictionary
Populates a drop-down list of choices by querying the REDCap tables using SELECT
queries only.
The database table being queried must exist in the same MySQL database as the
REDCap tables.
ONLY REDCap Super Users may add/modify this field type.
Advantage of this field type is that it allows you to populate a drop-down from a
dynamic source (i.e. a database table) rather than a static
When constructing the query itself, only 1 or 2 fields may be used in the query.
– If only one field exists in the SQL statement, the values retrieved from the
query will serve as both the values AND the displayed text for the drop-down
that is populated.
– If two fields are queried, the first field serves as the unseen values of the
drop-down list while the second field gets displayed as the visible text inside
the drop-down as seen by the user.
Example 1
Queries Project (ID #1264) for the contents of
the Provider field and creates a dropdown list in
the current project.
SELECT record, value
FROM redcap_data
WHERE project_id = 1264
AND field_name = ‘faculty’
ORDER BY value;
Example 2
Queries Project (ID=1264) and concatenates Record
ID, faculty name and credentials) to create a drop
down list in the current project:
SELECT a.record,
CONCAT_WS(' | ',
max(if(a.field_name = 'record_id', a.value,NULL)),
max(if(a.field_name = ‘faculty', a.value, NULL)),
max(if(a.field_name = ‘credentials', a.value, NULL)))
as value
FROM redcap_data a
WHERE a.project_id=1264
ORDER BY a.record;