CRM 2011 Filtered Lookups - Allowing You Build More Flexible Data Structures

Posted by Optimation Editor on 30 March 2011

A common issue encountered with CRM systems is that while modern CRM systems can be very flexible, and allow easy construction of new entities for storing data, the facilities they provide for accessing this customised data structures tend to be limited in subtle ways that may not be immediately apparent, but can have negative impacts on the system over time.

What tends to happen is common entities that could easily be grouped into a single structure (say a contact), can end up being separated into customised individual entities for each type/category of that entity. For example you see separate custom entities for client, partner, supplier etc.

The disadvantage of this that you now have multiple entities to maintain and any changes (e.g. additional fields) need to be rolled out to each of these entities. A better structure is to try and consolidate where practical into a single abstracted

entity and simply classify and categorise as necessary. And where major differences in the type of data being collected exists you can create separate extension entities, but still maintain you single repository of core common data.
A simple principle, but with some of the subtle forces at play within many CRM/xRM system, something that is not always easy to apply.

It was interesting to observe that with Microsoft's new CRM 2011, they've introduced a several new facilities that while simple in nature, go a long way to making it easy to avoid the above mentioned pitfall.
One of these is the concept of the filtered lookup. This allows links between different entities to be defined, and when you "lookup" a record on the related entity you can have that lookup predefined to show only results matching a certain criteria.

A simple of example of this might be a health care provider storing all contacts in a single "contacts" entity, but categorising them into contact types such as client, doctor, staff, and facility manager.

On the client application form they may want to record the client, their doctor, and perhaps the manager of the facility they're assigned to. So on the application form we can create three separate filtered lookup fields, which all reference back to the single contact entity, and each of these lookups will use a predefined filter to show only the relevant type of contact. Thus the user will only see "clients" when they use the client lookup field, and will only see doctors when they use the doctors lookup field.

Sounds fairly obvious right? Well it is, and there were ways of achieving this in previous CRM versions, however it generally involved writing large amounts of reasonably complex code to hand build this sort of facility.

This additional complexity in trying to achieve a more flexible data model often lead to the CRM system being setup differently whereby each of these contact types (e.g. client, doctor etc) would be setup as separate entities in the system.

While this would work, it meant the CRM system quickly becomes difficult to manage, with large amounts of duplicated structures and reports. For example if you ever wanted to start capturing additional contact information, such as an additional phone number, you would have to go in and edit each of the individual entities and reports separately.

With the new features such as the filtered lookups in CRM 2011 making it much easier to setup a more manageable and flexible data structures (e.g. a single contact entity for all types of contacts), the number and complexity of reports is simplified, and you only have a single contact entity to edit whenever changes are required (and they inevitably are). This allows the business analysts and system designers to build the correct structures to support a much more flexible and easier to maintain system.

There's a lot of value to be gained from seemingly small CRM 2011 features such as this, by making it is easier to design and build simpler and more flexible data structures, the business gains a CRM/xRM system that over time can much more easily cope with the growing and changing demands of the organisation.