|
|
|
|
Example - using non-pivot table keys
The following example illustrates a mapping scheme that uses the Patient to Section key type to insert lab results by section in the same row as the date on which they were collected. In this scheme, data from two sections of the same form is mapped to the t_TABLE1 target table:
- The dates in SECTION1_S and SECTION2_S are both mapped to the LABDATE column.
- The text box controls in ITEM2_1 in both sections are mapped to the LABRSLT column.
- The text box controls in ITEM3_1 in both sections are mapped to the LABRSLT2 column.
The following figure illustrates the mapping of controls to columns in the t_TABLE1 target table.
Table t_TABLE1 is defined with the Patient to Section key type. Its primary key consists of columns containing the PatientID, VisitID, VisitIndex, ItemsetIndex, FormID, and SectionID of each data point. It also contains target data columns for the LABDATE and for the two lab result items, LABRSLT and LABRSLT2. Because t_TABLE1 has a non-pivot table key type, no pivot column is defined. Any label data associated with the control path mappings is saved with the mapping definitions but will not be transferred to t_TABLE1.
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 application database table PF_CDDCOLUMNMAPPING, which holds CDD mapping definitions.
When the InForm application inserts data from the form into the t_TABLE1 target table, it creates a row for each control path in which the PatientID, VisitID, VisitIndex, ItemsetIndex, FormID, and SectionID differs from the previous one. The data transferred from the three items in SECTION1_S all have the same IDs and indexes. Their control paths do not differ until the Item component. Therefore, the InForm application inserts those items into a single row in the target table. The data transferred from the three items in SECTION2_S have the same IDs and indexes as each other but a different SectionID from the previous group of data points. Therefore, the InForm application creates a new row and inserts those items into the new row.
The following figure illustrates the results of the data insert into t_TABLE1.
When you create a CDD table definition, make sure that you choose a key type that includes all of the IDs required to uniquely identify each mapped data point. For example, if the key type is Patient to Form, and you map the same control occurring in two sections of the form to the same CDD column, the data value for the control in the second section will overwrite the first. To capture the values of both controls, you would need to choose at key type with a primary key that includes the SectionID or map the two controls to two different CDD columns.
