When you want to control the values that a user can use in a particular field in Microsoft Dynamics CRM, you will either create an Option Set field or a Lookup field. This is always beneficial for consistent reporting and searching. Additionally, there may be times when there are two or more such fields where you want to limit or filter the choices of a second field depending upon the value selected in the first field. An example of this is the Countries and States/Provinces fields. When a user selects a particular country, then you would want to only show the states/provinces associated with that country in the State/Provinces field. We call this either Dependent Options Sets or Filtered Lookups. Another example might be Regions and Territories with Region.

You can achieve the same objective with either Option Sets or with Lookups but through different means, and in today's blog, we will show you how!

Generally it is simpler to use entity lookups and filtering. Here are some arguments:

• The dependent filtering of Lookups can be achieved with out-of-the-box configurations and no custom JavaScript code.
• Lookup attributes can contain additional fields if needed, for example, Country Name , and abbreviation.
• Values in lookups can be added to or changed without modifying any JavaScript.
• Large data sets can be imported into lookup entities.

If you want use Dependent Option Sets, then you will need to use custom code applied to the Option Set fields. The code is described in this post.

Note: This code only works for on-premises CRM.

Follow the steps below to create filtered lookups where the second lookup will be showing only records based upon the record selected in the first lookup. We will use the example of Countries and States/Provinces mentioned earlier.

1. In Customizations, create two entities to contain the values to be selected, they can be of the "Organization" type rather than the "User Owned" type (Country and State/Province).

2. For the Country entity you can just use the default "Name" field, or you could add another field for Abbreviation. Note: if you are going to use many countries (there are approximately 200 globally), than we suggest you also add a "Sequence" field so that you can sort the most commonly used counties to the top of the lookup view list.

3. You can set up the State/Province entity in the same manner.

4. In the State/Province entity, create a N:1 relationship to the Country entity, and name it Country and make it a required field since States/Provinces will be dependent on Countries .

5. Add the Country Lookup to the State/Province form. Then select Save and Publish.

6. After your entities and new forms are created, you can either manually enter the Countries, and then the States/Provinces, but you can also find Excel lists of all Countries (~200) and Sates/Provinces (~3500) on the internet.

7. To import the data from Excel lists, they must first be converted to .CSV or .XML files.

8. Remember to first import the Countries, and then the States/Provinces because in your import you will be tying the States/Provinces to their appropriate Country in the second import.

9. Once your entities are populated in CRM, you can prepare the lookups and filtering on the desired forms where they will be used.

10. Let's assume you will be replacing the OOB text Country and State/Province fields on the Account form. First, on the Account entity, create new lookup fields for Country and State/Province referencing the newly created entities.

11. Place the fields on the Account form.

12. In the Additional Properties of the State/Province Form, Set the Related Records filtering and default views as follows:

13. Select Save and Publish.

Suggestion: On the Account form, create a Business Rule that hides the State field if the Country field does not contain data and shows it if Country has data. This will force the users to enter Country before they consider State.

That's all there is to it! You are now ready to use Filtered Lookups.

Your results should look like this, upon selecting US as the Country:

This is how you can make one lookup field dependent (filtered) depending on another. Other considerations in the case of the example given:

If you are actually going to use Lookups or Option Sets for Countries and States, remember that CRM uses OOB text fields for both Countries (Address1: Country/Region) and States/Provinces (Address1: State/Province) for its mapping functionality and mapping to child Addresses form the Account records. This means that there may be additional work required to use these lookups throughout the system.

You can create a workflow that will update the OOB fields on the create form of the Account record and on change of the Lookup Country and State/Province fields. This will keep the OOB fields in sync with your new filtered lookups. Alternately, a small piece of JavaScript code applied the new fields could do the same thing.

Happy CRM'ing!