CRM 2011 Filtered Lookups Explained

Article
1/17/2024

Microsoft Dynamics CRM 2011 has a new concept called Filtered Lookups. Knowing how to use filtered lookups properly helps the CRM System Administrator create an intuitive, easy to use CRM system that is extremely flexible, and allows you to implement a fully relational database model in your CRM system, which will greatly save on on-going system administration costs.

Relationship Primer

CRM 2011 allows you to create as many custom tables (they call them entities) as you want. You can create relationships between tables. There are three types of relationships: one-to-many, the same thing looking from the other side, many-to-one, and many-to-many.

As CRM does not allow you to customize many-to-many relationships, and because you can re-create the same functionality AND customize it with two one-to-many relationships, my advice is to never use the many-to-many relationship feature in CRM.

The one-to-many relationship is the only relationship that matters in CRM. The inverse, many-to-one, is the exact same relationship, just looked at from the other side.

For example, a person can have many addresses. In CRM you can create a one-to-many (1:N) relationship from person to address. Or, you could create a many-to-one (N:1) relationship from address to person. It’s the same thing. The only difference is which entity you are editing when you create the relationship.

What happens when you create a relationship in CRM is the following:

1) A “left nav bar” link to the “many” side is created on the form that is on the “one” side of the relationship.

On the Person entity will be a link under the Related section on the “left nav bar” labled “Addresses”.
When you click this link you can see all of this person’s addresses.

2) A lookup field is created on the form that is on the “many” side of the relationship.

On the Addresses entity will be a lookup that has the Person’s name in it.

And that’s it. But the power this gives you is amazing, when combined with filtered lookups in CRM 2011.

Filtered Lookups offer advantages over option sets

A lookup field is a fantastic control. It offers many distinct benefits over the next most valuable control: option sets. (OK, maybe dates are in there somewhere, too. But lookups are awesome!)

Certainly, when you create a relationship between two data tables (account and contact), you will use a lookup field. But I’m suggesting that you should use lookups in many cases where you might otherwise have used an option set.

Reference data is often entered as an option set (select from these 12 Contact Types). But, in a fully relational system (where 1 person has 1 contact type, but many people share the same contact type), this reference data is more purely implemented as a lookup. In other words, a custom field, linked to another (Contact Types) reference table.

Here are the advantages I have found to using lookups instead of option sets:

*Feature*	*Implemented Using Lookups*	*Implemented Using Option Sets*
Ease of use for the end user	Type a letter into a lookup field, the system displays recently selected values with that first letter. If the one you want is there, you can select it (with the mouse or down arrow, e.g.). If not, continue entering letters to narrow the search, or Hit tab, and it will either find the one that matches what you’ve entered, or will show you a list that matches what you’ve entered, and you can select the desired value with mouse or keyboard.	Users can type the first letter of the option The longer the list, the more time it takes the user to select from it. If you have more than 5 values in your option set, I strongly suggest you look at turning it into a lookup, for ease of user for the end user only.
End User control over values (distribute Admin functions to the end users)	You can give the ability to edit, or create new, lookup values, just like you can set permissions to any other table. If you want a key user to maintain the possible Product Version selections for a case, use a lookup, create the new table, and give that key user the ability to create new records on that table.	None. The end user must select one of the options made available by the Sys Admin. The System Admin must change the allowable option set values, save, and publish. (In a controlled setting, you would also have to propagate a new solution from Dev to Test to Prod.)
Quick ‘Reports’, or Views into your data	If you give users access to view the “Reference” table (the one the lookup refers to), users can gain insights about this relationship instantly. For example, if you have a Product lookup on the Opportunity form, and one of your Product Names is “CRM”, you can go to the Product entity, select CRM, and select “Opportunities” on the left nav bar, and see all of the Opportunities where “CRM” was selected as the product.	Can Filter on Option Sets in a grid to give you a list view of this data in place. (You could also filter on the lookup field in a grid, so that’s not really an advantage for option sets.)
Ability to retire values, or change valid values, over time	A huge advantage. If a value should no longer be selectable, simply deactivate the record. (To make “2010” no longer a valid year to select, deactivate the “2010” record.) The key is, CRM will display the values of deactivated lookups in those old records.	None. If you want a value to appear in the list for an old record, it has to appear in the list today. If you eliminate it from the list, it won’t show up in the old records. (Best answer: move the selection to the bottom of the list, and train the users not to select them… ugh..)
Filtered Lookups	Fantastic… read on…	Requires a lot of Javascript code to even come close.

