Advanced Field Mapping Definition

You can use the advanced mapping notation to set field mapping definitions on the OpenAir NetSuite Connector Mapping tab.

The following table lists the field mapping definition type with a description of usage and syntax, including the syntax used on the legacy NetSuite integration form if different. Review also the Advanced Mapping Best Practice Guidelines.

Source value	Usage	Syntax	Legacy syntax (if different)
Field value	Simple field mapping — Set the field in the target application to the same value as the field in the source application.	Not required unless used with conditional branching. The Mapping tab lets you select fields from a dropdown lists informed by the schema. Use the legacy syntax with conditional branching.	Target field name, followed by at least one space and source field name. `target_field source_field`
Fixed value	Set a field value in the target application to a fixed value.	Unless used with conditional branching, enter the equal sign followed by a value contained within single quotation marks in the Source field column. Example: `='123'` Use the legacy syntax with conditional branching.	Target field name, followed by at least one space and a value contained within a pair of single quotation marks. `target_field '123'`
Combined field and fixed string values	Set the field value in the target application to a concatenated string combining strings from multiple field values in the source application or fixed values. `\\n` can be used in fixed value strings to insert line breaks. Example: `'String\\n\\with\\nline\\nbreaks'`.	Target field name, followed by at least one space and a concatenation expression. The concatenation operator is a comma and can be used to combine any number of field string values as well as fixed string values contained within a pair of single quotation marks. `target_field source_field_1,'fixed string value',source_field_2` For example, use this syntax to map URL custom fields in OpenAir to URL custom fields in NetSuite for import. URL custom field values in NetSuite include the URL only, whereas URL custom field values in OpenAir include both the visible link text and the link URL. If the NetSuite URL field value is `https://www.example.com/example`, the corresponding URL field value in OpenAir should be `[Example of visible link text](https://www.example.com/example)`. To import URL custom field from NetSuite into OpenAir, use the following advanced field mapping: `target_field '[Example of visible link text](',source_field,')'` Concatenation can be used with looked up OpenAir field values. The following example maps a list/record custom entity field holding the internal ID of the parent project in NetSuite to a pick list custom field in OpenAir. Pick list custom field values sourcing list items from projects include the internal IDs of the customer [`customer_id`] and the project [`project_id`] separated by a colon [`:`]. The mapping involves two looked up OpenAir field values and some concatenation. `parent_project lookup=custentity_parent_project:lookup_table=project:lookup_by=netsuite_project_id:lookup_return=customer_id,':',lookup=custentity_parent_project:lookup_table=project:lookup_by=netsuite_project_id:lookup_return=id`	—
Today’s date (Macro)	Set the field value in the target application to today’s date.	Target field name, followed by at least one space and `_TODAY` `target_field _TODAY`	—
Empty (Macro)	Exclude a mapping. Can be used to exclude default mapping, for example.	Not required unless used with conditional branching. The Mapping tab lets you deactivate the field mapping row. Use the legacy syntax with conditional branching.	Target field name, followed by at least one space and `_REMOVE` `target_field _REMOVE`
Field value mapped to target field value	Set the target field values according to a list of mapped source field value – target field value pairs. In the example given, if the source field value is `127`, the target field value will be set to `4970`. `<target_field source_field> '126' '4971' '127' '4970' </target_field>` Important: Mapped values in the left column cannot contain spaces. You can refer to the values using internal ID of the record instead. For example, use the internal ID on Department records to map the department name field values. Alternatively, you can use conditional branching.	Opening tag with the target field name followed by a space and the source field name. List of mapped target and source field values, one target field value and one source field value per line, separated by one space and with each value contained within single quotation marks. Closing tag with the target field name. `<target_field source_field> source_value_1 target_value_1 source_value_2 target_value_2 </target_field>`	—
Looked up OpenAir field value	Look up a field value in a related OpenAir table and set the target field to this looked up value, if a matching record exists in the related table and if the looked up value exists. Can be used with concatenation (See “Combined field and fixed string values” entry in this table). Can be used with conditional branching (See “Conditional branching value” entry in this table).	Target field name, followed by at least one space, and all required lookup parameter-value pairs. Each parameter-value pair must be written in the format `parameter=value` and separated by colons (`:`). Four parameters are required: `lookup` - The lookup field. The field in the source application containing the value to be looked up. `lookup_table` - The lookup table. The OpenAir table containing the looked up information. `lookup_by` - The field in the lookup table containing the lookup field value. `lookup_return` - The field in the lookup table containing the value the target field should be set to. `job_code_id lookup=custentityuser_free_text: lookup_table=job_code:lookup_by=name: lookup_return=id`	—
Conditional branching value	Set the target field value using different mapping definitions depending on different conditions. Conditions can be set to compare a source field value with a fixed value or with another source field value. Conditions can be set to compare a looked up OpenAir field value with a fixed value or with another source field value. Conditions can be combined using the `AND` and `OR` logical operators. Conditional branching mappings are always applied after standard mappings when the integration runs. Generally, custom fields referenced in the default mapping definitions cannot be overridden with these custom mappings.	Conditional branching block opening statement `IF <<END` (space between `IF` and `<<`, no space between `<<` and `END`), followed by a line break, a series of `IF logical_expression THEN mapping ELSE alt_mapping` each separated by a line break, and a conditional branching block closing statement `END` on a separate line. Logical expressions can be formed of multiple conditions (relational expressions) combined using logical operators. Each relational expression consists of two operands separated by a relational operator and must be contained within parentheses. Available relational operators: `=`, `<=`, `>=`, `<`, `>`, and `<>` (not equal) Relational operands can be source field values, including looked up OpenAir field values, or fixed values. When using a looked up OpenAir field value as a relational operand, include the full `lookup` syntax between parentheses. Available logical operators in order of precedence: `AND`, `OR`. Parentheses can be used to modify operator precedence. `IF <<END IF (email = 'mcollins@example.com') OR (email = 'admin@example.com') THEN email lastName IF (firstName = 'John') THEN email entityID ELSE email 'nothing' END` Example of conditional branching using looked up OpenAir field values: `IF << END IF (lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_vendor2_id) <> '' THEN custcolinv_line_emp lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_vendor2_id IF (lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_user_id) <> '' THEN custcolinv_line_emp lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_user_id END` Important: There cannot be more than one conditional branching block on the same mapping group tab. Include all conditional branching (`IF ... THEN ...`) mapping definitions on the same mapping definition row. Leave the destination field empty on that row.	—

