Managing Dynamics CRM Option Sets

DS3 allows you to treat Option Sets like any other entity. You can preview them, update them and use them in lookups.

When integrating or importing data it is often the case that a new value for an option set will cause errors. For example when importing contacts we may set the account role code to the standard values. For example accountrolecode is an option set for the contact entity. DS3 allows you to treat Option Sets like any other entity. You can preview them, update them and use them in lookups.

Preview

The current accountrolecode contains the following items:

AccountRoleCode Data

The data we want to import contains the following data with obviously a new value ‘Consultant'.

Consultant Data

We can either create a new external lookup in a file or table with a list of acceptable accountrolevalues 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 CRM, we are going to synchronise in the distinct values of the role column using the following steps:

  1. Create a new project called ‘Contact_sync_role_to_accountrolecode' or similar.
  2. Connect the import source to the to the Source (A) side.
  3. Connect the target side (B) to the option set accountrolecode by either dragging it onto the target column panel or right clicking and choosing ‘Connect to Target (B)'.

Connect to Target B

  1. Add only the ‘Role' column and map to the name in the option set.
  2. Set the ‘Key Column' in the schema map to be the role column. In this case DS3 will warn about the Key not being unique and will only return distinct values of the Role column. In this case Consultant, Decision Maker, Employee and Influencer. The mapping should look like below.

Key Column

6.Compare the source and target

Compare A to B

  1. The new value ‘Consultant' is now in the accountrolecode option set.

Note: If you wanted to delete an option set value using synchronisation you are going to have to publish the entity as deletes do not get published automatically.

Using the Option Set value during a synchronisation
In our example we wanted to import come contacts and set the accountrolecode to the correct value depending on the value in the Role column of the data source. We can achieve this in two ways.

Using an Option Set as a lookup using drag and drop
Option Set lookups work exactly the same as normal lookups, just drag and drop them onto the column you are using to lookup with.

Darg and Drop Lookup

Using a calculated column Lookup Function Calculated column lookup functions differ from normal Lookup functions by using a special convention to indicate that the data source is an Option Set related to an entity.
For example if you were going to create a lookup using ‘Company' column to find the accountid of an account with the same name we would use the following if the CRM was on the B side:

LOOKUPB("acountid","account",WHEN(“name",Company))

But in this case we want to find the id of a option set accountrolecode with the same name as our role code:

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

As you can see from this example we have referenced the option set of the entity by using the convention entity|optionsetname.

Calculated Column


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.