Dynamics CRM: Adding a Country/Region option set using ISO 3166-1

Jun 10, 2011   //   by: Pedro Mac Dowell Innecco   //   Dynamics CRM/365  //  11 Comments

In Dynamics CRM, there is one particular bit that I always find myself customising, no matter the client size, region or industry: Country/Region fields.

By default in Dynamics CRM the country/region field is a text field, which means users could type anything they want. This of course can lead to confusion. The solution is to add an option set (a drop-down menu), which can be localised into multiple-languages. The problem is giving the values to the option sets. People often tend to give values starting from 1 to the options in alphabetical order (e.g.: 1 = Afghanistan; 2 = Åland Islands; […]). But this can often lead to issues as options are added or removed from the list.

What if I told you that I think I came up with the perfect solution, but that would mean that you should give specific values such as 4 for Afghanistan, 705 for Slovenia and 858 for Uruguay? I didn’t come up with these values out of a whim. They actually make a lot of sense. If you haven’t figured out the meaning of these values yet, read on for the explanation.

What are those values?

First and foremost I’d rather explain the logic behind those country/region values as to spare those more experienced with Dynamics CRM from the basics. The idea is quite simple. When giving values to the country/region entries in a country/region option set, we should use their respective ISO 3166-1 numerical code.

Besides the unique two-letter code used to identify countries and regions, which is best known as the code used for top-level domain specifications (e.g.: BT for Bhutan, DE for Germany, etc), each recognised country/region in the world also has a unique three-letter code (e.g.: BTN for Bhutan, DEU for Germany, etc) and a unique numerical code (e.g.: 64 for Bhutan, 276 for Germany). By using such unique numerical codes we can guarantee a pattern for identifying our country/regions and ensure the reliability of data, particularly with the addition or removal of options from the option set.

Download: I have created a Dynamics CRM 2011 solution entitled PMDCI’s Country/Region option set which provides a list of all countries and regions in accordance with the ISO 3166-1. You can download it by clicking here.

If you want to learn how to create your own country/region option set in Dynamics CRM 2011 and use it throughout your entire customisation, then keep on reading.

What is the issue?

As mentioned previously, in Dynamics CRM, by default the field used in several entities to hold the country/region value for an address is a text field. This can lead to several issues, such as typos in country names, or different spellings. For example, you could end up with accounts having the country of their primary address as US, USA, United States, United States of America… Or perhaps even ??????? if you have a global multi-language implementation. Now, imagine trying to run a sales pipeline report that group accounts based on their country/region. Not only this is a nightmare for business intelligence, but also a hassle for record management.

The case for option sets

A solution for this problem is to replace the country/region field used in several records in Dynamics CRM with an option set (i.e.: a drop-down list). This way you can let users select the country/region from a list, thus avoiding typos or alternate names. Moreover, option sets can be localised into multiple languages, when users see the address of an account that is based in the USA, they will see the country/region as United States of America if they’re using Dynamics CRM in English, Estados Unidos da América is they’re using it in Portuguese, Sjedinjene Americke Države is they’re using it in Croatian, and so on.

An option set has multiple options which is set by you, the Dynamics CRM system customiser, from which users can select the appropriate option. For each option added into the set, you must specify a label and a value. The value is an integer number, which is the value that is actually referenced in the record that is using the option set. The label is the text that represents the integer value, which can be translated into other locales.

For example, suppose that an English user of Dynamics CRM selects the option San Marino for the country/region of an account. when customising CRM, I set the value for the San Marino option as 674. The value referenced for country/region in this account would be 674. An English user would see the country/region as San Marino, while a Chinese user would see it as ???? and so on.

Creating the option set

We create our option sets under Settings | Customizations from the Dynamics CRM navigation pane. We must add a option set either into the default solution or into a custom solution that we create. In Dynamics CRM 4 we had to create an individual country/region option set for each entity where we would like to add it to. Now in Dynamics CRM 2011 we have the option to create global option sets that can be referenced in these entities. Therefore, updating the global option set will reflect in all the entities where it is being used.

