Expense Line Mapping Group

Use the Expense Line mapping group to map information from receipt records in the OpenAir expense report with information on the expense line items on the NetSuite expense report transaction record.

For reference information and best practice guidelines, review the following topics:

References — Overview of record types in OpenAir and NetSuite with links to the relevant reference material for information about fields available for mapping.
Default Field Mappings — Overview of default field mapping definitions including notes and best practice guidelines where relevant. Information exported by default includes .
Additional Field Mappings — There are no specific best practice guidelines for custom field mapping definitions. The mapping group does not support custom pullback field mappings, retrieving information from the line item created in NetSuite back into OpenAir.

References

The following table gives reference information about the records and fields available for custom field mapping definition with links to the relevant reference material. See also Schema Reference Documentation.

NetSuite Record type [`SOAP object`]	OpenAir Record Type [`table`]	Mapping Group [`Integration point`]
Expense Report Expense Line Item [`ExpenseReportExpense`] Schema: ExpenseReportExpense (2020.1)\|ExpenseReportExpense (2023.1) XSD: tranEmp (2020.1)\|tranEmp (2023.1)	Receipt [`ticket`, `item`] Data Dictionary: ticket, item	Expense Line [`EXPENSE_REP_LINE`]

NetSuite Record type [SOAP object]

OpenAir Record Type [table]

Mapping Group [Integration point]

Expense Report Expense Line Item [ExpenseReportExpense]

Schema: ExpenseReportExpense (2020.1)|ExpenseReportExpense (2023.1)

XSD: tranEmp (2020.1)|tranEmp (2023.1)

Receipt [ticket, item]

Data Dictionary: ticket, item

Expense Line [EXPENSE_REP_LINE]

Default Field Mappings

The following table describes the default field mapping definitions for the Expense Line mapping group tab.

