How to Create Dynamics CRM/CE Dependent Lookups

The entirety of my software consulting career has been on the functional side of AX/F&O. With some recent down time, I have been learning CRM/CE. As I quickly learned, a functional consultant in CE can perform a good portion of the job responsibilities that a developer would typically do in F&O. With that in mind, I want to share what I’ve learned in “reaching across the aisle.” Whether you’re a fellow F&O consultant learning CE or a new CE consultant, I hope you find this helpful!

With data in general, there’s a common request where if I select a value in Field 1, then the available values in subsequent Field 2 should be pre-filtered. In F&O, this functionality and concept exists in a few places (sales order return reason code groups and return reason codes, for example), and a CE functional consultant can create these dependent lookups all on their own! Let’s see how using the Lead functionality and declaring a Lead Source and Lead Source Sub-Type.

How to Create Dynamics CE Dependent Lookups

First, create an entity (in F&O terms, a table) for the Lead Source and Lead Source Sub-Type. I had created custom fields for a separate entity before this, so my brain immediately jumped to “Add the Lead Source field to the Lead Source Sub-Type form, then add both fields to the Lead form. Make the Lead Source and Lead Source Sub-Type fields an Option Set and be on my way.” That quickly got me nowhere.

There’s a key conceptual difference between F&O and CE here. In F&O, a lot of the dropdown fields on the Customer record, as an example, are supplementary tables. We work with the client to explain their utility and get their list of values. A minority of the dropdown fields are enum fields where the values are pre-defined by Microsoft and rarely, if ever, changed.

In CE, a user can create as many Option Set fields as they want, and this is akin to an F&O enum field. The difference in CE is the direct access to the field to define what the values should be. If you need the dependent lookup functionality, you’ll have to go with a separate entity/supplementary table. Below is a table that can be used as general operating parameters of what scenarios call for which approach.

To create the values for both Lead Source and Lead Source Sub-Type, execute an advanced find on each entity. Your results will be blank, and this is the point to enter the different records for each entity (in F&O terms, we’re populating the supplementary tables). When entering the sub-type records, make a selection on the source type dropdown. This is the tie that binds and will only display sub-type records that belong to the previously selected source record.

Next, add the Lead Source and Lead Source Sub-Type fields to the Lead form. For each field, set the Data Type = Lookup and the Target Record Type to the respective entity. This is how CE will know the values to display. To enforce the dependent lookup relationship, open the Field properties of the sub- type field and set the Related records filtering as such.

Creating a Business Rule for the Dependent Lookups

Technically, you are done; congratulations! However, a user could accidentally make a wrong selection, if they select a sub-type first. They could select Lead Source Sub-Type = 2A, then select Lead Source = 1, and CE will allow it. Luckily, we can leverage business rules to prevent this from happening!

The business rule canvas in CE has the same look and feel as the workflow canvas in F&O, but is much more powerful. We need to create a condition that checks to see if the Lead Source = blank. If true, we want to lock the Lead Source Sub-Type field and set the value to blank. If false, we want to unlock the Lead Source Sub-Type field, so the user can make a selection. The final business rule setup is below.

Have any other questions about CE? Feel free to contact us at any time!