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.

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

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.

Note: 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

Drag and drop the 'accountrolecode' OptionSet from the connections tree onto role, and set up the lookup to be based on 'name'. Add the lookup to the schema map and complete the mapping.

Calculated Column Lookup Function

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.

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

Properties

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.