Applying conditions to mappings

In some integrations, it is helpful to add conditional processing to a mapping. For example, suppose that you are mapping a source zip code field to a target zip code field. If the source zip code field is empty, you might want to fill the target field with 99999. To do this, you would specify an expression that tests the zip code source field to determine if it is empty, and if it is empty, inserts 99999 into the zip code target field.

The Data Mapper supports expressions that are similar to Microsoft Excel expressions, but does not support all Microsoft Excel expression syntax. You can define zero or one condition for each mapping.

The following procedure gets you started with applying conditions to mappings. As you work with mappings and conditions, you can perform the required steps in the order that is most convenient for you.

Procedure

1. Map a source and target field. This can be a one-to-one, many to one, one to many or a many to many mapping.
2. Create a new mapping in the Scriplets panel and click the Enable/Disable conditional mapping expression button :

3. Select the expression field and click the edit button at the end of the expression field. The Expression dialog box will display to specify the conditional expression that you want to apply to the mapping:

4. Due to the often complex nature of Xml and Json documents, the expression editor allows you to select which source fields you want to use in the expression and apply a field identifier to them. To add additional source fields, you search and select the fields from the source document(s) on the left and move them to the right side. For each field you move from left to right a field ID will get assigned to that field. While writing the expression you use the field ID instead of the field name. In the above image, the description field was automatically provided the identifier @1 as it was the source field in the mapping.
   
   In the example below, the description and price fields have been moved from the Source(s) to the Available Field(s) list, each assigned with a field ID:

5. In the Expression Editor, as you specify the expression, you use the field id to refer the field name. When you enter the field id @1 in the expression editor, the field name associated it will be displayed in the area below it:

6. Add the conditional expression IF (@2 <= 0, ‘This item is free’, @1)
   
   If the price is less than or equal to zero, set the description to “This item is free”, else set the description to the source’s description field:

7. When you add a field to the expression via the Expression Editor, the Data Mapper adds that field to the mapping:

8. To preview the data mapping results, click the Show/Hide test preview button  in the Scriptlets window. This displays a text input field on the source field and a read-only result field on the target field. Enter values in each of the source field’s text field, then click the Run preview test button to display the mapping result in the read-only field on the target field:

9. You can add one or more Transformations to any of the source and target fields in the selected mapping by clicking Add action button next to the field in which you want to apply a transformation and then selecting the desired transformation from the pull-down menu. When adding a transformation to a source field that is part of a conditional expression, the transformation occurs before the conditional expression is executed.
   
   For example, in the mapping created in the previous steps, add the Subtract number transformation and value (5) to the price Source field. Change the value of the price field’s preview input field and retest to see the results:

Supported functions in conditional expressions

IF(Boolean-expression, then, else)

When a Boolean expression evaluates to true, the Data Mapper returns the results of the then statement. When the Boolean expression evaluates to false, the Data Mapper returns the results of the else statement. All three arguments are required. Either the then or the else arguments can be null, which means that nothing is mapped when Boolean expression evaluates to that argument.

For example, consider the mapping that combines the lastName and firstName source fields in the target customerName field. You can specify this conditional expression:

IF (ISEMPTY(lastName), firstName, lastName + ‘,’ + firstName )

During execution, the Data Mapper evaluates the lastName field.

• If the lastName field is empty, that is, ISEMPTY(lastName) returns true, the Data Mapper inserts only the firstName value into the target customerName field.
• If the lastName field contains a value, that is, ISEMPTY(lastName) returns false, the Data Mapper maps the lastName value, followed by a comma, followed by the firstName value into the target customerName field.

Now consider the behavior if the third argument in this expression is null:

IF (ISEMPTY(lastName), firstName, null )

During execution, the Data Mapper evaluates the lastName field.

• As in the previous example, if the lastName field is empty, that is, ISEMPTY(lastName) returns true, the Data Mapper inserts only the firstName value into the target customerName field.
• However, when the third argument is null, if the lastName field contains a value, that is, ISEMPTY(lastName) returns false, the Data Mapper does not map anything into the target customerName field.

ISEMPTY(source-field-name1 [+ source-field-name2])

The result of the ISEMPTY() function is a Boolean value. Specify at least one argument, which is the name of a source field in the mapping that you want to apply the condition to. When the specified source field is empty, the ISEMPTY() function returns true.

Optionally, add the + (concatenation) operator with an additional field, for example:

ISEMPTY(lastName + firstName)

This expression evaluates to true if both source fields, lastName and firstName, are empty.

Often, the ISEMPTY() function is the first argument in an IF() function.