Here is how to do it:

  1. Log-in into Dynamics CRM with an account that has system customisation privileges.
  2. In the navigation pane, click on the Settings module.
  3. In the navigation pane, under System, click on Customizations.
  4. In the workplace, click on Customize the System to open the customisation window.
  5. In the customisation window menu bar, click on New and then select Option Set to open the New Option Set window.
  6. The New Option Set window will load. Create an option set with the following settings under Schema:
    • Display Name: Country/Region
    • Name: new_CountryRegion
    • Description: Country/Region option set to be used in customisations
  7. Under Options we must add the country and regions we would like to have available in our option set. As explained previously, the idea here is to add options as per the ISO 3166-1 numerical code.
  8. Click on the green plus icon (+) to add an option.
  9. In Label, type the name of the country or region to add (e.g.: Brazil).
  10. In Value, type in the respective ISO 3166-1 numerical value for the country or region you are adding (e.g.: 76 for Brazil). Click on OK to ignore the message about the option value being out of the solution’s option value prefix range.
  11. You must repeat steps 8 to 10 for each country/region you want to add into your option set. Remember to click on Save periodically.
  12. Click on Save and Close once you’re done with your list.
  13. Back on the customisation window, select the option Option Set from the Component Type drop-down.
  14. In the list of components, select the newly created Country/Region  option set, then click on the Publish icon.

We have now published our option set and it is ready to be used in our entities.

Using the option set

The next step would be to add the Country/Region option set into the entities form where we would like to use it. What I would normally do is add the country/region option set right where the original country/region text field is located, then remove or hide the country/region text field.

Next I explain how to add the country/region option set for the account entity. You can follow the same instructions to add it to other entities. Here is how to do it:

  1. Back at the customization window, in the navigation pane click on the arrows next to Entities to expand the entities list.
  2. Under Entities, look for the item Account and click on the arrow next to it to expand its items.
  3. Under Account, click on Forms.
  4. The list of forms for the Account entity will load. Double-click on the form named Information (Form Type: Main, State: Managed).
  5. In the form designer for Account’s Information form, click on New Field in the Field Explorer pane located on the right-side.
  6. The New Field window for the Account entity will load. Add the option set with the following settings:
    • Display Name: Country/Region
    • Name: new_CountryRegionList
    • Requirement Level: Business Required
    • Field Security: Disabled
    • Auditing: Enabled
    • Description: Country/Region name for address
    • Type: Option Set
    • Use Existing Option Set: Enabled
    • Option Sect: Country/Region (the option set we created previously)
    • Default Value: Unassigned Value (you can select a default option here if you wish)
  7. Click on Save and Close to save the option set and go back to the form designer for Account’s Information form.
  8. In the form designer for Account’s Information form, locate in the Field Explorer pane the Country/Region field we just created and drag it right under the ZIP/Postal Code.
  9. Select the original Country/Region text field and press delete to remove the field from the form.
    NOTE: If you want to perform business logics (e.g.: JScript) involving the original Country/Region field, the field must be included in the form. If that is the case you can hide the field by double-clicking on it and deselecting the option Visible by default.
  10. Click on the Save button located in the Ribbon bar at the top of the form, then click on Publish.

The custom option is now added into the Account entity and ready to be used. The same process can be followed to add our custom Country/Region option set into other entities, such as: Leads, Contacts, Competitors, Addresses, and so on.

However, there is none final task we must do. When creating records from other existing records, the values of the source entity are transferred into the new entity. For example, when qualifying a lead into an account, the address information is inherited from the lead by the newly created account.

Create mappings

After adding our custom Country/Region option set into all of our required entities (e.g.: Accounts, Contacts and Leads for a start), we can notice that accounts and/or contacts created from qualifying leads won’t inherit the country/region that has been specified in the source lead record, like with the other address fields. In order to achieve this we must establish a mapping between the Country/Region option set for the Lead entity to the Country/Region option sets for the Account and Contact entities.

