MS365/MSCRM tip #1: Quickly create a large number of available values for Option Sets - TKI Solutions

SHARE



 
Why standardize information?

Having standardized values is important for any software system. It will basically allow you to keep your database clean and to quickly identify any relevant information. Think about generating a report or trying to extract all record that match a certain criteria in a file. It is easy as long as you have only one option for the same value. But if you have multiple options (variations) for the same value, then things get complicated. You will spend more time searching and you might get incomplete data.

One of the issues that occur often is the way people type in countries names. For the Country field, United States of America, United States, USA and US are a set of values that actually mean the same thing and therefore should be standardized.

 

Standardizing data in Microsoft Dynamics 365 (CRM)

In Microsoft Dynamics 365 (CRM) as well as in many other software solutions one easy way to standardize data is to create Option Set (Drop-down) type fields. If you put an Option Set inside a user’s form for the Country field, he/she will be forced to choose the standard value.

But, if you will try to create an Option set in Microsoft Dynamics 365 (CRM) you will notice that the values should be entered manually, one by one. And in cases like the example above, where there are a lot of options (a lot of countries), it will take quite a while.

 

The workaround – importing values in an Option Set

Unfortunately, Microsoft Dynamics does not offer a direct method of importing these values. But there is a trick that you can use. Whenever you try to import actual data (records) within an entity and cover an Option Set field, in case a value from the import file is in the Option Set, CRM will recognize it and import it accordingly. But if it is not, the system will offer you the option to use the source option values, which means creating a new value in Microsoft Dynamics for each of the unrecognized values in the source file.

 

The workaround – step by step

Therefore, all you have to do is to create the Option Set for the desired entity and then import a record in that entity for each of the Option Set’s missing values. Let’s say that you create the Country field on the Contact entity and set its type to Option Set. After you do that, you can create a basic CSV file and have the column Country into it. Each Country should appear only once in this file and for each of the rows, you will also want to add information for the required fields. If you do not add that information the import will not be accomplished. You should also pay attention in case you have any duplicate detection rules that target those additional, required fields, and put unique values in there. You should not worry about adding fictive information in those fields, because you will delete the records that were imported after the job finishes anyway.

In this example I have took into consideration that I have a Duplicate Detection Rule that detects contact with the same Full Name (First Name + Last Name) so I made sure that they all have different Full Names.I have also put “Contact” as the first name. This way, it will be easy for me to query the database later (by using First Name = “Contact”), identify these fictive Contacts and delete them.
 

 

And this is what the Import Wizard window is showing when I try to import this file and click on the details of the Option Set mapping. You will notice that it does not offer any Option under Dynamics 365 Filed Values (because the field was created but no value was provided for the options) and instead it gives each option within the import source file (the csv file that we used) under the Source Option Values.
 

 

Considerations for this workaround

One important aspect that you need to keep in mind is that this method will not work for Global Option sets. So if you need to have this Country Option Set for multiple entities (Contacts, Leads, Accounts), you will have to create a separate field for each of those entities (Local Option Sets) and to perform three imports, one for each entity.

The Local Option Set has some disadvantages in contradistinction with the Global Option Set. Probably the biggest one is that you have to make modifications within every field inside an entity when something changes in the available options list. So you must always make sure that you do that and remember to do it for each entity.

 

Conclusion

This is one of the most useful workarounds for Microsoft Dynamics 365 and Microsoft Dynamics CRM. It allows you to create Option Sets regardless of the number of available options and to quickly insert each of them, through record imports. This way you save precious time for yourself and you get the required data standardization.

If you required additional assistance or advice don’t hesitate to contact us at office@tkisolutions.com
Stay tuned for future tips & tricks on how to overcome system limitations and work more efficiently.