On the Integration tab, click on Additional Columns tab
Click the Add Additional Column button
Name – Enter a name for the column
Type – Select SuiteQL Lookup from the Type dropdown list
In the Query editor create a SuiteQL query
Drag any source columns or Global Variables into your script
Use NVL() and an alias to make sure every column is available in Return Fields even if the first record doesn’t contain a value in that column
-- Required Permissions (Customers, Address, Perform Search)
SELECT
NVL(customer.entityId, ' ') AS customer_EntityId,
NVL(customer.accountNumber, ' ') AS customer_AccountNumber,
NVL(customer.companyName, ' ') AS customer_CompanyName,
NVL(customer.externalId, ' ') AS customer_ExternalId,
customer.id AS customer_InternalId,
customer.entityNumber AS customer_EntityNumber
FROM
customer
WHERE
customer.externalId = '<<AccountNumber>>';
Return Fields – Select the field(s) that will be returned from the Lookup
If No Data – Select the required option if no data is found
Option
Description
Continue
SmartConnect will continue with the integration even if no data is found
Restrict line out if data exists
If lookup DOES return data, the integration will not send any lines that use this lookup
Restrict line out if no data exists
If lookup does NOT return data, the integration will not send any lines using this lookup
Return failure for current integration line
SmartConnect will return a failure for that current line that didn’t find a value
Return Null
SmartConnect will return a null value
Stop processing integrations
SmartConnect will stop processing all records if no data has been returned from the lookup
Process Unmapped Column – If the lookup should be ran even when it is not mapped on the line
Use this if the result is only used in another additional column
Include lookup result in scripting – If the result of the lookup should be available to use in Calculated Columns
Click Save to save the column settings
Feeling stuck? Get the support and guidance
you need to help you power through any data challenge
We're on your integration team. Connect with our people and let us
know how we can help you.