Considering that we have also added the Country/Region option set into the Lead entity, here is how to map the field from Lead to Account:

  1. Back at the customization window, in the navigation pane click on the arrows next to Entities to expand the entities list.
  2. Under Entities, look for the item Lead and click on the arrow next to it to expand its items.
  3. Under Lead, click on 1:N Relationships.
  4. The list of 1-to-Many relationships for the Lead entity will load. Double-click on the Schema Name named account_originating_lead (Primary Entity: Lead, Related Entity: Account)
  5. In the Lead to Account relationship window, click on Mappings located under Common in the left pane.
  6. The list of field mappings between Lead and Account will load. Click on the button New to add a new mapping. The Create Field Mappings From Lead to Account window will load.
  7. In the Source Entity Fields pane (left-side), select the field new_CountryRegionList.
  8. In the Target Entity Fields pane (right-side), select new_CountryRegionList as well.
  9. Click on OK to close the Create Field Mapping window. Then back at the Lead to Account relationship Window click on Save and Close.
  10. Back at the customization window, in the navigation pane click on the item Lead under Entities to show the entity’s main definition window.
  11. Click on Publish to publish the mapping we just created.

Repeat this process to create the mapping of our Country/Region option set from Lead to Contact.

Wrapping up

The main idea behind this post was to introduce the best practice of using ISO 3166-1 for creating our Country/Region option set. In the process we also introduced the use of global option sets in Dynamics CRM 2011, plus the mapping of fields between related entities. In further posts I’ll explain how to improve the usability of this field through the use of JScripts.

Tags:

11 Comments

  • ablaviation 3 September 2011 at 20:37

    Hello Pedro,
    If you already have the field country/region as text and have records already in the database, how can you change the filed to an option set?
    Thank you

    • pmdci 4 September 2011 at 12:35

      Hi ABL,

      Thank you for the reply. There are two ways that I can think of from the top of my head:

      1. Mass-edit of multiple records: You can filter or sort the view of your records (e.g.: accounts or contacts) so you can select all the appropriate records, click the edit button and select the appropriate country/region from the drop-down menu you have added. I did this once for a system with about 2,000 records and it took me about 10 minutes.

      2. Process (dialog or workflow): You could create a dialog or on-demand workflow to go through your records and set the appropriate country/region.

      The other option would be to export to Excel and then use the import tool in Dynamics CRM to update your records. To to this you must import the records to include their UID string. There is an article about Dynamics CRM 4 that explains how to do it. I reckon the process will be similar in Dynamics CRM 2011 (if anything it would be simpler since the import functionality has been greatly enhanced in the current version). http://www.crmsoftwareblog.com/2010/05/using-the-…

      Hope it helps!

      Cheers,
      P

  • ablaviation 4 September 2011 at 16:39

    Thank you very much Pedro.
    For the workflow option, how can you run the workflow on the already existing accounts?
    kinldy,

    • pmdci 4 September 2011 at 19:49

      Hi.

      In this case you want to create a workflow that doesn't start automatically when a record is created or updated. You want a workflow that can be run on demand. You can set this option when you create the workflow.

      When looking at the list of your records for the entity in question, you must then click on the Run Workflow button and select the workflow you created.

  • ablaviation 4 September 2011 at 21:27

    Thank you very much for your kind help.

    Have a great day,

    • pmdci 14 September 2011 at 17:04

      Hi ABL,

      Just one point.

      You might want to keep the original country/region fields in your forms and just hide them by default. The idea is that you can populate the value of the original country/region field with the text value of the option set you have added. You can easily do this with a jScript.

      The idea behind this is to allow reports and other third party applications you might want to integrate with Dynamics CRM (eg: Google Maps, or Microsoft Bing Maps) to be able to easily read the country/region information.

  • Dave 6 October 2011 at 15:30

    Did you ever export that solution for attaching to this post?

    • pmdci 6 October 2011 at 15:37

      Dave,

      This is something I am working on this week. I am finishing the Part 2 of my sales pipeline post (95% done). I promise that my next bit will be to export the Option Set.

      Thank you for your patience and sorry for the delay! :-p

  • Corbett 1 February 2012 at 17:03

    Anyone have a good idea on how to create the option set with hundreds of countries without having to manually enter each of them in?