REDCap automatically populate fields from earlier records

Question

I want to populate a dropdown menu with what was inserted in that field in early records (not forms with piping).

Specifically, I have a field named "Journal". If someone before recorded the journal "Metabolomics", I want it to appear in the dropdown menu (or something similar).

If the journal is new, I would add a text field where the new journal can be added.

Thank you for your help

question from:https://stackoverflow.com/questions/65648281/redcap-automatically-populate-fields-from-earlier-records

Answer 1

If I understand you properly, you want people to select from a drop-down list and if the correct option doesn't exist you want to allow them to enter it in themselves. This can be done with a Dynamic SQL query field, which only an administrator can configure, so you'll need your local admin to do this for you.

Essentially you'll have a Dynamic SQL field (which I'm calling [journal_query]) to look up distinct values already entered in, and a text box field ([journal_other]) to enter in a new value if the dynamic SQL field doesn't contain the desired option.

The [journal_query] SQL field has this query:

SELECT DISTINCT value FROM redcap_data WHERE project_id = [project-id] AND field_name = 'journal_other' UNION SELECT 'Other';

Which displays all existing values and adds an 'Other' option in the list.

And the [journal_other] field has branching logic to show only if [journal_query] = 'Other'.

A complexity is that the data now exists in only one of two fields, so a third field could be used to combine the two, selecting the value of [journal_other] if the value of [journal_query] = 'Other', and otherwise, selecting the value of [journal_query]. If you're on a recent version and have access to the @CALCTEXT action tag, then you would create a text entry field (this field I would call [journal] since it will always contain the correct value for the record), and apply this action tag:

@CALCTEXT(if([journal_query]='Other',[journal_other],[journal_query]))

Then the value of [journal] (rename fields as necessary) contains the respondent's selected or manually entered journal type, which you may use for piping or for reporting.