Managing Dynamics 365 OptionSets

When integrating or importing data it is often the case that a new value for an OptionSet will cause errors. For example when importing contacts we may set the account role code to the standard values, accountrolecode is an OptionSet for the contact entity.

Data Sync allows you to treat OptionSets like any other entity. You can preview, update and use them in lookups.

In this example the current OptionSet 'accountrolecode' contains the following items:

The data that we wish to import contains the following data with a new role value of 'Consultant'.

We can either create a new external lookup in a file or table with a list of acceptable account role values or directly synchronise from the source the values of the role into the option set value. The second option is preferable in most circumstances.

In this case, before we synchronise the data into Dynamics 365, we are going to synchronise in the distinct values of the role column using the following steps.

Sync Roles to AccountRoleCode

Connect the import source to the to the Source (A) side, in this case the dataset containing the new role 'Consultant'. Connect the target side (B) to the OptionSet accountrolecode by either dragging it from the connection tree onto the target column panel or right clicking and choosing Connect to Target (B).

You can access the optionsets in an entity from the connection tree by expanding the list folder under the entity node of the connection tree.

Configure the schema map so that only the role column is listed and set this to be the key value. Data Sync will warn about the key not being unique and will only return distinct values of the role column (Consultant, Decision Maker, Employee and Influencer).

Compare the source and target and then synchronise the changes.

The new value Consultant is now in the accountrolecode OptionSet.

info

If you wanted to delete an option set value using synchronisation you will need to publish the entity as deletes do not get published automatically.

Using the OptionSet Value

Data Sync LOOKUPA/B functions support looking up OptionSet values from Entity OptionSets. OptionSet Lookups work exactly the same as normal lookups, you can drag and drop them onto the column you are using to lookup with or you can use calculated columns.

In our example we wanted to import a list of contacts and set the accountrolecode to the correct value depending on the value in the Role column of the data source.

Drag and Drop Lookup

The quick way to do a lookup is to drag and drop the accountrolecode OptionSet from the connections tree onto the Role column in the source window.

This will open the lookup configuration window where you need to define the lookup target column to be name.

You will then be able to access the lookup columns int he source window by expanding the tree. Add the lookup id to the schema map and map this to accountrolecode in the target.

You can then run the compare and Sync to update the contact records.

Calculated Column Lookup Function

An alternative way to define the lookup is manually via calculated columns. Calculated column LOOKUPA/B functions differ from normal lookup functions by using a special convention to indicate that the source is an OptionSet related to an Entity.

For example if you were to create a lookup using the Company column to find the accountid of an account with the same name we would use the following if the CRM was on the data source B side:

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

To lookup an id value for a given OptionSet name we would use the following syntax entity|optionset in the from part of the LOOKUP function.

For this example the function would be:

LOOKUPB("id", "contact|accountrolecode", WHEN("name", Role))

Properties

The table below shows some of the connection properties of note, with a brief description for each.

Property	Description
CrmAuthenticationProvider	Dynamics 365 authentication provider scheme to use.
OrganisationServiceUrl	URL to your Dynamics 365 Organisation Service SOAP\WCF service.
Entity	Dynamics 365 entity name.
IsGlobal	Global OptionSet.
LanguageCode	Dynamics 365 label language code.
List	Dynamics 365 entity OptionSet, picklist, state or status list name.
Timeout	Service HTTP Request Timeout.