Source value

Usage

Syntax

Legacy syntax

(if different)

Field value

Simple field mapping — Set the field in the target application to the same value as the field in the source application.

Not required unless used with conditional branching. The Mapping tab lets you select fields from a dropdown lists informed by the schema.

Use the legacy syntax with conditional branching.

Target field name, followed by at least one space and source field name.

target_field source_field

Fixed value

Set a field value in the target application to a fixed value.

Unless used with conditional branching, enter the equal sign followed by a value contained within single quotation marks in the Source field column.

Example: ='123'

Use the legacy syntax with conditional branching.

Target field name, followed by at least one space and a value contained within a pair of single quotation marks.

target_field '123'

Combined field and fixed string values

Set the field value in the target application to a concatenated string combining strings from multiple field values in the source application or fixed values.

\\n can be used in fixed value strings to insert line breaks. Example: 'String\\n\\with\\nline\\nbreaks'.

Target field name, followed by at least one space and a concatenation expression. The concatenation operator is a comma and can be used to combine any number of field string values as well as fixed string values contained within a pair of single quotation marks.

target_field source_field_1,'fixed string value',source_field_2

For example, use this syntax to map URL custom fields in OpenAir to URL custom fields in NetSuite for import. URL custom field values in NetSuite include the URL only, whereas URL custom field values in OpenAir include both the visible link text and the link URL. If the NetSuite URL field value is https://www.example.com/example, the corresponding URL field value in OpenAir should be [Example of visible link text](https://www.example.com/example). To import URL custom field from NetSuite into OpenAir, use the following advanced field mapping:

target_field '[Example of visible link text](',source_field,')'

Concatenation can be used with looked up OpenAir field values. The following example maps a list/record custom entity field holding the internal ID of the parent project in NetSuite to a pick list custom field in OpenAir. Pick list custom field values sourcing list items from projects include the internal IDs of the customer [customer_id] and the project [project_id] separated by a colon [:]. The mapping involves two looked up OpenAir field values and some concatenation.

                    parent_project lookup=custentity_parent_project:lookup_table=project:lookup_by=netsuite_project_id:lookup_return=customer_id,':',lookup=custentity_parent_project:lookup_table=project:lookup_by=netsuite_project_id:lookup_return=id

—

Today’s date (Macro)

Set the field value in the target application to today’s date.

Target field name, followed by at least one space and _TODAY

target_field _TODAY

—

Empty (Macro)

Exclude a mapping. Can be used to exclude default mapping, for example.

Not required unless used with conditional branching. The Mapping tab lets you deactivate the field mapping row.

Use the legacy syntax with conditional branching.

Target field name, followed by at least one space and _REMOVE

target_field _REMOVE

Field value mapped to target field value

Set the target field values according to a list of mapped source field value – target field value pairs. In the example given, if the source field value is 127, the target field value will be set to 4970.