NetSuite Field	OpenAir Field	Notes
`ExpenseReportExpense.category`	Looked up OpenAir field values — `Item.netsuite_item_id__c` for the expense item with `Item.id` equal to `Ticket.item_id`	The internal ID of the NetSuite expense category corresponding to the expense item associated with the receipt in OpenAir.
`ExpenseReportExpense.expenseDate`	`Ticket.date`	—
`ExpenseReportExpense.memo`	`Ticket.notes`	—
`ExpenseReportExpense.customer`	Looked up OpenAir field values — `Project.netsuite_project_id__c` for the project with `Project.id` equal to `Ticket.project_id`	The internal ID of the NetSuite project record corresponding to the project associated with the receipt in OpenAir.
`ExpenseReportExpense.amount`	`Ticket.total` `Ticket.total` - `taxAmt1` calculated value	The Tax amount is substracted from the total Amount when the following conditions are all met: The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form. Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax. The NetSuite Tax Nexus Type [`netsuite_tax_nexus`] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to `Canada` or `VAT`. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax. See also the mapping definition for NetSuite field `ExpenseReportExpense.taxAmt1` in this table.
`ExpenseReportExpense.isBillable`	See notes	The Billable box on NetSuite expense line items is exported from OpenAir receipts Never checked if the Disable expense report references on invoices in NetSuite integration box is checked on the Invoices (Invoices) export workflow settings form exported from OpenAir receipts. The following rules are ignored. Important: You should not check the Disable expense report references on invoices in NetSuite integration box in most cases. For more information, see Disable vendor bill linkage on invoices in NetSuite integration. Checked if the receipt is associated with a charge record in OpenAir. The following rules are ignored. Not checked if the Billable receipt box is not checked on the receipt in OpenAir. The following rules are ignored. Set according to the Project receipts on export to NetSuite [`netsuite_project_expenses`] custom field value on the associated project record in OpenAir, if a value is set. Checked if Project receipts on export to NetSuite is set to `Billable`, not checked if Project receipts on export to NetSuite is set to `Non-billable`. Checked, otherwise.
`ExpenseReportExpense.receipt`	`!Ticket.missing_receipt`	The Receipt box is checked on the expense line item in NetSuite if the Missing Receipt box is not checked on the OpenAir receipt.
`ExpenseReportExpense.currency`	`Ticket.currency` `Ticket.currency_symbol` Base currency of OpenAir account	Active only if the Multiple Currency is enabled for your NetSuite account. By default, the currency on the expense report is set to: The Currency on the receipt record in OpenAir when it is a foreign currency receipt (receipt in a different currency to the currency on the employee record associated with the expense report), and if the Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports is not checked on the Expense Reports (Expense Reports) export workflow settings form. The Currency field is `Ticket.currency_symbol` if the Foreign Currency Receipts in Expense Grid is enabled for your OpenAir account (recommended), and `Ticket.currency` otherwise. See also Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports. The Currency on the receipt record in OpenAir, otherwise and if the currency is the same as the Currency for the expense report and the Multicurrency feature is enabled for your OpenAir account. The base currency for your OpenAir account, otherwise.
`ExpenseReportExpense.foreignAmount`	`Ticket.currency_cost * Ticket.quantity` `Ticket.total`	Active only if the Multiple Currency is enabled for your NetSuite account. The total amount in foreign currency if it is a foreign currency receipt and if the Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports is not checked on the Expense Reports (Expense Reports) export workflow settings form. The Foreign Amount field is set to `Ticket.currency_cost` multiplied by `Ticket.quantity` if the Foreign Currency Receipts in Expense Grid is enabled for your OpenAir account (recommended), and `Ticket.total` otherwise. See also Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports.
`ExpenseReportExpense.custcol_oa_expense_report_line_id`	`Ticket.id`	Active and visible only if the Apply invoice line in NetSuite to expense using OpenAir ticket id box is checked on the OpenAir NetSuite Connector administration form. See Apply invoice line in NetSuite to expense using OpenAir ticket id. The internal ID of the OpenAir receipt is stored in the OpenAir Expense Report Line ID [`custcol_oa_expense_report_line_id`] custom field on the expense line item in NetSuite. OpenAir NetSuite Connector uses this custom field to match the relevant expense line when you export invoices from OpenAir to NetSuite.
`ExpenseReportExpense.taxCode`	Looked up OpenAir field value`Tax_location.netsuite_tax_rate_id` for tax location record with `Tax_location.id` equal to `Tax_rate.tax_location_id` for tax rate record with `Tax_rate.ticket_id` equal to `Ticket.id`	Visible only if the Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form. Active only when the following conditions are all met: The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form. Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax. The NetSuite Tax Nexus Type [`netsuite_tax_nexus`] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to `Canada` or `VAT`. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax. The Tax amount on the expense line is set to the Tax paid on the OpenAir receipt if the NetSuite Tax Nexus Type is `VAT` or calculated from values in the `tax_rate` record associated with the OpenAir receipt . if the NetSuite Tax Nexus Type is `Canada`.
`ExpenseReportExpense.taxAmt1`	Calculated value [`taxAmt1`] — `Ticket.total_tax_paid` Sum of `Tax_rate.gst` , `Tax_rate.hst`, `Tax_rate.pst`, `Tax_rate.state`, and `Tax_rate.federal` for tax rate record with `Tax_rate.ticket_id` equal to `Ticket.id`	Visible only if the Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form. Active only when the following conditions are all met: The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form. Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax. The NetSuite Tax Nexus Type [`netsuite_tax_nexus`] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to `Canada` or `VAT`. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax. The Tax amount on the expense line is set to the Tax paid on the OpenAir receipt if the NetSuite Tax Nexus Type is `VAT` or calculated from values in the `tax_rate` record associated with the OpenAir receipt . if the NetSuite Tax Nexus Type is `Canada`.
`ExpenseReportExpense.custcol_oa_gst_override`	Calculated value — sum of `Tax_rate.gst` and `Tax_rate.hst` for tax rate record with `Tax_rate.ticket_id` equal to `Ticket.id`	Active and visible only if the Allow manual override of Canadian taxes on expense report export box is checked on the Expense Reports (Expense Reports) export workflow settings form. The Goods and Services Tax (GST) and Harmonized Sales Tax (HST) is stored in the OpenAir: GST/HST [`custbody_oa_gst_override`] custom field on the NetSuite expense line item. See Allow manual override of Canadian taxes on Expense report export.
`ExpenseReportExpense.custcol_oa_pst_override`	Looked up OpenAir field value — `Tax_rate.pst` for tax rate record with `Tax_rate.ticket_id` equal to `Ticket.id`	Active and visible only if the Allow manual override of Canadian taxes on expense report export box is checked on the Expense Reports (Expense Reports) export workflow settings form. The Provincial Sales Tax (PST) entered manually on the OpenAir receipt is stored in the OpenAir: PST [`custcol_oa_pst_override`] custom field on the NetSuite expense line item. See Allow manual override of Canadian taxes on Expense report export.
—	`Ticket.exported`	Not visible on mapping definition table OpenAir adds a timestamp for the date and time the record was exported.

