Creating a substring from record ID

[Site admin (Peter MacIsaac)]

I have created a user identify model that contains multiple parts e.g  2003-1-1  (while meaningful identifiers are rarely needed - this was a special case to enable the automated creation of a participant ID from data entered during family registration)

Now I need to parse this ID to extract the first 4 characters (the family ID).

The following solution was provided by Luke Stevens (Murdoch Childrens Research Institute)

This can be done using a combination of

(1) the dynamic query (SQL) question type (it will create a drop-down list using the data inside your projects or another SQL table,  using the Dynamic Query (SQL)? You need administrator help for this one) and

(2)  a SMART Variable (this is new from REDCap version 8.4.0)

The SQL query to insert in the dynamic query is:

select "id", substring([record-name],1,4)

This query will look up the open record -select "id",  then extract the values in the string for the record id using the [record-name] smart variable, for values in positions 1-4 of the value.

Then create a hidden variable in the next form and pipe the value from the selected SQL query.  

This is a bit convoluted but does  enable the value to then be written to the database.