<target_field source_field>
   '126' '4971'
   '127' '4970'
</target_field>

Important:

Mapped values in the left column cannot contain spaces. You can refer to the values using internal ID of the record instead. For example, use the internal ID on Department records to map the department name field values. Alternatively, you can use conditional branching.

Opening tag with the target field name followed by a space and the source field name. List of mapped target and source field values, one target field value and one source field value per line, separated by one space and with each value contained within single quotation marks. Closing tag with the target field name.

<target_field source_field>
   source_value_1 target_value_1
   source_value_2 target_value_2
</target_field>

—

Looked up OpenAir field value

Look up a field value in a related OpenAir table and set the target field to this looked up value, if a matching record exists in the related table and if the looked up value exists.

Can be used with concatenation (See “Combined field and fixed string values” entry in this table).

Can be used with conditional branching (See “Conditional branching value” entry in this table).

Target field name, followed by at least one space, and all required lookup parameter-value pairs. Each parameter-value pair must be written in the format parameter=value and separated by colons (:).

Four parameters are required:

lookup - The lookup field. The field in the source application containing the value to be looked up.
lookup_table - The lookup table. The OpenAir table containing the looked up information.
lookup_by - The field in the lookup table containing the lookup field value.
lookup_return - The field in the lookup table containing the value the target field should be set to.

job_code_id   lookup=custentityuser_free_text:
              lookup_table=job_code:lookup_by=name:
              lookup_return=id

—

Conditional branching value

Set the target field value using different mapping definitions depending on different conditions.

Conditions can be set to compare a source field value with a fixed value or with another source field value.

Conditions can be set to compare a looked up OpenAir field value with a fixed value or with another source field value.

Conditions can be combined using the AND and OR logical operators.

Conditional branching mappings are always applied after standard mappings when the integration runs.

Generally, custom fields referenced in the default mapping definitions cannot be overridden with these custom mappings.

Conditional branching block opening statement IF <<END (space between IF and <<, no space between << and END), followed by a line break, a series of IF logical_expression THEN mapping ELSE alt_mapping each separated by a line break, and a conditional branching block closing statement END on a separate line.

Logical expressions can be formed of multiple conditions (relational expressions) combined using logical operators.

Each relational expression consists of two operands separated by a relational operator and must be contained within parentheses.
Available relational operators: =, <=, >=, <, >, and <> (not equal)
Relational operands can be source field values, including looked up OpenAir field values, or fixed values.
When using a looked up OpenAir field value as a relational operand, include the full lookup syntax between parentheses.
Available logical operators in order of precedence: AND, OR.
Parentheses can be used to modify operator precedence.

IF <<END
   IF (email = 'mcollins@example.com') OR (email = 'admin@example.com') THEN email lastName
   IF (firstName = 'John') THEN email entityID ELSE email 'nothing'
END

Example of conditional branching using looked up OpenAir field values:

IF << END
   IF (lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_vendor2_id) <> '' THEN custcolinv_line_emp lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_vendor2_id
   IF (lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_user_id) <> '' THEN custcolinv_line_emp lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=netsuite_user_id
END

Important:

There cannot be more than one conditional branching block on the same mapping group tab. Include all conditional branching (IF ... THEN ...) mapping definitions on the same mapping definition row. Leave the destination field empty on that row.

—

Advanced Mapping Best Practice Guidelines

Review the following best practice guidelines:

You should contact OpenAir Professional Services if you need to make changes to your integration configuration and field mapping definitions.
Make sure you familiarize yourself with the NetSuite and OpenAir technical documentation before attempting to set field mapping definitions using the advanced notation — See Schema Reference Documentation.
Declare each NetSuite or OpenAir field you reference in your advanced field mapping definitions — See Field Declarations.

Checkbox fields — You can use an advanced mapping to map a checkbox field in NetSuite with a checkbox field in OpenAir. Note that the values corresponding to the state of the checked or cleared state of the box are different in NetSuite and OpenAir. Refer to the following table and example:

Checkbox State	NetSuite Value	OpenAir Value
Checked	`'true'`	`'1'`
Cleared	`'false'`	`''` (empty value)

Checkbox State

NetSuite Value

OpenAir Value

Checked

'true'

'1'

Cleared

'false'

'' (empty value)

NS_FIELDS custentity_my_netsuite_checkbox
NS_CUSTOM_FIELDS custentity_my_netsuite_checkbox
OA_FIELDS my_openair_checkbox
OA_CUSTOM_FIELDS my_openair_checkbox

IF <<END
   IF (my_openair_checkbox = '1') THEN custentity_my_netsuite_checkbox 'true'
   IF (my_openair_checkbox = '') THEN custentity_my_netsuite_checkbox 'false'
END