Microsoft Dynamics CRM 2011 - Filtered Option Sets or Filtered Lookups

A new out of the box feature in Microsoft Dynamics 2011 is filtered option sets also called as filtered lookups. This is a positive step forward as compared to days of Microsoft Dynamics CRM 4.0.

A filtered lookup or filtered option set is a lookup whose values are depended on an earlier selection.  This earlier selection can be in any other field on the form.

In Dynamics CRM 4.0 if I need a filtered lookup, the only way is to write some code in JavaScript. If my project had around hundred custom entities and say only 50% of them had filtered lookups, that's a lot of JavaScript. I can write a common JavaScript wrapper function and call it every time, but still, there is nothing out of the box.

Dynamics CRM 2011 has changed the way we create filtered lookups. Instead of JavaScript, I can simply change few settings for a lookup and have it configured as a filtered lookup. In this blog, I will configure a filtered lookup.

Below are the steps to create a filtered lookup in an entity whose values are based on a selection in another field.

1) I will use few entities to show filtered lookup configuration. I will use Movies, Genres and Sub Genres. Movies have N:1 relationship with genres and sub genres.

Entities

2) Below is the genre records created in my CRM 2011.

Movie Genre Records

3) Below are the sub genre records. As marked, each of these sub genre belongs to a genre.

Sub genre has a N:1 relationship with genre.

Movie Sub Genre Records Sorted by Genre Name

4) Below are the records for the Movies entity.

Movies Records

5) I have opened the movie record GoldenEye. This is one of my favourite James Bond movies. Movie has a genre and sub genre field on the form, as marked below.

Every movie falls under a genre and a sub genre. The genre selected is "Thriller".

Fields for Filtered Lookups

6) Every Sub genre falls under a genre. On the movie form whenever I open the Sub Genre lookup I can see all the items. The Sub Genres are not filtered based on the selected Genre "Thriller".

Sub Genre Lookup Without Filtering

7) I will configure a sub genre lookup field so that it filters based on the selected genre. Click "Customize Entity" under "Customize" tab to customise Movies. This will open the Movies form in design mode.

Customise Movies Entity

8) On the form below shown are the 2 required fields. Double click the "Movie Sub Genre" field to check its properties.

Movie Form

9) This will open the "Field Properties" window for the sub genre lookup field. Check out the marked area below which states "Related Records Filtering". This is the section where we configure a filtered lookup.

By default this section is disabled. Click on the check box to enable this section.

Field Properties for Sub Genre on the Movie Form

10) I will set the option "Only show records where:". Since I want to bind this field to Genre, I will select "Movie Genre (Movies)". This pull down selection shows all the options for Movies entity on which sub genre is dependent.

"Only show records where: " Setting

11) I will set the option "Contains". I selected "Movie Genre (Sub Genres)"

"Contains" Setting

12) Below are the settings which will configure the sub genre to a filtered lookup.

Filtered Lookup Settings

13) After these changes click "OK" on "Field Properties" window. Then click "Save".

Save the Movie Form Changes

14) I will now publish these changes.

Publish the Movie Form Changes

15) I will go back to the same Movies record "GoldenEye" and open the sub genre lookup. Now I can only see 2 sub genres as they have been filtered for the genre "Thriller". So we have a filtered sub genre lookup.

Sub Genres Filtered for the Genre Selection of "Thriller"

16) I will now select a different genre to test the filtering in sub genre lookup. I will select Genre as "Family".

Select Genre as "Family"

17) The sub genres have been filtered for the genre "Family". The values or options in sub genres are different than what we saw when the genre was "Thriller".

Sub Genres Filtered for the Genre Selection of "Family"

So we saw above how the sub genres are filtered on earlier genre selections. This filtering process was setup without writing a single line of code. It saves us from further code testing and maintenance.

One more feather in the cap of Microsoft Dynamics CRM 2011!

I hope this blog about 'Microsoft Dynamics CRM 2011 - Filtered Option Sets or Filtered Lookups' was informative. Please feel free to leave your comments.