OpenAir Field Value Lookup (Export) and Record Lookup (Import)

You can use the following lookup options when mapping OpenAir fields to columns in the CSV file you export information to or import information from:

• Field value lookup (Export) — You can look up OpenAir standard or custom field values for record types directly or indirectly related to the record type you export from OpenAir to a CSV file. This may be useful when you need to include:

  • Information stored in a custom field for a record type directly related to the record type you selected for export.

    Note:

    Standard fields for supported record types directly related with the record type you selected for export are available for mapping without additional setup. See Mapping OpenAir Fields to CSV Columns.

  • Information stored in a standard or custom field for a record type indirectly related to the record type you selected for export. If the record type you export includes a foreign key to a related record type that itself includes a foreign key to a third record type, you can make information from that third record type available for mapping. For example, each project is associated to a customer referenced using the foreign key customer_id in the project table, there is a named employee who approves invoices for each customer and this named employee is referenced using the foreign key tb_approver in the customer table. If you export projects, you can use a field value lookup to include information from the user table about the employee who approves invoices for the customer you are delivering the project for.

    Note:

    You can use external field lookup to export information about the payment terms associated with your invoices in invoice and time bill exports.

    The optional feature Save Payment Terms Internal ID on Invoice Records must be enabled for your account. This feature adds a hidden payment terms ID field (payment_termsid), that can then be used for external field lookup.

    For more information, see Save Payment Terms Internal ID on Invoice Records.

  • Information stored in a standard or custom field for a related record of the same type you selected for export. For example, a rebill invoice or a credit note is an invoice object that relates to an original invoice referenced by the foreign key original_invoiceid. If you export invoices, you can use a field lookup to include information about the original invoice associated with a rebill invoice or a credit note.

• Related record lookup (Import) — You can look up OpenAir records associated with the records you import by name or external ID. For example, a CSV file contains information about expense receipts and includes the name of the project for which the expense was incurred, or the unique ID for that project in the third-party software application from which you extracted the information. If you import receipts from that CSV file into OpenAir, you can use a record lookup by name or external ID to include the internal ID of the OpenAir project record associated with each receipt you import.

Looking Up OpenAir Field Values (Export)

Use the following steps to look up OpenAir standard or custom field values for record types directly or indirectly related to the record type you export from OpenAir to a CSV file.

To look up OpenAir field values on export:

1. On the Field Mapping window, select a field mapping with a green arrow and click External Field.

   Note:

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

   The External Field Lookup window appears.

   

2. Enter a name for the OpenAir field value lookup.

3. Source Association — Select the record type and the field for this record type containing the foreign key. To do so:

   1. Select a record type under Select the association to map from. Dropdown list options include the record type selected for export and other supported record types directly related to the record type selected for export.

   2. Do one of the following:

      • If the field containing the foreign key is a standard OpenAir field, choose Record field and select one of the standard fields for that record type.

      • If the field containing the foreign key is a custom field, choose Custom field and enter the custom field name exactly as defined in OpenAir.

        Note:

        The custom field type must be a pick list or hold a foreign key referencing an OpenAir object by its internal ID.

        To verify the name and type of custom fields in OpenAir, go to Administration > Global Settings > Custom Fields.

        Custom fields may have an optional display name that is used on forms and list views in the OpenAir UI in addition to the unique field name that is used to reference the custom field. This field name may contain only alphanumeric and underscore characters. For more information about custom fields, see Custom Fields.

4. Target Association — Select the record type and the field for this record type containing the looked up field value. To do so:

   1. Select a record type under Select the external record type. Dropdown list options include all supported record types.

   2. Do one of the following:

      • If the lookup field is a standard OpenAir field, choose Record field and select one of the standard fields for that record type.

      • If the lookup field is a custom field, choose Custom field and enter the custom field name exactly as defined in OpenAir.

        Note:

        To verify the name and type of custom fields in OpenAir, go to Administration > Global Settings > Custom Fields.

        Custom fields may have an optional display name that is used on forms and list views in the OpenAir UI in addition to the unique field name that is used to reference the custom field. This field name may contain only alphanumeric and underscore characters. For more information about custom fields, see Custom Fields.

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

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

Looking Up Associated OpenAir Records (Import)

Use the following steps to look up OpenAir records associated with the records you import by name or external ID.

To look up associated OpenAir records on import:

1. On the Field Mapping window, right-click on a field mapping linking a foreign key in the OpenAir table to any column in the CSV file with a green arrow, and click Edit.

   Note:

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

   The Edit field mapping window appears.

   

2. If the field value can reference several associated records referenced by a comma-separated list of external IDs or names, check the List of values separated by comma.

3. Check the Map in place of internal OpenAir ID box.

4. Choose the lookup option. You can look up the OpenAir record (and its OpenAir internal ID) by External ID (default) or by Name.

   Note:

   OpenAir lets you store a unique External ID [external_id] for records imported from a third-party application. You should use this External ID to reference the primary key of each record in the external system you import it from, and lookup record associations using external IDs. Names may not necessarily be unique, unless you have some strict validation in place to avoid duplicate names.

5. Select the OpenAir record type to look up from the Select record type this id is associated with dropdown list.

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

   A confirmation window appears.

7. Click Yes to confirm that the value of the external ID or name field is unique for each record in the selected record type, and continue.

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