Combining and Splitting Information

You can combine several source fields into a single destination field (Import and Export), combine separate date and time fields in the source CSV file into a datetime or timestamp field in OpenAir (Import only), or split a source field into two or more destination fields (Import only).

Combining Several Source Fields into a Single Destination Field (Import and Export)

Combining Date and Time Fields for Import

Undoing a Field Combination

Splitting a Source CSV Field into Several OpenAir Fields for Import

Undoing a Field Split

Combining Several Source Fields into a Single Destination Field (Import and Export)

You can combine information stored under several columns in your CSV file and import it into a single OpenAir field. You can also combine information stored in several OpenAir fields and export it to a CSV file under a single column. You can combine information in either one of three ways:

• Concatenate — You can join two or more text strings into one string and specify a separator to use between the source strings in the destination string.

• Add — (Export only) You can sum numerical values in two or more OpenAir fields and use the total to populate the value under the destination column in the CSV file.

• Multiply — (Export only) You can multiply numerical values in two or more OpenAir fields and use the product to populate the value under the destination column in the CSV file.

Note:

To modify a combined field mapping, or exclude the fields from the information exported or imported, you must undo the field combination first. You can then combine the fields again, or remove the fields from the export or import as required. For more information about undoing a field combination, see Undoing a Field Combination.

To combine several source fields into a single destination field:

1. On the Field Mapping window, line up the source fields you would like to combine so they are adjacent. Drag each field to change the order they appear in the list if necessary.

   The source fields are under OpenAir for export field mappings, or under Source for import field mappings. If you want to concatenate the source field values, line them up in the order you want them to be concatenated. Source fields lined up top to bottom are concatenated left to right in the destination field.

   Note:

   For steps to go to the Field Mapping window, see Mapping OpenAir Fields to CSV Columns.

2. Hold Ctrl and click the fields you want to combine.

   Selected fields are highlighted in blue.

3. Click Combine.

   The Combine fields window appears.

4. Do one of the following:

   • For exports, enter the name of the CSV column that will hold the combined OpenAir field values in the Name of the combined fields box.

     

   • For imports, select the OpenAir field that will hold the combined CSV values from the Select an OA field to combine into dropdown options.

     

5. (Export only) Choose the combining method. Three combining methods are available: Concatenate, Add, and Multiply.

   • You can combine any OpenAir field values using the Concatenate method, regardless of the combined field data types — non-string values are converted to string before being concatenated.

   • When combining field values using the Add or Multiply methods, the combined OpenAir field values should be either numbers or decimals. The resulting value is 0 otherwise.

   Note:

   For import field mappings, you can only combine source fields using the Concatenate method.

6. If you are combining fields using the Concatenate method, select the separator to use between field values in the resulting string from the Specify type of the fields separator dropdown options. If you select the Custom separator option, enter the separator in the text box, or leave it empty to combine the fields with no separator.

7. Click OK to return to the Field Mapping window.

   The Field Mapping window shows the combined fields with a blue branched arrow.

   

8. Click OK to save the field mapping settings and return to the main Integration Manager window.

Combining Date and Time Fields for Import

You can combine separate date and time fields in your source CSV file into fields containing both date and time parts when importing information into OpenAir. This may be useful if date and time information are recorded in separate columns in your CSV file and you want to import information into OpenAir fields such as created (datetime data type) or updated (timestamp data type), for example.

To combine date and time fields for import:

1. On the Field Mapping window, click Combine Date.

   Note:

   For steps to go to the Field Mapping window, see Mapping OpenAir Fields to CSV Columns.

   The Field Mapping Windows shows blue branched arrows for each datetime or timestamp fields in the OpenAir record type.

   You can undo the field combination for any of OpenAir datetime or timestamp fields if you do not need to import the information, or if the column values already contain both date and time parts in the source CSV file. For more information about undoing a field combination, see Undoing a Field Combination.

   

2. Drag the date and time fields in your source CSV file to the end of the blue branched arrow for the corresponding OpenAir datetime or timestamp fields. The time field must be below the date field.

   

3. Click OK to save the field mapping settings and return to the main Integration Manager window.

Undoing a Field Combination

You can undo a field combination at any time after you combine fields. To modify a combined field mapping, or exclude the fields from the information exported or imported, you must undo the field combination first. You can then combine the fields again, or remove the fields from the export or import as required.

To undo a field combination

1. On the Field Mapping window, click the field combination you want to undo.

   Note:

   For steps to go to the Field Mapping window, see Mapping OpenAir Fields to CSV Columns.

2. Click Uncombine.

3. Click OK to save the field mapping settings and return to the main Integration Manager window.

Splitting a Source CSV Field into Several OpenAir Fields for Import

You can split a field from the source CSV file into two or more fields when importing information into OpenAir.

Note:

To modify a split field mapping, or exclude the fields from the information imported, you must undo the split field first. You can then split the fields again, or remove the fields from the import as required. For more information about undoing a field split, see Undoing a Field Split.

To split a source CSV field into a several OpenAir fields for import:

1. On the Field Mapping window, line up the OpenAir fields you want to split the information into so they are adjacent. The source CSV field value is split left to right and will populate the selected OpenAir fields top to bottom. Line up the source CSV field containing the information you want to split with the top OpenAir field that will receive the information. Drag each field to change the order they appear in the list if necessary.

   Note:

   For steps to go to the Field Mapping window, see Mapping OpenAir Fields to CSV Columns.

2. Hold Ctrl and click the fields you want to split the information into.

   Selected fields are highlighted in blue.

   

3. Click Split.

   The Split fields window appears.

   

4. Select the CSV field containing the information to split.

5. Choose a method under Split options.

   • Split using delimiter — Select a delimiter or specify a custom delimiter.

   • Split using character range — enter the character range for each OpenAir fields.

6. Click OK to return to the Field Mapping window.

   The Field Mapping window shows the split fields with a red branched arrow.

   

7. Click OK to save the field mapping settings and return to the main Integration Manager window.

Undoing a Field Split

You can undo a field split at any time after you split fields. To modify a split field mapping, or exclude the fields from the information imported, you must undo the field split first. You can then split the fields again, or remove the fields from the import as required.

To undo a field combination

1. On the Field Mapping window, click the field split you want to undo.

   Note:

   For steps to go to the Field Mapping window, see Mapping OpenAir Fields to CSV Columns.

2. Click Clear Splitting.

3. Click OK to save the field mapping settings and return to the main Integration Manager window.