Lookups
Lookups are an important feature of Data Sync as they allow you to look up values in other Data Sources like a foreign key in a SQL database. One of the best things about Lookups in Data Sync is that it is not limited to looking up against the same type of system. For example, your Source data might be Dynamics CRM, and you can Lookup into a CSV file if you wish.
When creating a Lookup, the process creates an In-Memory Table of all the values in the Lookup Data Source. This table provides for a Fast Lookup without requiring a round-trip on every row to evaluate the lookup. If the Lookup Data Set is large, it can take a little while initially to create the Lookup Data Source.
For very large lookup data sets, it can be more efficient to use the LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL functions with calculated columns. These functions work similarly to the regular LOOKUPA and LOOKUPB functions, but they load the lookup data in batches instead of all at once. This can significantly improve performance when dealing with large amounts of data.
Create a Lookup
To create a Lookup, select the Column in the Data Source that you want to lookup values from. Then select the Add Lookup button from the Data Source toolbar to open the connection window and then the lookup configuration window.
Alternatively, you can drag and drop from the Connection Library onto the Column in the Data Source to create a Lookup. This is the quickest and recommended method as the connection details will be updated if the connection is modified (e.g. password is updated)
If you haven't used the connection library, you will then need to configure the connection to your intended data source to lookup in.
Next you need to define the Lookup Connection properties, you should set the Data Type and the Column in the Target that will Join the Lookup Records.
Once the Lookup is configured, you will notice a > against the column in the Data Source window. CLicking on this will expand the tree view and reveal the Lookup Columns below.
You can then use these columns just like any other column in your source and add them to your schema map.
Delete a Lookup
To Delete a Lookup, select the Lookup column and then press the Delete Lookup button from the data source toolbar.
Calculated Column Lookups
The major Data Connectors support Lookups via Calculated Column expressions, which create an internal dictionary of values that are used at runtime to return the values. These lookups re-use the connection on either the source or the target and are ideal when you need to get a value from your target system.
However, if you need many column values from the same lookup, it's better to use normal lookups.
You can use the functions LOOKUPA and LOOKUPB to return lookup data. Or LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL to incrementally lookup the data.
LOOKUPA uses the Data Source (A) connection whilst LOOKUPB uses the Data Target (B) connection.
For example to lookup the value of the accountid in the Dynamics CRM account entity where the name field matches the Name column in your data source you would write the following expression.
LOOKUPB("accountid", "account", WHEN("name", Name))
This is loosely equivalent to the following SQL : SELECT accountid FROM account WHERE name = ?
You can also add additional WHEN parameters to the LOOKUPA/B function to create an AND expression, for example:
LOOKUPB("accountid", "account", WHEN("name", Name), WHEN("statuscode", MyStatusCode))
This is loosely equivalent to the following SQL : SELECT accountid FROM account WHERE name = ? AND statuscode = ?