How to Set Up Pre-Filtered Lookup Fields in CRM 2011

8 Steps to Pre-Filter Lookups in Microsoft Dynamics CRM 2011

As I begin my fourth Microsoft Dynamics CRM 2011 implementation, I find myself using one of the lesser-known new features of this version of CRM over and over again. In all of the previous versions of Microsoft CRM, implementers have had a need to ‘pre-filter’ lookups and picklists, or build dependent picklists. Now, in CRM 2011, we are able to do just that! To utilize this heavily, it does require some planning to ensure that you are not creating too many unnecessary entities or tables in CRM, but when you do your planning, this feature will become very useful.

Let’s take a simple example – we have Categories and Sub-Categories for our Leads and Accounts. From a User-Interface perspective, we want the User to enter the Category, then click the lookup for Sub-Category and get some specific results in that list that are pre-filtered based on what was chosen in the Category field. Creating this pre-filtered lookup and maintaining the parent-child hierarchy or definition for the filtering is all out-of-the-box functionality now in CRM 2011. In the past, this type of functionality could be achieved, but would be many, many lines of javascript that was difficult to maintain or change, especially when you turn over the CRM implementation to the client. These pre-filtered lookups can also be several levels deep, whereas before that was very difficult to maintain via javascript.

Step 1:

Here’s how you set up these pre-filtered lookups for the Lead Entity. First, create the first new entity by going to Settings → Customization → Customizations → Customize the System. Now click New → Entity to create a new Entity in CRM 2011.

Next, refer to the image below for the settings to create the new entity (this is for my Category Entity example):

Create a new category entity in CRM 2011

Image 1 – Create new Category Entity
**Note: I always add custom entities to the Settings Area so that you easily view the entity records, and in this case, add Categories to CRM.
**Note: There is no need to make this entity anything more than a lookup table, so there is no need, most of the time, for these other options (mail merge, notes, activities, etc.). Please evaluate these options for your individual situation as there could be instances that these options are required, but not in my example.

Step 2:

Now Save the entity and we will add the relationships from this new Category Entity to the Lead entity. On the left-hand side of the new Entity definition screen, click on 1:N Relationships. Then click “New 1-to-Many Relationship,” which is where we will add the new relationship to the Lead. Fill out the form as in the image below.

Create 1:N Relationship between category & lead

Image 2 – Create new 1:N Relationship between Category and Lead

A couple of things I want to point out:

  • For the relationship definition ‘Name’, by default, the relationship name is new_new_category_lead, and I always remove the duplicate ‘new_’ from the name so that it becomes new_category_lead.
  • For the lookup field ‘Name’, by default, it concatenates “id” to the end of the name, and I always leave this so that if I am writing reports or integrations, I will know that the field is a lookup field.
  • This type of relationship should be referential as a category is not truly a ‘parent’ to a lead, and I think of it in these terms, if the primary entity (in this case category) is deleted, should all related entity records (in this case leads) be deleted as well (one cannot exist without the other)? In this case, that is not true, so referential is the correct relationship behavior.

Step 3:

Next, Save and Close the Category Entity in CRM 2011 and navigate to the Lead Form within the Default Solution by going to Components → Entities → Leads → Forms → then double-click the ‘Main’ Form. Now drag the new ‘Category’ field onto the Form where you would like for it to appear. Save and Publish the Lead Form. Now you have created the first lookup field which will determine the values that appear in the second lookup field.

Step 4:

The next step is to create the second custom entity, Sub-Category. Repeat steps 1 through 3 above, just replacing Sub-Category for Category. Once you have added the Sub-Category field to the Lead form and published it, we are ready to configure the pre-filtering.

Step 5:

Now we will add a Category lookup to the Sub-Category form. This is how we will define what Sub-Categories appear for which Categories. Within the Default Solution, navigate back to the new Sub-Category Entity (Components → Entities → Sub-Category → N:1 Relationships) and click “New Many-to-1 Relationship.” Now we will tie Categories to Sub-Categories in order to filter. Fill out the form as in the image below.

Create new N:1 Relationship sub-category & category

Image 3 – Create new N:1 Relationship between Sub-Category and Category

Step 6:

Now go to the Sub-Category’s Main Form and add the new, required Category field to the form, as shown in the image below. Save and publish the changes.

Sub-category form customizations in MS CRM 2011

Image 4 – Sub-Category Form Customizations

Step 7:

The last step in the configuration process is to actually add the pre-filtering to the sub-category field on the Lead Form. This is where we will tell CRM 2011 to pre-filter the sub-category field based on the category chosen on the Lead. Navigate to the Lead entity Form (Components → Entities → Lead → Forms) and open the ‘Main’ form. Once the Form is open, double-click on the Sub-Category field where we will set its properties. Configure the Related Records Filtering options as in the image below.

Setting sub-category pre-filtering on lead form

Image 5 – Setting Sub-Category Field Pre-Filtering Properties on Lead Form
**Note: you can choose whether you would like to restrict the user to choosing only Category and Sub-Category combinations that you have defined (leave ‘Allow users to turn off filter’ unchecked) or you can allow Users to choose any combination and leave the pre-filtering just as a default, but not a hard requirement (in that case, you would check the ‘Allow users to turn off filter’ box).
**Note: For now, leave the Additional Properties as-is. I will touch on this in another post.

Step 8:

Now click OK and Save and Publish the Lead form. The last step is to go into Microsoft Dynamics CRM 2011 and add the Categories and Sub-Categories to CRM. I typically would add all top-level Categories to CRM 2011 first (for the new entities to show up in Settings, you will need to refresh CRM (push F5), then navigate to Settings → Extensions → Categories. Add all of the new Categories necessary. When you are finished, go to Settings → Extensions → Sub-Categories and add all of your Sub-Categories (remember for each Sub-Category, you must also enter a Category – this is how we define the pre-filtering!). When you have finished adding your data to CRM, open up a Lead and test out the pre-filtering to ensure you are getting the expected results! Now if you wanted to add this same functionality to the Account or any other entity in CRM 2011, just repeat these steps to create the relationships with other entities!

Click here to learn more about Microsoft Dynamics CRM 2011.

Next Post