sqlredcap

Populate REDCap dropdown with data from outside source

I think this question may be somewhat similar to this one : REDCap automatically populate fields from earlier records

That said, in that question they were populating the dropdown with data from elsewhere in REDCap, my question pertains to outside of REDCap.

What exactly I'm trying to do:

At our institution we store a bunch of information in a clinical trial management software called "OnCore". That's not SUPER important for this question, besides the fact that we connect to it for various reasons through a couple different possible channels (sql, api, Snowflake, etc..).

At the same time, we're building a REDCap project where staff can keep track of deviations that happen in trials and what was done about it. The problem is, staff currently have to manually type into REDCap the protocol they are working on.

Let's say in OnCore a protocol is called LS-P-Joe. Well, in REDCap, someone might type "LS-p-jOe" or "Joe" or "LSPJOE" etc.. etc.. and it'll cause a problem when I go to merge the data later.

I'd love to have a dropdown where staff can only select one spelling of the word, unfortunately there are dozens if not a few hundred studies to choose from (I don't want to build them all into the REDCap project manually).

Which brings me to: is there a way to have a dropdown in REDCap that dynamically connects to an outside data source? Let's say through SQL query?

Solution
    1. Create a new REDCap project that contains only the legal protocol. No patient data points are needed.
    2. Use a simplified version of @Jangari's solution (that you referenced above). But your scenario doesn't require the UNION clause. I think you'll need a REDCap admin to implement the Dynamic SQL Field.
    3. Automate some script to periodically update the list of legal protocol names. Presumably you query OnCore (maybe with an Oracle driver) and update REDCap (see this list of packages that use REDCap's API).
    4. I suggest involving your site's REDCap admin as soon as possible. They can also ask questions in the API space on the REDCap Community Site.