How To Filter Lookup Field Based On Another Lookup In Dynamics 365 CRM

In Dynamics 365 CRM having filtered lookups can greatly enhance the user experience by just simplifying the input of data entry. It ensures that users are presented with only related options based on their previous selected option also it improves accuracy and reduces chances of selecting wrong options.

In this blog, we will explore how to filter lookups fields based on another lookup. Specifically, we will use an example where you have three entities: Company, Employee and Employee Assignment. The goal is to ensure that when a company is selected in Employee Assignment form, only employee related to that company are available for section.

WHY USE FILTERED LOOKUPS?

Filtered lookups make data entry effective, minimize errors of selection, and enhance the user experience. For example,

• Increased Accuracy: By filtering the records based on related data, user is less likely to select irrelevant or incorrect options.
• Save Time: It speeds up the data entry process by showing users only the information they needed.

Now, let’s get started I will show you step by step process of setting up filtered lookup in Dynamics 365 CRM Services.

STEP 1: SET UP YOUR ENTITIES  

First, verify you have the following:  

• Company Entity: A custom entity that stores details about companies. 
• Employee Entity: A custom entity that stores details about employee, including a lookup field to the Company entity.  
• Employee Assignment: A custom entity that stores details of employee Assignment, including a lookup of fields Employee Entity and Company Entity.

In our example, the relationship between these three entities is important. The Employee entity should have a lookup field pointing to the Company entity and Employee Assignment have a two lookup fields pointing to the Company and Employee entity.

As we can see in below images:

STEP 2: USE OUT OF BOX FUNCTIONALITY TO FILTER LOOKUP

Dynamics 365 CRM provides built in functionalities to filter lookups fields, which can be configured without writing custom code. Here you can achieve the desired behavior:

1. Go to Employee Assignment Form:

• In the form editor, open the Employee Assignment form where you want the lookup to be filtered.
• Select Employee field and check on Filter by related rows in Filtering section.
• In Relationship to current table select Company (Employee Assignments).
• In Relationship to lookup’s table select Employee (Company).
• Additionally in Display options Section check Disable most recently used items.

STEP 3: TESTING THE FILTERED LOOKUP

Now that the form is set up, it is time to test the filtered lookup functionality:

Let’s, create some sample data in Companies and Employees entities.

Companies

Employees

Before

As you can see in below screen shot before the filtering functionality was applied, all the employee records in the system are visible in the employee lookup.

After

As you can see in below screen shot after filtering functionality is applied, only employee records associated with the selected company in the Company field are visible in the employee lookup.

STEP 4: TROUBLESHOOTING AND BEST PRACTICES

If the filtering doesn’t work as expected, check the following:

• Entity Relationships: Ensure that the relationship between the Company, Employee are properly defined.
• Lookup Setting: confirm that the filter is configured to use the correct entity and field.
• Publish the Form: Ensure you have saved and published the form after making changes.

PRO TIP: JAVASCRIPT FOR COMPLEX FILTERING CONDITION

While the out of box filtering lookup functionality is sufficient for the basic scenarios, but for more complex filtering can be achieved by using JavaScript.

For Example:

You may want to filter employees based on both the selected company and include only active employees. Then you can use JavaScript as follows

Attach this script OnChange event of Company Lookup field.

function filterEmployeeLookup(executionContext) {
  var formContext = executionContext.getFormContext();
  var companyId = formContext.getAttribute("companyid").getValue();

  if (companyId != null) {
    var employeeLookup = formContext.getControl("employeeid");
    employeeLookup.addCustomFilter(
      "<filter type='and'><condition attribute='companyid' operator='eq' value='" +
        companyId[0].id +
        "' />" +
        "<condition attribute='statuscode' operator='eq' value='1' /></filter>"
    ); // 1 = Active
  }
}