Setting Up Conditional Overrides

Conditional overrides are a way to build in some light logic on export and import in Integration Manager. You can use conditional overrides to:

• Compare test field values with a fixed value, and replace a reference field value with a custom value or a field value if the condition is met.

• Replace reference field values with a custom value or a field value if the test field value contains a specific pattern of text (regular expression).

• Use regular expressions to search for a specific pattern of text in the reference field value, and replace this pattern with a custom value or a field value.

The reference field is the source field containing the values you want to override if the condition is met.

The condition that determines if the reference field value should be replaced is a simple logical expression with two operands and a comparison operator.

• The first operand is a test field, or if you select more than one test field, the sum of numeric test field values (Export only), or the result of concatenating text test field values. The test field and the reference field can be the same field.

• The second operand can be either a fixed value or a pattern (regular expression), depending on the selected comparison operator.

• The following comparison operators are available: Equals, Not Equals, Regex (regular expression), Contains, Not Contains.

  Use the Regex comparison operator to find test field values that contain a specific pattern of text. The condition evaluates as true if the test field value or the result of concatenating text test field values contains a portion of text matching the regular expression pattern. If the test field is the same as the reference field, you can use the Regex comparison operator to match and substitute a pattern of text in the reference field value with a custom value, instead of replacing the entire reference field value with that custom value. You may use capturing groups in the regular expression pattern and use these capturing groups for string substitution. For examples of conditional overrides using regular expressions, see Regular Expressions Use Case Examples.

Note:

For more information about regular expression, see https://en.wikipedia.org/wiki/Regular_expression and https://cheatography.com/davechild/cheat-sheets/regular-expressions/.

You should discuss your requirements with OpenAir Professional Services before you start using regular expressions in conditional overrides.

Conditional overrides are evaluated before field combinations. If you are using field combination to combine several source fields into one destination field, you can use conditional overrides to replace each source field values before the source field values are combined.

You can define multiple conditional overrides for the same field (reference field). Integration Manager evaluates each conditional override for every record included in the export or import in the order from the bottom to the top of the list. On export, conditional overrides with the Override after summing entries box checked are evaluated last. You should set the evaluation order carefully as the test fields used in one conditional override can be modified by a previous conditional override.

To set up conditional overrides:

1. In Integration Manager, locate the row corresponding to the record type and the direction required from the table listing the record types available for import from and export to a CSV file.

2. Do one of the following:

   • Click to select the row, then go to Options > Formatting.

   • Right-click the row, then click Formatting from the context menu.

   The Formatting window appears.

3. Click the Conditional Override tab.

   

4. Click Add.

   The Conditional Overrides window appears.

   

5. Select the reference field (the source field containing the values you want to override if the condition is met) To do so, select a field from the Select the reference field whose value will be overridden the dropdown options.

6. Select one or more test fields (the fields used as first operand in the condition determining if the reference field value should be replaced). Use the arrows to add fields to (arrow pointing right), or remove fields from (arrow pointing left) the Selected column. If you select more than one field, the logical expression compares the result of concatenating the test field values —or on export, the sum of numeric test field values— with the value or pattern you specify.

7. Specify the comparison operator and the value or pattern you want to compare the test values with under Override Condition.

8. (Export only) Check the Numeric comparison box if you are comparing numeric values.

9. (Export only) Check the Override after summing entries box if you want to and apply the conditional override to summarized grouped record data. For more information about summarizing grouped record data, see Accounting Settings.

10. Set the override value. To do so, do one of the following:

    • Choose and enter a Custom value.

      If you select Regex (regular expression) as the comparison operator, and if the test field is the same as the reference field, Integration Manager substitute the custom value for the portions of the reference field value matching the regular expression pattern. You can include capturing groups in the regular expression pattern and use these capturing groups for string substitution in the custom value. For examples of conditional overrides using regular expressions, see Regular Expressions Use Case Examples.

      (Export only) You can use any value even if the value is not a valid value for the field data type. To allow any value, independently of the field data type, check the Override field type box.

    • (Export only) Choose and select a Field value from the dropdown options.

11. Click OK to save the conditional override and return to the Formatting window.

    The Conditional override tab on the Formatting window shows a summary of the conditional override you added.

12. Add other conditional overrides as required.

13. After you add conditional overrides, you can:

    • Edit or delete any listed conditional overrides. To edit or delete a conditional override, click the conditional override, and click Edit or Delete.

    • Change the order of evaluation for your conditional overrides. To do so, click the conditional override you want to move up or down the list, and click Shift Up or Shift Down.

      Important:

      Integration Manager evaluates each conditional override one after the other following the order from the bottom of the list (first) to the top of the list (last). On export, conditional overrides with the Override after summing entries box checked are evaluated last. You should set the evaluation order carefully as the test fields used in one conditional override can be modified by a previous conditional override.

14. Click OK to save the running order of conditional overrides and return to the main Integration Manager window.

Regular Expressions Use Case Examples

The following example illustrate a possible use of regular expressions.

Leading zeros

In this example, the source field value is an integer with up to 3 digits and the destination field value must always be a 4-digit number converted to string with leading zeros. You can use a calculated field and conditional overrides to add 2 or 3 leading zeros depending on the number of digits in the source field value. To do so:

1. Add a calculated field with the name Leading Zero and the constant value 0 (zero). See Making Additional Information Available for Mapping (Calculated Fields).

   

2. Combine the Leading Zero and the source field (in this example Activity) into the destination field Activity Ref. See Combining and Splitting Information.

   

3. Create a conditional override with Leading Zero as reference field, Activity as test field, Regex as comparison operator, enter the pattern \b[0-9]\b, and the custom override value 000 (three zeros).

   The regular expression \b is a word boundary anchor marking either end of a continuous series of non-space characters, and [0-9] is a number digit. The pattern \b[0-9]\b matches any 1-digit integer values. The conditional override adds three leading zeros if Activity is a 1-digit integer.

   

4. Create a conditional override with Leading Zero as reference field, Activity as test field, Regex as comparison operator, enter the pattern \b[0-9][0-9]\b, and the custom override value 00 (two zeros).

   The conditional override adds two leading zeros if Activity is a 2-digit integer.

   

Note:

In this case, the evaluation order of conditional overrides does not matter.

Changing Dates to First Day of the Month

In this example, the source field value is a date field in the MM/DD/YY and the destination date field value must always be the first day of the month. You can use a conditional override to change the DD component to 01 in the source date field.

To do so, create a conditional override with the source date field as both reference field and test field, Regex as the comparison operator, enter the pattern /[0-3][0-9]/ and the custom override value /01/.

Number Format for Import

When importing information into OpenAir from a CSV file, numeric values must use the following format:

• Decimal separator: dot (.)

• Thousands separator: none

Important:

Imported numeric values must not contain commas.

Imported numeric values should have the same maximum number of decimal digits (decimal precision) as the destination field in OpenAir. Otherwise, values will be rounded to the decimal precision of the OpenAir field.

You can use conditional overrides to change the format of the imported numeric values, if required. Examples:

• To change the decimal comma to a decimal dot, create a conditional override with the source number field as both reference field and test field, Regex as the comparison operator, enter the pattern , and the custom override value ..

• To remove commas separating groups of thousands, create a conditional override with the source number field as both reference field and test field, Regex as the comparison operator, enter the pattern ,, and leave the custom override value empty.

To verify the format of the OpenAir field, refer to the OpenAir Data Dictionary. See OpenAir Data Dictionary.