Data Mapping on Collect Forms

<< Click to Display Table of Contents >>

Navigation:  Collect > Collect Enterprise > Template Designer Page > Forms and Fields >

Data Mapping on Collect Forms

Depending on the format used, mapping to the database is provided by default but can also be customized at will. To edit or build the data mapping on a form, click the Data Mapping button on the Fields section of the Form Setup page on the Template Designer page.

col-form-setup_data_mapping_zoom55

The Data Mapping window opens with the form name listed in the window header. If data mappings have already been created for the form, the first mapping will be displayed and the number of mappings will be displayed on the right side of the Data Mapping window header. If the form does not have data mappings, the Create New Mappings window will open.

col-data_mapping_window-example_ph_zoom60

Fields in the target format table are displayed in the Field Mappings pane. This allows for easy review and editing of data mapping on the entire form. The search bar allows users to enter search strings for field names as well as value and active attribute formulas.

 

If the data mapping can link to other format tables, the 'Type' and 'Source Field' drop-down menus will be active below the target format table.

Col-Data_Mapping_FileLinking

The data mapping attributes, or values, for each field can be customized through the Formula Builder. Click the Value button to open the Formula Builder and create a data mapping. For any field with a data mapping (i.e., formula) assigned to the value property, the active property is automatically set to TRUE. The Value button is blue and displays the formula. The Active button is also blue and can have a formula assigned.

 

col-data_mapping_window-example_active_value_zoom60

 

To remove a formula from any field or the table, open the formula builder and delete the existing formula. The data mapping is removed and the button (Active or Value) will be white, indicating an unpopulated status.

 

Note: A linked mapping sets the object EQuIS Unique Identifier (EUID) of the created row to the EUID of the row created by the linked mapping. Do not use a linked mapping with a previously configured mapping that uses the table level Variables attribute feature because an error will be logged and the linked mapping will be ignored. Since the Variables attribute generates a set of mappings, it does not have a single object EUID, and thus, cannot be used by the linked mapping, which needs a single object EUID to link correctly.

 

To access a different mapping on the form, select the button displaying the name of the current mapping, which opens the Mapping Name window. Use the advanced search box or scroll to find and select the desired mapping.

Col-Data_Mapping_Name_Window

Advanced Search – Click the down arrow to expand the advanced search dialog window. Items that can be searched (e.g., ID, Target Table) will appear in the first drop-down. By default, the first drop-down will be selected as Any. The Any option will search all the items to match the expression with any of the properties. The second drop-down allows the user to select an operator (e.g., Starts With, Ends With, Equals, Contains). By default, the operator is set to StartsWith. The operator defines how the expression is matched to the items being searched. Enter the expression to be searched in the text field. Multiple expressions can be set by clicking the Col-Data_Mapping_Advanced_Search_Plus_Button (plus button) below the first expression. Expressions can also be removed/cleared by clicking the Col-Data_Mapping_Advanced_Search_Minus_Button (minus button) to the left of each expression. To collapse the advanced search dialog, click the Col-Data_Mapping_Advanced_Search_Up_Arrow (up arrow) at the bottom of the advanced search dialog. Note that collapsing the advanced search will not clear search expressions that have been configured.

Col-Advanced_Search_Dialog

Any data mapping can be easily checked for anything that might be set up incorrectly by clicking the Validate button. A message dialog will display any errors, if detected, to guide the user through what needs to change.

 

Click Save to save changes applied to any data mapping. Click OK to close the Data Mapping window.

 

 

Data Mapping Attributes

 

Data mapping attributes are formula derived values that control how mappings behave. Any attribute will display as blue when changed from default properties. A description of ow each attribute affects the functionality of the data mapping is provided below.

 

Active – Active is a Boolean attribute where the configured formula logic will determine whether a pre-population record is created (i.e., the formula evaluates to TRUE) or not (i.e., the formula evaluates to FALSE). For any field with a data mapping (i.e., formula) assigned to the value property, the Active property is automatically set to TRUE.

 

col-data_mapping_window-example_active_zoom60

 

Global Active – Global Active is a Boolean attribute where the configured formula logic will determine whether a pre-population record is created (i.e., the formula evaluates to TRUE) or not (i.e., the formula evaluates to FALSE). To assign a Global Active attribute formula to the entire table, select the Active button to the right of the Target Format Table drop-down. With one data mapping in a template, the Global Active attribute will be more manageable than using the Variable attribute.

 

col-data_mapping_global_active_zoom60

 

