Incremental Lookups

Release 3.0.986+

When performing a Lookup into a Large data set, this can take a long time since the target data set is loaded before the lookup can be returned. This generally works well when your source is the entire data set or the target lookup is small.

When you use Incremental Mode in data sync your source may return a few records and need to lookup into a target data set of millions to get the lookup value. This is not optimal since it requires a full load of the target lookup data set.

We have created LOOKUPAINCREMENTAL and LOOKUPBINCREMENTAL these functions are exactly the same as LOOKUPA and LOOKUPB except they work incrementally. The result of each lookup operation is cached so that the same value is not looked up multiple times.

These functions are supported with the following Data Sync providers.

• Dynamics CRM
• SQL Server
• OleDB
• ODBC

For Dynamics CRM this Incremental Lookup will execute a FetchXml query for each new value required from the Lookup. Therefore you want to only do this on a few source records to reduce the server round trips required to lookup each value.

Example

Using LOOKUPBINCREMENTAL to lookup the accountid from the account entity when the account.name equals CompanyName in the source data.

LOOKUPBINCREMETAL("accountid", "account", WHEN("name", CompanyName))

The function signature of LOOKUPAINCREMENTAL is exactly the same as LOOKUPA so you can easily swap them and see the benefit right away.

For your custom providers that want to support the Incremental Lookup function, you will need to implement the IDataSourceLookupIncremental interface.

Then implement the GetLookupValue method and return the value. You do not need to implement the caching this is done for you.

public object GetLookupValue(DataLookupSource source, string column, IDictionary< string, object> conditions) 
{
    return null;
}