Creating Lookups

The below tutorial will cover how to add lookups to your project.

Lookups are an important feature of Data Sync, these allow you to lookup values in other Data Sources much like a Foreign Key in a SQL Database. Data Sync is not limited to looking up against the same system type. For example your Source data might be Dynamics CRM and you can Lookup into a CSV file if you wish.

The Lookups create an In-Memory Table of all the values in the Lookup Data Source, this provides for a Fast Lookup without requiring a round-trip on every row to evaluate the lookup. Since the entire Data Source is loaded if the Lookup Data Set is large it can take a little while initially to create the Lookup Data Source.

There are two ways to create lookups, either through the data source window or by drag and dropping from the connection library.

Drag and Drop

Simply find the connection in the connection library and drag this onto your chosen data source column.

Data Source Window

Select the column in the data source window that you wish to lookup values from and then select the Add Lookup button from the data source toolbar.

This will open the connect data source window where you can choose the provider you wish to connect to for the target of your lookup. Complete all the required credentials to connect.

Configure Lookup Connection

Now we need to configure the lookup connection properties. Set the data type and the target column that will join the lookup records.

Once the lookup is configured there will be a + next to the column you previously highlighted. Click on this to show the lookups available. These columns can now be added to your schema map like any other columns in your source.

Deleting Lookups

To delete a lookup, select the lookup and then press the Delete Lookup button from the data source window toolbar.

Calculated Columns

The major connectors also support lookups using calculated column expressions.

These lookups re-use the connection on either the source or target and are great for when you need to get a value from the target system. These lookups create an internal dictionary of values that are used at runtime to return the values.

LookUp Functions

The major lookup functions that you can use within calculated columns are:

LOOKUPA

Lookup a value from data source A.

LOOKUPA("accountid", "account", WHEN("name", name))

The lookup function above is similar to the SQL statement:

SELECT accountid FROM account WHERE name=?

LOOKUPAINCREMENTAL

Lookup a value from data source A incrementally. This can be used in the same way as LOOKUPA, but is best for large datasets as the result of each lookup is cached so that the same value is not looked up multiple times.

LOOKUPB

Lookup a value from data source B.

An example of how you would use this is:

LOOKUPB("accountid", "account", WHEN("name", name))

The lookup function above is similar to the SQL statement:

SELECT accountid FROM account WHERE name=?

LOOKUPBINCREMENTAL

Lookup a value from data source B incrementally. This can be used in the same way as LOOKUPB, but is best for large datasets as the result of each lookup is cached so that the same value is not looked up multiple times.

For other lookup functions please visit our documentation site.

Troubleshooting

Lookup doesn't work/ isn't returning data

Please check that your lookup connection is correct. Has the right data type and linking column been selected?