CRM 4 – Implementing Dependent Lookups using JScript

Often times you want to restrict the values of a given list based on some selected value of another list. For instance, consider the two lists, Countries and States, where based on the Country selected you only want to show the States of that particular country. There is a nice sample (dependentpicklist) in the CRM 4 SDK demonstrating how you can implement such picklists. In fact, by using the CRM Demonstration Tools you can easily define the dependencies and generate the required Jscript code. However, if we are dealing with Lookup fields instead of Picklists, then having a similar functionality is not that straightforward.

Although CRM 4 doesn’t have out of the box capability to define such lookup fields, there are number of ways you can achieve this including 3rd party CRM modifications such as Stunnware Filtered Lookup. How ever in this post we will see a really simple way to achieve this by using only JScript and the built-in search feature of the lookup view.

The Demo

For the sake of this demonstration, lets consider a custom entity called Expense Claim use to record an organization’s expense claims. Assume that this entity has two dependent entities, Expense Category and Expense Type. Suppose each expense category is consists with one or more expense types.

Figure 1: Relationships between entities

The following figures shows how its looks in CRM 4 after populating some data to the entities. Note how we have added the expense types under each expense category.


Figure 2: Expense Categories


Figure 3: Expense Types with Categories

Now consider the following figure where we have the form of the Expense Claim record and the above two entities as lookup fields.


Figure 4: Expense Claim form with category and type

The expected outcome of this form is that once the expense category has been selected, we should only show the related Expense types in the expense type lookup. Lets see how we can achieve that.

Expand the Search Field List

First you need to include the expense category as a search filed of the expense type lookup view. This will enable us to search and filter expense types by expense categories. For this we need to  customize the Expense Type Lookup View and add Expense Category as a Find Column.


Figure 5: Customize Expense Type Lookup View

Once we have the category as a find column in type lookup view, we can filter the types for a particular category simply by using the “search” parameter of the lookup view. In essence, what we have to do is, when ever we launch the expense type lookup, set the search criteria for that view using the search parameter. We will achieve this by including some jscript code in the load event of our main form (Expense Claim form).


Figure 6: Customize the Expense Claim form to trigger custom jscript

We will include the following JScript code inside the OnLoad event of the form.


Figure 7: Jscript code to filter the lookup data

In the above code we first define a function (FilterLookup) where the source is typically the primary lookup (Expense Category) and the target is the dependent lookup (Expense Type). We will simply set the search criteria of the dependent lookup view (Expense Type Lookup View) to the name of the expense category. You will shortly see the advantage of having this functionality defined as a function. After the function definition, we are calling the function with values of expense category (crmForm.all.ecm_expensecategoryid) and expense type (crmForm.all.ecm_expensetypeid) so that the filtering applies on the very first load of the form.

Now save all customizations and check the results! The following figure shows the filtered expense type lookup view based on the selected expense category. In effect, what has happened was we have automatically put the name of the category in the search box and since we have defined category as a “find field”, the types have been filtered accordingly.


Figure 8: The filtered view of the Expense Type Lookup View

So far so great, but we are not quite done yet! Note that right now if you go and change the category again, you will still see the previous filtering on the expense type lookup view. To fix that we need to set the filtering criteria on each time we change the value of the primary field which in this case the expense category. Since we already defined the filtering logic as a function, doing this is super easy. We just need to call that function at on change event of the Expense category field. The following figure shows how we can do that.


Figure 9: Customize Expense claim form to include functionality on change event of category




Figure 10: Modify onChange event of the Expense Category

Here we will simply set the search criteria again and in addition clear the existing value of the expense type so that the user can select a new value based on new category. Note how we are calling the FilterLookup function we defined at the onLoad event of the main form.

Now you can save all customizations and see our dependent lookups in action!

Further improvements

You may further improve the functionality of this setup in multiple ways. For instance you may completely disable the field boxes and allow selection only by using lookup view. Also you may hide or disable the search box in the expense type lookup view.

Please note that this solution will NOT validate (or enforce) users entering un-related expense types. For that we may need to implement a custom plugin or an advanced implementation which involves client-side service calls. How ever, given the simplicity of this solution, I think this will cater to most of this kind of requirements.