NetSuite Field

Direction

OpenAir Field

Notes

ExpenseReportExpense.category

Looked up OpenAir field values — Item.netsuite_item_id__c for the expense item with Item.id equal to Ticket.item_id

The internal ID of the NetSuite expense category corresponding to the expense item associated with the receipt in OpenAir.

ExpenseReportExpense.expenseDate

Ticket.date

—

ExpenseReportExpense.memo

Ticket.notes

—

ExpenseReportExpense.customer

Looked up OpenAir field values — Project.netsuite_project_id__c for the project with Project.id equal to Ticket.project_id

The internal ID of the NetSuite project record corresponding to the project associated with the receipt in OpenAir.

ExpenseReportExpense.amount

Ticket.total
Ticket.total - taxAmt1 calculated value

The Tax amount is substracted from the total Amount when the following conditions are all met:

The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form.
Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax.
The NetSuite Tax Nexus Type [netsuite_tax_nexus] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to Canada or VAT. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax.

See also the mapping definition for NetSuite field ExpenseReportExpense.taxAmt1 in this table.

ExpenseReportExpense.isBillable

See notes

The Billable box on NetSuite expense line items is exported from OpenAir receipts

Never checked if the Disable expense report references on invoices in NetSuite integration box is checked on the Invoices (Invoices) export workflow settings form exported from OpenAir receipts. The following rules are ignored.

Important:
You should not check the Disable expense report references on invoices in NetSuite integration box in most cases. For more information, see Disable vendor bill linkage on invoices in NetSuite integration.
Checked if the receipt is associated with a charge record in OpenAir. The following rules are ignored.
Not checked if the Billable receipt box is not checked on the receipt in OpenAir. The following rules are ignored.
Set according to the Project receipts on export to NetSuite [netsuite_project_expenses] custom field value on the associated project record in OpenAir, if a value is set. Checked if Project receipts on export to NetSuite is set to Billable, not checked if Project receipts on export to NetSuite is set to Non-billable.
Checked, otherwise.

ExpenseReportExpense.receipt

!Ticket.missing_receipt

The Receipt box is checked on the expense line item in NetSuite if the Missing Receipt box is not checked on the OpenAir receipt.

ExpenseReportExpense.currency

Ticket.currency
Ticket.currency_symbol
Base currency of OpenAir account

Active only if the Multiple Currency is enabled for your NetSuite account.

By default, the currency on the expense report is set to:

The Currency on the receipt record in OpenAir when it is a foreign currency receipt (receipt in a different currency to the currency on the employee record associated with the expense report), and if the Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports is not checked on the Expense Reports (Expense Reports) export workflow settings form. The Currency field is Ticket.currency_symbol if the Foreign Currency Receipts in Expense Grid is enabled for your OpenAir account (recommended), and Ticket.currency otherwise. See also Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports.
The Currency on the receipt record in OpenAir, otherwise and if the currency is the same as the Currency for the expense report and the Multicurrency feature is enabled for your OpenAir account.
The base currency for your OpenAir account, otherwise.

ExpenseReportExpense.foreignAmount

Ticket.currency_cost * Ticket.quantity
Ticket.total

Active only if the Multiple Currency is enabled for your NetSuite account.

The total amount in foreign currency if it is a foreign currency receipt and if the Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports is not checked on the Expense Reports (Expense Reports) export workflow settings form. The Foreign Amount field is set to Ticket.currency_cost multiplied by Ticket.quantity if the Foreign Currency Receipts in Expense Grid is enabled for your OpenAir account (recommended), and Ticket.total otherwise. See also Do not set the foreign currency in NetSuite for foreign currency receipts when exporting Expense reports.

ExpenseReportExpense.custcol_oa_expense_report_line_id

Ticket.id

Active and visible only if the Apply invoice line in NetSuite to expense using OpenAir ticket id box is checked on the OpenAir NetSuite Connector administration form. See Apply invoice line in NetSuite to expense using OpenAir ticket id.

