Fun with NetSuite Connector Advanced Mapping
There are times that only a NetSuite Connector advanced mapping will do when trying to export the right data to NetSuite. There are many advanced mappings available to meet your needs and some can be quite complex.
In this expert quick tip we will look at one that isn’t complex and that can be quite useful – Concatenation.
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. The target field is listed first then the source field(s) and fixed string value(s) you want to include in the order needed.
target_field source_field_1,’fixed string value’,source_field_2
Concatenation can also be used with looked up OpenAir field values. The following example uses a lookup to find the name of an OpenAir user and include it, other fields from the OpenAir expense report line, spaces, and an @ sign, and export all of them to populate the MEMO field on NetSuite expense report lines.
Our goal is to have the following information displayed in the NS expense report line MEMO field:
USER NAME followed by a space; QUANTITY; the @ sign preceded and followed by a space; COST followed by a space; DATE of the expense
We would add a mapping row to the NS Connector Expense Reports > Expense Line like the one below.
Click on the sprockets, add, click OK, then save the mapping below. Note that the fixed values, in this example the spaces and the @ are all in separate quotes ( ‘ ) as required.
#CONCAT
OA_FIELDS user_id,quantity,cost,date
NS_FIELDS memo
memo lookup=user_id:lookup_table=user:lookup_by=id:lookup_return=name,’ ‘,quantity,’ @ ‘,cost,’ ‘,date
The result – when the OpenAir expense report is exported, in NetSuite the expense report line MEMO field will have the following information.
Owen, James 1.00 @ 354.95 2024-05-24
For more information on this and other advanced mappings please consult the NetSuite Connector Guide.
That’s all for now! We hope you find this information helpful as you continue using NetSuite OpenAir.
