Looking Up Values in other Entities

Managing relationships between entities in Dynamics 365 is a key requirement. Data Sync makes this easy by allowing you to lookup values within other entities and return these within your schema to enable the joining of records within your existing Dynamics entity tables.

The lookup columns returned can be added to the schema directly, used within calculated column functions or used within datasource filters.

Below we will cover the different ways of creating a lookup in Data Sync.

Lookup Methods

There are a few ways to do a lookup in Data Sync: Drag and drop from the connection library, using the lookup button in the data source toolbar, and manually via calculated columns.

Drag and Drop

If you have the connection stored in the connection library, or you are using a CSV or Excel file (for example), then you can drag the connection onto the source column you want to lookup. This opens the lookup configuration window where you can change the name, and define the column that joins the two datasets.

When the lookup configuration window opens make sure to set the correct lookup target column that joins the two datasets. In this case it is accountid of hte account entity that joins the parentcustomerid of the contact entity. Make sure the data type is correct for the column being used and click OK to create the lookup.

The columns are then loaded into the source column list and these can be viewed by expanding the tree view and adding them to the schema map as required.

Lookup Button

If the connection has not already been created then you can use the lookup button in the data source toolbar to connect.

This will open the connection window where you can connect to the data source you want to lookup the values in. Once you have connected the configuration window will open, where you can set the target column that joins the datasets and the data type of that column. Clicking OK creates the lookup and the columns will be visible in the source column list as shown in the drag and drop section above.

Calculated Columns

An alternative way, where you can have more control is to use calculated columns. To use these click onto the Add Column button in the data source toolbar.

You should start by entering a name for the column to be created. Please note column names need to be unique otherwise an error will be thrown. One way to ensure uniqueness is to add fx_ as a prefix.

Then you need to set the data type, e.g. string, and then use the in-line LOOKUPA/B functions to return the values.

One example using the LOOKUPB Function to get the accountid from the account entity where the name of the account equals the CompanyName value found in the source. This is when the target datasource is connected to any entity in Dynamics. If your source is connected to Dynamics instead then you would use LOOKUPA.

The syntax for the LOOKUPB function is :

object LOOKUPB(string column, FROM(from), keyValue)
object LOOKUPB(string columnName, LFROM from, params KVAL[] keyValues)

An example of how this cna be used to return the accountid can be seen here:

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

If you need more than one column to be returned then you would need to repeat this process to return the value required.

Option Set Lookups

You can also lookup values in Option Sets in Calculated Columns by using the format entity|optionset as the data source.

For example looking up the id value for the Consulting industrycode in the account|industrycode optionset.

LOOKUPB("id", "account|industrycode", WHEN("name", "Consulting"))