The internal ID of the OpenAir receipt is stored in the OpenAir Expense Report Line ID [custcol_oa_expense_report_line_id] custom field on the expense line item in NetSuite. OpenAir NetSuite Connector uses this custom field to match the relevant expense line when you export invoices from OpenAir to NetSuite.

ExpenseReportExpense.taxCode

Looked up OpenAir field valueTax_location.netsuite_tax_rate_id for tax location record with Tax_location.id equal to Tax_rate.tax_location_id for tax rate record with Tax_rate.ticket_id equal to Ticket.id

Visible only if the Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form.

Active only when the following conditions are all met:

The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form.
Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax.
The NetSuite Tax Nexus Type [netsuite_tax_nexus] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to Canada or VAT. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax.

The Tax amount on the expense line is set to the Tax paid on the OpenAir receipt if the NetSuite Tax Nexus Type is VAT or calculated from values in the tax_rate record associated with the OpenAir receipt . if the NetSuite Tax Nexus Type is Canada.

ExpenseReportExpense.taxAmt1

Calculated value [taxAmt1] —

Ticket.total_tax_paid
Sum of Tax_rate.gst , Tax_rate.hst, Tax_rate.pst, Tax_rate.state, and Tax_rate.federal for tax rate record with Tax_rate.ticket_id equal to Ticket.id

Visible only if the Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form.

Active only when the following conditions are all met:

The Enable tax export on Expense reports box is checked on the Expense Reports (Expense Reports) export workflow settings form.
Your OpenAir account is configured to use tax locations on receipts or expense items to calculate tax.
The NetSuite Tax Nexus Type [netsuite_tax_nexus] custom field on the demographic form for the employee associated with the expense report in OpenAir is set to Canada or VAT. For United States nexuses, tax breakout information on expense lines is not supported in NetSuite — the expense line Amount should include tax.

ExpenseReportExpense.custcol_oa_gst_override

Calculated value — sum of Tax_rate.gst and Tax_rate.hst for tax rate record with Tax_rate.ticket_id equal to Ticket.id

Active and visible only if the Allow manual override of Canadian taxes on expense report export box is checked on the Expense Reports (Expense Reports) export workflow settings form.

The Goods and Services Tax (GST) and Harmonized Sales Tax (HST) is stored in the OpenAir: GST/HST [custbody_oa_gst_override] custom field on the NetSuite expense line item. See Allow manual override of Canadian taxes on Expense report export.

ExpenseReportExpense.custcol_oa_pst_override

Looked up OpenAir field value — Tax_rate.pst for tax rate record with Tax_rate.ticket_id equal to Ticket.id

Active and visible only if the Allow manual override of Canadian taxes on expense report export box is checked on the Expense Reports (Expense Reports) export workflow settings form.

The Provincial Sales Tax (PST) entered manually on the OpenAir receipt is stored in the OpenAir: PST [custcol_oa_pst_override] custom field on the NetSuite expense line item. See Allow manual override of Canadian taxes on Expense report export.

—

Ticket.exported

Not visible on mapping definition table

OpenAir adds a timestamp for the date and time the record was exported.

Additional Field Mappings

Important:

Review the following guidelines

You should contact your OpenAir Professional Services representative if you need to make changes to your integration configuration and field mapping definitions.
For advanced mapping usage and best practice guidelines, see Advanced Field Mapping Definition.
Familiarize yourself with the NetSuite and OpenAir technical documentation before attempting to set field mapping definitions using the advanced notation — See Schema Reference Documentation.

Review the following guidelines:

Reimbursable or Non-reimbursable Status — Exported expense reports can include both reimbursable and non-reimbursable receipts. OpenAir receipts have a Reimbursable (R) or Non-reimbursable (N) Status. To make sure you can bill customers for non-reimbursable expenses without reimbursing the employee, configure OpenAir NetSuite Connector to create an additional negative line for non-reimbursable receipts. See Automatically create negative Expense report lines in NetSuite for non-reimbursable expenses.

Important:
You should not use advanced mapping to set the value of the Non-reimbursable [expenseReportExpense.isNonReimbursable] field on the NetSuite expense line in function of the receipt Status [ticket.status] in OpenAir. Support for the Non-reimbursable field in NetSuite is limited and impacts the ability to rebill expense line items.