|
|
|
|
Example - using pivot table keys
The following example illustrates a mapping scheme that uses the Pivot Visit key type to group lab results by visit date. In this scheme, data from three forms in two visits is mapped to the t_TABLE1 target table:
- The dates in FORM1 are both mapped to the LABDATE column.
- The text box controls in FORM2 and FORM3 are all mapped to the LABRSLT column.
The following figure illustrates the mapping of controls to columns in the t_TABLE1 target table.
T_TABLE1 is defined with a key type of Pivot Visit, and the LABRSLT column is defined as the pivot column. Because the key type is Pivot Visit, the components of the control path used to determine the composition of the pivot set are PatientID and VisitID. The date control and the four text box controls from the forms in Visit 1 have these IDs (P1 and V1) in common and thus make up one pivot set. The date and text boxes from the forms in Visit 2 also have common PatientIDs and VisitIDs (P1 and V2), and thus make up the second pivot set.
The following figure summarizes the characteristics of the table, column, and control path mapping definitions. In fact, this figure illustrates the layout of the InForm software database table PF_CDDCOLUMNMAPPING, which holds CDD mapping definitions.
When the InForm application inserts data from the first pivot set into the t_TABLE1 target table, it creates a row for each data value mapped to the pivot column, LABRSLT. Then, it populates all non-pivot columns with the same date value. Note that in CDD target tables, date and time control values are stored in several different columns, according to the composition of the control and its completeness. In this example, mapping the date to the LABDATE column results in entries in the LABDATE_DT and LABDATE_STR columns. When the pivot set key changes on the data from the second visit, it creates a row for each Visit 2 LABRSLT value and fills in the LABDATE_DT and LABDATE_STR columns with the date of the second visit. The Data Label property specified for each control path mapping definition appears in the LABEL column. The following figure illustrates the results of the data insert into t_TABLE1.
Note: This type of CDD mapping does not support itemset data. To create CDD mappings for controls that occur in itemsets, use one of the non-pivot table key types.
