Record Creation or Update Rules on Import

You can use the Integration Manager import functionality to create new records or update existing records in OpenAir.

Integration Manager uses an import key field or a combination of import field key fields to determine if a matching record already exists in OpenAir. If the record exists, Integration Manager updates the OpenAir record with the corresponding information in the CSV file. If the record does not exist, Integration Manager adds a new record in OpenAir.

Several factors impact the record creation and update rules when importing information from a source CSV file into OpenAir:

Lookup method — Two lookup methods are available. The New serverside lookup by external_id box on the Field Mapping window control which method Integration Manager uses.

Important:
You should use the server-side import key lookup method. It is faster and more robust than the alternative client-side method. To do so, check the New serverside lookup by external_id box. This is the default method – the box is already checked the first time you open the field mapping settings for a specific record type.
- Server-side lookup (when the box is checked) — Integration Manager uses server-side logic to process each row of information (or record) in the source CSV file one after the other, and searches for the import key field value in OpenAir to determine whether to update or create the corresponding record in OpenAir.
- Client-side lookup (when the box is cleared) — Integration Manager compares each row of information in the source CSV (on the client-side) with information retrieved from OpenAir before the import. This method also uses import keys, but the record creation or update rules depend on the type of record you import. This method does not cross check the content if the source CSV file for duplicated import key field values. You must verify your CSV data and be sure that all import key field values are unique to avoid the creation of duplicate records in OpenAir.
The server-side lookup offers a little less flexibility in the type of fields that can be used as import keys. However, it is faster and more robust than the alternative client-side method.

Field mapping — Integration Manager determines which field to use as the import key based on the field mapping you define for your import. The following table lists the fields that can be used as import key in descending order of precedence from highest to lowest. The import key is the highest of the listed OpenAir field that is mapped to column in the source CSV file.

Note:

When the New serverside lookup by external_id box is checked, a red color arrow indicates the field used as an import key.

#	OpenAir Field	Descripton
1.	Internal ID [`id`]	Map the OpenAir internal ID if you want the import to update OpenAir records only. The OpenAir internal ID is the unique identifier (primary key) for a record of a given type in OpenAir. OpenAir assigns a unique internal ID sequentially to each new record. The assignment of internal IDs is reserved to OpenAir. If the OpenAir internal ID [`Id`] is mapped to a column in the source CSV file, Integration Manager uses the internal ID as the import key to find and update the matching OpenAir record. If there are no records with a matching record type and matching internal ID, Integration Manager returns an error. A mapped OpenAir internal ID always takes precedence over a custom import key, external ID, or name field. Important: Integration Manager never creates a record in OpenAir on import if the OpenAir internal ID is mapped with a column in the CSV file.
2.	Custom Import Key Field	Define and map a custom import key to create or update OpenAir records. OpenAir lets you store an External ID [`External_id`] for records imported from a third-party application as standard. You can also create custom fields in OpenAir to store unique identifiers for corresponding records in multiple third-party applications across your IT infrastructure. To do so: In OpenAir, create a custom field to use as import key. See Creating and Modifying Custom Fields. In Integration Manager, declare this custom field as an import key by checking the Its value is unique and the field is used as a key mapping field box. See Making OpenAir Custom Fields Available for Mapping. Integration Manager uses the custom import key to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and custom import key, if all the following conditions are met: A custom import key is mapped to a column in the source CSV file. The OpenAir internal ID [`Id`] is not mapped to a column in the source CSV file. Important: You should use the custom import key to reference a unique identifier (primary key) for the corresponding record in the third-party application you import information from. The custom import key can then be used to link an OpenAir record with an external system record in a 1:1 relationship, to ensure that the OpenAir record can be updated after you make changes to the information in the external system, and to avoid the creation of duplicate records.
3.	External ID [`external_id`]	Map the external ID standard field to create or update OpenAir records. OpenAir lets you store an External ID [`External_id`] for records imported from a third-party application. Integration Manager uses the external ID to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and external ID, if all the following conditions are met: The external ID [`External_id`] field is mapped to a column in the source CSV file. A custom import key field is not mapped to a column in the source CSV file. The OpenAir internal ID [`Id`] field is not mapped to a column in the source CSV file. Important: You should use the external ID to reference a unique identifier (primary key) for the corresponding record in the third-party application you import information from. The external ID can then be used to link an OpenAir record with an external system record in a 1:1 relationship, to ensure that the OpenAir record can be updated after you make changes to the information in the external system, and to avoid the creation of duplicate records.
4.	Name [`name`]	Map the name standard field to create or update OpenAir records (client-side lookup only) depending on the record type. Depending on the record type, Integration Manager uses the name field to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and name, if all the following conditions are met: The New serverside lookup by external_id box is not checked on the Field Mapping window. The name [`name`] field is mapped to a column in the source CSV file. The external ID [`External_id`] field is not mapped to a column in the source CSV file. A custom import key field is not mapped to a column in the source CSV file. The OpenAir internal ID [`Id`] field is not mapped to a column in the source CSV file. Important: The name cannot be used as the import key field when the server-side lookup is enabled. This lookup behavior depends on the type of records you import. See below.