Key – If the field is a primary field key, the Key box will by checked by default and the field name is displayed in red text. Deselect the check box to remove the key designation from the field. Any any field can be selected to make that field a primary key field.

 

col-data_mapping_primary_key_zoom60

 

Note: The Key property of a field mapping determines whether the field is part of the table mappings search key. The table mappings search key is the combination of all key fields and must be the same for all mappings that target the same table. This key is used when multiple mappings need to compliment each other in order to populate data. For example, if the location table is targeted from two separate forms, the SYS_LOC_CODE field could be used as the key so data from both forms would be combined on the rows that have the same SYS_LOC_CODE value. It should be noted that if search keys are defined, any duplicate rows that have the same key values will override each other. Thus, it is possible that the data in the first created row will be over-written by the data in the second created row. This would mean that the duplicate row errors would not exist in the generated EDD, potentially causing data loss. In general, keys should not be used for field mapping unless multiple mappings are targeting the same table and data needs to be combined from both source tables. In that case, the search keys must be that same for all mappings.

 

Variables – The Variables button is a table-level attribute that allows users to specify variable replacements in their mappings. This attribute is useful when numerous data mappings with similar structures are required and eliminates the need for numerous mappings to the same format table. When the Variables attribute is set, the mapper will iterate over each of the JavaScript Object Notation (JSON) objects in the attribute and replace each of the fields specified by the keys of the object with the corresponding value. See the Variables attribute example for more information about this feature.

 

Note: The table-level Variables attribute feature is executed prior to the table-level Active attribute.

 

 

Create New Data Mapping

 

To add a new data mapping, click the Create New Mapping button in the upper portion of the Data Mapping window.

Col-Data_Mapping_New-Button

The Create New Mappings window opens and contains the sections (tables) of the format assigned to the template. Use the search bar or scroll to find and select the desired format table.

Col-Data_Mapping_New

The Data Mapping window updates to have the selected format table assigned as the target format table and as the mapping name. In the example below, the LocationParameter_v1 table was chosen.

Col-Data_Mapping_New2

Fields in the target format table are displayed in the Field Mappings pane without any formulas assigned. Create any data mappings using the formula builder as described earlier in this article. Click Save to save the new data mapping. Click OK to close the Data Mapping window.

 

Note: A database field can be mapped multiple times. For example, the SYS_LOC_CODE can be mapped to HISTORICWATERLEVEL_V1 and also to HISTORICALOCATION_V1.

 

 

Copy Data Mapping

 

Data mappings can be copied for quick and minimal changes, which is helpful when adding multiple data mappings that require similar structure for the same target format table. An example is when mapping field parameters (e.g., pH, ORP, DO, etc.) for a Groundwater Form to the format table FieldResults_v1. The mapping structure will be the same for all the field parameters, but the values for the parameters will change depending on which parameter is being mapped.

 

To copy an existing data mapping, ensure that the current mapping is the one to be copied and then click the Copy Mapping button in the upper portion of the Data Mapping window.

Col-Data_Mapping_Copy-Button

The Data Mapping window will have all the same data mappings, but the mapping name will be automatically modified. For the example above, the new mapping name is WaterLevel_V1-1. Edit the mapping name and any formulas. Click Save to save the copied data mapping. Click OK to close the Data Mapping window.

 

See All Data Mappings

 

The See All Mappings button allows users to see ALL data mappings on a form in a read-only table view, rather than viewing one data mapping at a time. Users can easily compare and verify data mappings without having to toggle between individual data mappings. This tool can be especially useful for forms with numerous data mappings or with many fields that have similar data mappings.

 

Select the See All Mappings button in the upper portion of the Data Mapping window to open the Mapping Grid window.

Col-Data_Mapping_See-All-Button

Col-Data_Mapping_See-All-Grid

Click the Download button to save all the data mappings to a single CSV file.

 

 

Rename Data Mapping

 

Any data mapping can be renamed by selecting the Rename Mapping button in the upper portion of the Data Mapping window.

Col-Data_Mapping_Rename-Button

A dialog box will open allowing the user to rename the data mapping. Modify the name as desired and click the OK button.

Col-Data_Mapping_Rename-Window

 

Delete Data Mapping

 

To delete the current data mapping, click the Delete Mapping button in the upper portion of the Data Mapping window.

Col-Data_Mapping_Delete-Button

A message dialog will be displayed to confirm the action. Select the Yes button and the current data mapping will be removed.

Col-Data_Mapping_Delete-Window