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.
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):
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.
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.
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.
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.
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.
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.
Image 3 – Create new N:1 Relationship between Sub-Category and Category
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.
Image 4 – Sub-Category Form Customizations
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.
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.
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.