Filtered Lookups Explained

The idea behind a filtered lookup is simple. An unfiltered lookup typically allows the user to search all records in the related entity. (The Account lookup on the Contact form will, without being filtered, show the user all Account Names to choose from). A filtered lookup shrinks that list, to (ideally) a shorter, more applicable list.

There are actually two types of filtered lookups implemented in CRM 2011: (a) a filtered list, and (b) what are commonly called hierarchical lists. I’ll explain how easy it is to create both of these lists, and what they are used for, in turn.

Filtered List

For example, let’s say you want to attach a Sales Exec and a Sales Manager, people who work for your company, onto each Opportunity. Easy enough, you just create two N:1 relationships between the Opportunity and Users entities.

But soon the users complain that they don’t want to have to look through all Sales Execs to select a Sales Manager. Before CRM 2011 Filtered Lookups, you would have to write some tricky, perhaps unsupportable, code to find just the Sales Execs, and make the lookup show only that data.

In CRM 2011, doing this takes a few minutes, and requires no code.

1) Figure out some way of distinguishing Execs from Managers.

One solution is to add two new custom checkbox fields to the Users table: one for Sales Exec, one for Sales Mgr (if some can be both, and at some point, they will), or
an option set (Job Title) with one value for each.

2) Update User records to check each of the boxes (you can use a bulk edit on a group of users to set them to Sales Execs, then do another bulk edit on a different set of users to check the Sales Mgr checkbox.

3) Create custom views on the User entity for each different type of user (Sales Exec, Sales Mgr views), and publish the Users entity (publish all customizations)

4) Customize the opportunity form.

If you haven’t already, create N:1 lookups to the users table, one for Sales Execs, one for Sales Mgrs.
Double-click the Sales Mgr lookup field on the form and scroll down to the bottom of the form to the “Additional Properties” section.
If you want the user to have a search box show up, so they can search within the filtered view, check the “Display Search Box in lookup dialog” box. (I’d only do this is the filtered view returns dozens of records, so I typically un-select this box.)
In the Default View field, select, in this example, the Sales Exec view. (Select the view you created in step 2 that showed only those users.) This will be the default view that shows up in the standard CRM Lookup form, and the sales exec will be displayed. So, typically, the end user just has to select from a much smaller list.
In the View Selector field, select one of the following:
1. Off. The user can’t select any other users than the ones in the Sales Exec view.
2. Show All Views. The user can change the view to any other user view.
3. Show Selected Views. The user can change the view to ones you select.
You can do the same for Sales Execs, just select the Sales Exec default view instead of the Sales Mgr default view in step (d) above.

5) Do the usual save and publish, and test out your form.

Open the Opportunity record, and Click on the Sales Exec lookup field, and only those Users you marked with a check in the Sales Exec checkbox field (or who have Sales Exec selected as their Job Title in an option set) will appear. A different list of users (the Sales Mgr checked ones) will appear in the Sales Mgr lookup field.

When you use filtered views in this way, what the user typed is validated against only the records that meet the filter criteria. So if we had a Dave Sales Exec and a Dave Sales Mgr, without filtered lookups, the user would have to choose among Daves. If the Sales Exec filter is implemented as shown above, the user can just type Dave, and the one Dave Sales Exec will be shown to the user for quick selection (and, if unique, will be selected automatically as you tab away from the field.)

This helps users avoid mistakes, which improves data quality, and makes it much simple for users to select from a targeted list of valid choices.

Implement ONE Reference Table using filtered views

Another use of this type of filtered view is to use a “Reference Table” approach. Often, if you properly design your relational database, you end up with dozens (and in a big system, hundreds) of “name only” entities. These entities just use the name field to display the various choices. (Valid Product Type, or Contact Type, or Account Type.) Now, this is an excellent technique, because you gain all of the benefits of lookups, as opposed to using option sets, as described above. But if you have a hundred of these tables, maintaining the ones that matter gets increasingly difficult.

In some systems a typical approach, to limit the proliferation of these type of tables, has been to instead create ONE reference table for all of these “name only” entities. Until now, you couldn’t do that in CRM. But now, you can.

