Microsoft Dynamics CRM: How To Do Dependent Lookups

http://crmadminassist.com/microsoft-dynamics-crm-dependent-lookups/

You might refer to them as cascading or dynamic or dependent or . . . whatever you call them, the ability to filter the data available to the users in one field based on the value chosen in a previous field is pretty handy.  You may have seen postings about this type of functionality as it relates to option sets.  To make the values available for selection in one option set dependent upon the value selected in a ‘prior’ option set requires jscript.  For those of us who aren’t coders but still need that functionality, if we can work with lookups instead of option sets (and are using CRM 2011 or newer), dependent lookups can be helpful.  By the way, a shout out goes to Leon Tribe for his post on this subject and for inspiring me to give it a try.

As an example

Suppose you have a need to classify your Opportunities (or Accounts or Leads) as to the Market Segment they fall under as well as a further ‘Market Sub-Segment’ classification.

To make it easier on the users and to gather correct information, you want the values available in the Market Sub-Segment to be filtered so only the Sub-Segments associated with the Market Segment chosen are visible.

Let’s see how easily this can be done . . .

Create the entities

I my example, I didn’t need much information in either entity as they are strictly for lookup.  Both were created with ‘Organization’ ownership.

Market Segment fields:

• Name (Primary field – for description of the Market Segment)
• Code (the code from the ERP system)

Market Sub-Segment fields:

• Name (Primary field – for description of the Market Sub-Segment)
• Code (the code from the ERP system)

However, if we stop there, there is no relationship between the two entities and we’ll not be able to restrict the values in the Market Sub-Segment lookup on a form like we want to.

Did you say ‘relationship’?

We need to create a 1:N relationship from the Market Segment to the Market Sub-Segment entity and set the behavior to ‘Referential’ (partial 2015 form shown, yours may be slightly different):

I added the new Market Segment lookup field to the Market Sub-Segment form and published both forms (after I’d set up your two forms as I wanted them to be):

For each entity with a form that will display the Market Segment and Market Sub-Segment lookups, I also needed to add 1:N relationships from Market Segment and from Market Sub-Segment to the related entities (Opportunities, Accounts, Leads, etc.)  Naturally I added the 2 lookup fields to the appropriate form(s), selected the default view and view selector settings and published my changes.  We’ll configure the filtered lookup shortly . . . stay tuned.

Time for Data

Depending on the amount of data, you can decide how you wish to populate your ‘Market Segment’ and ‘Market Sub-Segment’ data.  Whatever method, you need to populate the data for the first lookup first.  You need those records to exist in order to associate each dependent (Market Sub-Segment) record with one ‘Market Segment’ record.

While the data from the ERP system only consists of a Code and a Description for the Sub-Segment, I also need to link the Sub-Segment record to its appropriate Market Segment record:

At this point I have Market Segment and Sub-Segment data in CRM and the records are related but the CRM form doesn’t yet know how to take advantage of the relationship.

Filtered Lookups to the rescue!

I now modify the form(s) that contain the Market Segment and Sub-Segment lookups.  I open up the form and open up the Field Properties for the lookup field that represents the dependent lookup – in my case, that is the Market Sub-Segment.  In the ‘Related Records Filtering’ section, I select the checkbox for ‘Only show records where’.  Then I choose the entity that represents the first lookup (Market Segment in my case) and then finally I choose the dependent entity in the ‘Contains’ area (Market Sub-Segment in my case):

Now on my Opportunity form, whenever I have selected ‘Distributor’ as a Market Segment, in the Market Sub-Segment lookup I only see the 3 appropriate values:

Sounds good, but . . .

There are a few things to consider:

• If you change the first lookup value, the value in the dependent lookup is now incorrect; you need to do a lookup in the dependent field to get the new ‘acceptable’ values to select from (and then select one!)
• If you enter partial data in the lookup and use autocomplete, CRM will bring back values that match your autocomplete criteria and will not filter the records as you want them to be filtered – users need to use the lookup icon and not try an autocomplete
• This can be expanded to more levels than just 2; you just need to continue adding a link from the entity ‘above’ to the entity ‘below’ so CRM knows how to filter the lookups

I hope this helps in at least a few situations!