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))