Create a Reference Table
1. Use the Name field for the Reference Value
2. Add a Category (or Type) (option set) field to the table. (Or, create a Category table first, and link to this Reference table as a lookup field…). Categories must be unique. (Basically, we are creating "name/value pairs" in this new custom reference table.)
3. Create a view that displays all of the records in a given category.
  1. View Filter Criteria: Type = “State”, should show all 50 US states.
  2. For example, the Category is “State”, and one of the 50 is “WA”.
  3. On a form, create a N:1 relationship to the Reference table.
    1. Edit the Lookup field that creates, and make it a filtered lookup by selecting the View you created in step 2 above in the “Additional Properties” section.

The result will be that you will only see the 50 states. (Not impressive until you add more entries to this reference table with other categories, this will help you reduce the clutter if you have many "one field" reference tables.)

I have found one caveat that may cause you to decide NOT to use this approach: when you are importing data, you can only do a lookup to another entity, you cannot do a filtered lookup. So if you added the 50 US State Codes and you added Countries to this table, when you are importing a contact from the state of "CA", that record may not import, because the country "CA" (Canada) and the state "CA" (California) would both match the contact State, and therefore the record would not be imported because the "lookup could not be resolved".

Hierarchical Lists

Hierarchical lists are also simple in principle: the value the user selects in one field affects the valid values for another field.

For example, if I have a Country lookup field, and select US, I would expect the State/Region lookup field to only show me US states. IF I select Canada, I should see only provinces.

In the past, this could be solved a number of ways, all involving coding. In CRM 2011, filtered lookups solve this without coding.

Create a Country table, with just one field, the Country Name field.
1. Publish all customizations.
Create a State/Region table with the Name field set to the States.
1. Add a 1:N relationship between Country and State.
2. This will create a Country lookup field that you can add to the State/Region form.
3. Save the form, and publish all customizations.
Open the Country table, and create one record that has USA in the Name field, and one record that has Canada.
Open the State/Region table, and create a few US State records (where the Country lookup has USA selected), and a few Canadian provinces (Country lookup = Canada on these records).
On your Contact entity:
1. Create an N:1 relationship to the Country table.
2. Create an N:1 relationship to the State table.
3. Open the Contact form.
4. Place the State field onto the form.
5. Double-click to edit the State field properties.
Click the “Only show records where” box.
1. Normally, or if you didn’t do all of the above steps correctly, you would only see boring Created By, Modified By type fields, and most people skip other this feature when they see that….
2. Now, you should see something like “Country (Contact)”. Select that.
3. The Contains box will now give you one choice, “Country (Country). You’re in good shape.
4. Add the Country lookup field to the form.

7. Save the form, and publish all customizations.

8. Create a New Contact.

Select a value in the Country field.
Click on the State/Region lookup.

If you picked US, you should see only US States, if you picked Canada, you should see only provinces.

There are a number of areas where you can apply these techniques to reduce coding, make it easier for the user, reduce mistakes, and in general increase overall customer satisfaction.

Note that the key to having this work is to have 3 entities that have the following relationships:

Country has many States, implemented in a ref table

Same Country can exist on many Address records, an Address only has one Country

Same State can exist on many Address records, an Address only has one State

For example, the State reference table entity has a field called Country that is a Lookup to the Country refence table. (Manitoba’s country is Canada; Montana’s is US). The Contact entity has Country as a lookup, and State as another lookup field.

So the system is smart enough to filter the State lookup to show only those State table records whose Country field has the same value as the Country field on the currently open Contact. (Only show US States if the Country = “US”.)

Pretty amazing, given the flexibility you have to create any tables, and any relationships and lookups you need. Using filtered lookups, you can now relate reference tables to data tables in powerful ways, restricting information to only that which is valid, by your definition.

Now, there are some areas where you may not want to use a lookup, for example, State Codes are most commonly entered as an option set, as those 50 values have not changed very often. However, if you do have addresses for different countries, even in this case, you might want to use a filtered lookup to select the valid states (provinces, territories) for the selected country.

Conclusion

In short, when you are starting to build a lengthy option set, try creating a lookup field instead, and learn how to filter them two different ways. You gain flexibility, ease of use, and reduce the amount of custom coding required. Your users will thank you, as will those who come after you to maintain your CRM system.

Share via