Look Up Data During Runtime¶
Use Case¶
A common need is to pass data to a target system which must be 'enriched' with data that is not native to the source. The source may contain a value that can be used in a lookup that is done on-the-fly.
Note
This design pattern uses Design Studio as an example; you may apply the same concepts in Cloud Studio using similar steps.
Example 1¶
This operation flow searches for new Credit Memos in NetSuite, looks up the SFDC customer id, then inserts the credit memo into SFDC.
The transformation maps fields to a flat file format that is written to a temporary target.
In this script, the internal id is passed to a global variable, and launches an operation that performs the lookup.
The global variable is passed to a NetSuite query
The transformation maps the externalId (which is the SFDC Id assigned to that NetSuite customer in a different operation) to a simple file format.
The global variable is assigned to the field value.
In the transformation, the value in 'entity' is passed to the CcccPppO2B__Account__c, a required field to insert a Credit Memo.
Example 2¶
The first operation searches for new NetSuite Sales Orders, and upserts the data to SFDC.
The 'Secondary_Customer__c' field contains a lookup mapping:
A very useful Jitterbit function is SFLookup, which allows Jitterbit to pass soql to SFDC and get a value in return. This logic checks if a secondary customer ID is in the source, does a lookup and passes that value to the target. Note the use of local variables rather than global variables.
What is also interesting is the use of relationship lookups. For example, in a previous operation, Assistant_Technician_1/ NetSuite_Internal_ID values in SFDC are populated. In this transformation, the relationships to FX5__Job__c are exposed. By mapping the NetSuite_Internal_ID__c under the Assistant_Technician_1__r (relationship), SFDC will perform the lookup of the SFDC ID for us. Otherwise, it would be necessary to build SFLookup functions.
Example 3¶
In this example, one of the endpoints is SQL Server, and the endpoint holds operation timestamp values in a table.
In this script, the DBLookup() function is used to get the current timestamp value to populate a global variable, which is used in the dependent operations.