Function to parse eform_data values

  • Version
  • 2 Download
  • 1.36 KB File Size
  • 1 File Count
  • June 6, 2018 Create Date
  • June 6, 2018 Last Updated
An RbT can be used to extract data from any table. There are 2 eform tables: eform_values and eform_data.
The eform_values table is nice because it's just a set of var_names and var_values.  If your eform has 80 fields, there will be 80 records.
The problem is that to extract each field you need to write a SQL 'JOIN' with a unique alias.
The SELECT to get the value for the 'rvrped' (Rendez-vous Pediatric) is:
IF( efv_rvrped.var_value   = 'X', 'Yes', 'No' ) AS RDVPed,
You need a unique alias to get that particular pair of names/values:
LEFT JOIN eform_values efv_rvrped   ON efd.fdid = efv_rvrped.fdid   AND efv_rvrped.var_name   = 'rvrped'
Unfortunately, the maximum number of joined tables is MySQL is 61.
Solution:
A function that parses the eform_data record, finds the matching name="fldname" tag, then the corresponding value="fldvalue"
Usage is:
SELECT fn_efgetvalue( "rvrped", efd.form_data ) AS Value FROM eform_data efd WHERE fdid = 21 ;
If you don't have access to your mysql server, you will have to ask your Support people to add the function.
You also need to know the names of all the fields in your eForm...

Download
FileAction
fn_efgetvalue.sqlDownload