OpenAir Field

Descripton

Internal ID [id]

Map the OpenAir internal ID if you want the import to update OpenAir records only.

The OpenAir internal ID is the unique identifier (primary key) for a record of a given type in OpenAir. OpenAir assigns a unique internal ID sequentially to each new record. The assignment of internal IDs is reserved to OpenAir.

If the OpenAir internal ID [Id] is mapped to a column in the source CSV file, Integration Manager uses the internal ID as the import key to find and update the matching OpenAir record. If there are no records with a matching record type and matching internal ID, Integration Manager returns an error.

A mapped OpenAir internal ID always takes precedence over a custom import key, external ID, or name field.

Important:

Integration Manager never creates a record in OpenAir on import if the OpenAir internal ID is mapped with a column in the CSV file.

Custom Import Key Field

Define and map a custom import key to create or update OpenAir records.

OpenAir lets you store an External ID [External_id] for records imported from a third-party application as standard. You can also create custom fields in OpenAir to store unique identifiers for corresponding records in multiple third-party applications across your IT infrastructure. To do so:

In OpenAir, create a custom field to use as import key. See Creating and Modifying Custom Fields.
In Integration Manager, declare this custom field as an import key by checking the Its value is unique and the field is used as a key mapping field box. See Making OpenAir Custom Fields Available for Mapping.

Integration Manager uses the custom import key to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and custom import key, if all the following conditions are met:

A custom import key is mapped to a column in the source CSV file.
The OpenAir internal ID [Id] is not mapped to a column in the source CSV file.

Important:

You should use the custom import key to reference a unique identifier (primary key) for the corresponding record in the third-party application you import information from. The custom import key can then be used to link an OpenAir record with an external system record in a 1:1 relationship, to ensure that the OpenAir record can be updated after you make changes to the information in the external system, and to avoid the creation of duplicate records.

External ID [external_id]

Map the external ID standard field to create or update OpenAir records.

OpenAir lets you store an External ID [External_id] for records imported from a third-party application.

Integration Manager uses the external ID to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and external ID, if all the following conditions are met:

The external ID [External_id] field is mapped to a column in the source CSV file.
A custom import key field is not mapped to a column in the source CSV file.
The OpenAir internal ID [Id] field is not mapped to a column in the source CSV file.

Important:

You should use the external ID to reference a unique identifier (primary key) for the corresponding record in the third-party application you import information from. The external ID can then be used to link an OpenAir record with an external system record in a 1:1 relationship, to ensure that the OpenAir record can be updated after you make changes to the information in the external system, and to avoid the creation of duplicate records.

Name [name]

Map the name standard field to create or update OpenAir records (client-side lookup only) depending on the record type.

Depending on the record type, Integration Manager uses the name field to find and update the matching OpenAir record, if it exists, or to create a new record in OpenAir if there are no records with a matching record type and name, if all the following conditions are met:

The New serverside lookup by external_id box is not checked on the Field Mapping window.
The name [name] field is mapped to a column in the source CSV file.
The external ID [External_id] field is not mapped to a column in the source CSV file.
A custom import key field is not mapped to a column in the source CSV file.
The OpenAir internal ID [Id] field is not mapped to a column in the source CSV file.

Important:

The name cannot be used as the import key field when the server-side lookup is enabled.

This lookup behavior depends on the type of records you import. See below.

Record Type — If using the client-side lookup (when New serverside lookup by external_id box is not checked), the record creation or update rules depend on the type of record being imported. The following table describes record creation and update rule variations for different types of records.

Record Type	Record Creation and Update Rules Variations
Transaction Records: Booking Envelope Invoice Payment Project Billing Rule Project task Project task assign Receipt Reimbursement Resource Profile TimeBill or Slip (Charges) User Project Rate.	Only the OpenAir internal ID can be used as import key.
Entity Records: Booking Type Cost Center Customer Department Expense Item Prospect Service Vendor	OpenAir internal ID, custom import key, external ID, and name fields can be used as import keys.
Special Case: Contact	The OpenAir internal ID can be used as import key. In addition, a combination of `customer_id`, `firstname` and `lastname` is always used as import key. If the customer ID and the contact first and last name in the source CSV file match the customer ID, first and last name of an existing contact record in OpenAir, in addition to a matching internal ID, custom import key, or external ID, that record is updated, and if there are no contact records with a matching customer ID, first and last name, a new record is created.
Special Case: Project	The OpenAir internal ID can be used as import key. The combination of `customer_id` and `name` fields can be used as import key (instead of the `name` field alone). In this case, if both the customer ID and the project name in the source CSV file match the customer ID and name of an existing project record in OpenAir, that record is updated, and if there are no project records with a matching customer ID and matching name, a new record is created.
Special Case: User	OpenAir internal ID, custom import key, and external ID fields can all be used as import keys. The `nickname` can be used as import key (instead of the `name` field).
Special Case: Profile Type	The OpenAir internal ID can be used as import key. The combination of `name` and `type` fields can be used as import key (instead of the name field alone). In this case, if both the name and the type in the source CSV file match the name and type of an existing profile type record in OpenAir, that record is updated, and if there are no profile type records with a matching name and matching type, a new record is created.

Record Type

Record Creation and Update Rules Variations

Transaction Records:

Booking
Envelope
Invoice
Payment
Project Billing Rule
Project task
Project task assign
Receipt
Reimbursement
Resource Profile
TimeBill or Slip (Charges)
User Project Rate.

Only the OpenAir internal ID can be used as import key.

Entity Records:

Booking Type
Cost Center
Customer
Department
Expense Item
Prospect
Service
Vendor

OpenAir internal ID, custom import key, external ID, and name fields can be used as import keys.

Special Case:

Contact

The OpenAir internal ID can be used as import key.

In addition, a combination of customer_id, firstname and lastname is always used as import key. If the customer ID and the contact first and last name in the source CSV file match the customer ID, first and last name of an existing contact record in OpenAir, in addition to a matching internal ID, custom import key, or external ID, that record is updated, and if there are no contact records with a matching customer ID, first and last name, a new record is created.

Special Case:

Project

The OpenAir internal ID can be used as import key.

The combination of customer_id and name fields can be used as import key (instead of the name field alone). In this case, if both the customer ID and the project name in the source CSV file match the customer ID and name of an existing project record in OpenAir, that record is updated, and if there are no project records with a matching customer ID and matching name, a new record is created.

Special Case:

User

OpenAir internal ID, custom import key, and external ID fields can all be used as import keys.

The nickname can be used as import key (instead of the name field).

Special Case:

Profile Type

The OpenAir internal ID can be used as import key.

The combination of name and type fields can be used as import key (instead of the name field alone). In this case, if both the name and the type in the source CSV file match the name and type of an existing profile type record in OpenAir, that record is updated, and if there are no profile type records with a matching name and matching type, a new record is created.