Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Adding a State or a Province Field that is a Dropdown or Lookup in CRM 2011

Post Author: Joe D365 |

using an option set or lookup in CRM 2011Dear Joe CRM,

Since the state and province fields in Contacts, Leads and Accounts are single line of text, our users are able to enter states and provinces however they want. This means Minnesota might be entered "Minnesota," "Minn," or "MN," depending on who enters it. This makes searching for people or accounts by state unreliable. I am thinking of putting some structure around states by making it an option set or a lookup in CRM 2011. Do you have any recommendations on best practices on adding this field?

Sincerely,

Altered States

Dear Altered States,

This is a pretty common question. Dynamics CRM was designed this way for a few reasons. First, this better supports companies that have contacts and accounts in various countries around the world. Second, the integration with Outlook requires the field be a single line of text for the field to sync with Outlook contacts. This means there are drawbacks to changing the state/province field to a pick list or lookup and must weigh your options carefully. Here are my recommendations if you add this field.

Option Set vs Lookup

Your first decision is whether to make it an option set (pick list / dropdown menu) or lookup to a custom entity. Let's take a look at each option.

OPTION SET:

Using an option set to create structure involves first creating a global option set, and then using it when creating fields on each entity where you want to use the state/province dropdown, and adding the field to the form.

Steps:

  1. Create a global option set with all the states and provinces as values
    1. Click on Settings | Customizations | Customize the System
    2. Click on Option Sets in the Left Navigation bar | New
      option sets in CRM 2011
    3. Fill out the appropriate options | Save and Close
  2. Create an option set type field in each entity you want to use the state/province dropdown.
  3. Add the field to the form

Let's take a look at the pros and cons of using a global option set for the state/province field.

Pros Cons
Setting up a global option set is fairly straightforward, making this the simpler option. The 50+ values make the dropdown list long, forcing users to scroll when making a selection.
There is no additional set up using a global option set. The option set is limited to one value. So if you use the two digit state abbreviation, users will not have a reference to what AR means.

LOOKUP (Create a custom entity)

You can alternatively create a look up to a custom state/province entity where each record is a state or province. This process involves creating a custom entity, creating relationships between it and the entities where you'll use it, creating the records, and giving users permission to view append the state/province records.

Steps:

  1. Create New Entity making sure all the options under Communication & Collaboration are unchecked.
    lookup in CRM 2011
  2. Create the fields you want to reference here. More than likely, you will want the capture both the two digit postal code and full name in separate fields.
  3. Create a 1:N relationship between the State/Province entity and the entities where you want to use it (i.e. Contact, Lead, and Account).
  4. Add this new field as a look up on the Primary entity's main form.
  5. Create the state and province records in the new entity.
  6. Grant all your users read and append to rights to the new State/Province entity.
Pros Cons
Since it's a custom entity, it can contain separate fields for the state abbreviation and the state's full name. This is helpful for users who are not familiar with state abbreviations. Setting up a custom entity requires more planning than an option set.
The system can be set up so users can search by either the state abbreviation or the full name of the state. You must grant permissions to the custom entity.

BEST PRACTICE RECOMMENDATIONS:

Regardless if you choose a global option set or go the lookup route; here are my best practice recommendations that you must consider when creating a new field to house state and province information.

  • Keep populating the out-of-the-box field. Contacts synchronize to Outlook using the out of the box state/province field. This allows you to get the full functionality from your Outlook client. There are two methods you can use to automatically populate this field.
    • Javascript
      • Javascript does its magic when the user populates the state/province field on the form. In order for javascript to work on a field, you must keep both fields on the form, but hide the out-of-the box field so it doesn't create confusion. The first drawback to this solution is that it doesn't work when you perform a bulk edit to the state/province field on existing records since you're not opening the form where the script is run. Also, javascript requires coding, making this a more complex solution.
    • You can alternatively use workflow to populate the out of the box field state/province field. This is a fairly simple workflow, but must be recreated for each entity where you use the new state/province field. Due to the way workflows run, it may take a few seconds for the state to populate the out of the box field.
  • Use a global option set if you choose the option set method. There are at least 13 places in Dynamics CRM that use the state/province field and reusing it creates consistency and reduces administration.
  • Make sure the cascading behavior is referential if you choose the lookup method.
  • Map the new data field from the Lead entity to the Contact and Account entities. This ensures the data persists when you qualify a Lead. I also recommend mapping the Account to Contact field (this occurs in the out-of-the-box fields.
  • Make the value of Searchable in the new field "No." This removes it from the users' searching to avoid confusion.

You should also check out the blog Global Option Sets in Dynamics CRM 2011 to get the basics of global option sets. You can also take a look at our post on the differences between Global Option Sets and Custom Entities.

Keep in mind it's always wise to weigh your business reasons for changing the out of box functionality and to take the extra steps of automatically populating the out of the box fields.

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

2 comments on “Adding a State or a Province Field that is a Dropdown or Lookup in CRM 2011”

  1. In 2015 online what is the best option: Create option set or lookup? Also, what are the steps in 2015 to create these options? Thx.

    1. Hi - The best option varies a bit by client. In the blog above we have a box with pros and cons for each option. Most of our clients go with the global option set but a few have done the lookup entity.

PowerObjects Recommends