Lookup Functions Everywhere

We've revamped the lookup function capability in our next release so support many more lookup function scenarios. The new Lookup functions make it really quick and easy to lookup a value from a remote system based on a value from your source. The Functions are :- LOOKUPA - This function uses the Datasource A system details to lookup a value. For example if your Datasource A is SQL Server then this lookup will use the same connection string details but allow you to override the Table to look in.

For example the following Expression:

LOOKUP CompanyName FROM Suppliers WHERE SupplierID EQUALS SupplierID

Is written as a Calculated Column Function Like this:

LOOKUPA("CompanyName", "Suppliers", KV("SupplierID", SupplierID))

This is entered into a Data Sync Calculated Column like this.

The KV function is a Key-Value function that is used to pass parameters to multiple functions. Expanding on this function you can override the Data source Properties with a FROM parameter i.e you can pass in your own SQL Query through a KV Function. i.e.

LOOKUPA("CompanyName", FROM(KV("Command", "SELECT SupplierID, CompanyName FROM Suppliers")), KV("SupplierID", SupplierID))

You can also chain the conditions where you need to match on multiple values. i.e.

LOOKUPA("CompanyName", "Suppliers", KV("SupplierID", SupplierID), KV(“Country”, “UK”))

LOOKUPB – This is functionally equivalent to LOOKUPA except that it uses your Data source B (Target) connection details. For example where your target is Dynamics CRM you can write a Lookup Expression to lookup the AccountID from the account Entity where the names match like this. LOOKUPB(“accountid”, “account”, KV(“name”, name))

LOOKUPEX – This function can be used for simple lookups against a CSV, Excel or Data Sync Project. To lookup from an Excel Spreadsheet you can use this Function:

LOOKUPEX("Name", FROMEXCEL("C:\\Users\\Sean\\Documents\\Numbers.xlsx"), KV("ID", ID))

To Lookup from a CSV File you can use this Function:

LOOKUPEX("Name", FROMCSV("C:\\Users\\Sean\\Documents\\Numbers.csv"), KV("ID", ID))

And finally to use an Existing Data Sync project that is connected to any other data source you can use this function:

LOOKUPEX("Name", FROMDATASYNC("C:\\Users\\Sean\\Documents\\Numbers.dsprj"), KV("ID", ID))