Creating a Sub Lookup in Microsoft CRM 4.0

Quite often there is a requirement to have the values of one picklist or lookup be determined by the value of another picklist or lookup. As most of you will know Microsoft CRM does not have the concept of sub picklists or sub lookups. I’ve often seen this requirement solved through the use of picklists and JavaScript. Yuk! This solution means that every time a new value is added to either the parent or child picklist, the JavaScript code needs to be changed. Not a great solution for system administrators who have no knowledge of JavaScript.

Here is a simple solution using lookups rather than picklists with some simple, generic (and warning: unsupported) JavaScript.

For my example today lets presume I need to add a Primary Industry lookup to the Account form. I also need add a Sub Industry lookup where the values shown are determined by what I select as the Primary Industry. 

First I need to create two new entities, Primary Industry and Sub Industry. Next I need to create a 1:n relationship between the two new entities with Primary Industry as the Primary Entity. As you can see in the image below I have also set Primary Industry relationship attribute as Business Required so all Sub Industries must have a Primary Industry.

Next I add the Primary Industry attribute to the Sub Industry form.

In order to be able to filter the Sub Industry lookup based on the selected Primary Industry value I need to add the Primary Industry attribute as a find column in the Sub Industry Lookup View. I can now publish my two new entities.

I add some values to my Primary Industries and my Sub Industries.

Now I need to add the Primary and Sub Industry lookups to my Account form. I create  a n:1 relationship between the Account entity and my Primary Industry and Sub Industry entities. In each case the Account Entity will be the Primary Entity. I can then add the created Primary Industry and Sub Industry lookup attributes to the Account form as shown below.

Now I need to add the JavaScript to filter the value of the Sub Industry lookup based on what is selected as the Primary Industry. On the change event of the Primary Industry lookup I add the following JavaScript:

var lookupItem = new Array;

lookupItem = crmForm.all.new_primaryindustryid.DataValue;
var searchVal = lookupItem[0].name;
crmForm.all.new_subindustryid.additionalparams = ‘search=' + searchVal;

I can now publish all my changes and test the result. As you can see below because I selected Finance as the value for my Primary Industry, when I click on the lookup for the Sub Industry it is “pre-filtered” and only shows the Sub Industries that relate to Finance.