Filtering Lookups based on Option set (picklist) field in Dynamics CRM

Introduction

Dynamics CRM provides an interactive environment to control values that a user can use in any of the fields for consistent reporting and searching. There are certain types to it, that include: Lookup, Option Set and MultiSelect.

  • Lookup” that allows users to quickly select a record from the existing data (records from related entities having 1 to many relationship (1: N)), inside the CRM.
  • “Option set” (picklist) allows users to choose a single option from number of options in a list. These types of fields not only help users to input data in the forms more accurately and efficiently, they also help technical developers spend less time on customizations to setup such fields.
  • “MultiSelect” Option Set (users can select multiple options instead of single select), Single Line of Text, Two Options, Multiple Lines of Text etc.

By default, lookup control shows only 10 results and can be used to show filtered results based on another lookup field, this is called relative filtering. Relative filtering is Out of the Box (OOB) feature in Dynamics CRM and can be used to filter out values based on the relationship (can be configured in the lookup field’s settings on a form). But, sometimes the requirement changes from relative filtering to filtering the results, based on option set field value which cannot be covered by any OOB feature and requires JavaScript custom code. However, filtering lookup can be performed based on any field type depending upon the requirement.

Learn more about our Dynamics 365 offerings 

Sample Scenario

A lookup field ‘Best Player’ shows name of the players from different sports i.e. Cricket, Basketball, Soccer, played in a country. The Option set field ‘Sports Type’ contains three values i.e. Cricket, Basketball and Soccer. The name of the players must change to their relative sports depending on the option set value. The user must not see name of all the players from Sports type, if Soccer is the chosen option.

Below is an example of a bad user experience which shows all the players even though Soccer is selected in Sports Type field.

example of a bad user experience

To change the following lookup field to show the relevant values, we need to incorporate the custom code mentioned below:

Schema

Entity: Player (new_player)

Fields: Name (name ), Player Sports (new_playersports), Team (new_playerteam) , Jersey No. (new_jerseyno) , Years Active (new_playeryearsactive) ….

Entity: Match (new_match)

Fields: Sports Type (new_sportstype), Best Player (new_bestplayer), Team A (new_team1) , Team B (new_team2), Winner Team (new_winnerteam) …..

The Code

We use JavaScript to filter the data based on the field type.

Firstly, we need to create a function which will be called onChange of the option set field ‘Sports Type’. This is because we need to filter records based on the value in this field ‘(new_sportstype)’. The addPreSearch method called on ‘Best Player (new_ bestplayer)’ field calls a function filterlookup which contains actual logic to filter the records. Another function removePreSearch calls the same function for removing the filtering logic used in the filterlookup function.

filterlookup function

The filterlookup function needs a FetchXML condition, that will filter the lookup and will be applied on ‘Best Player’ field using addCustomFilter function. The FetchXML should be built and tested on the same entity (Player in this case), so the field name remains valid.

Learn more about our Dynamics 365 offerings 
FetchXML condition

Final Thoughts

As the option set changes, the lookup field also filters out the corresponding values.

corresponding values

This enhances the user experience, as it helps narrow down the number of values in a lookup field, searching for a record from number of irrelevant records is quite cumbersome. Filtering the lookups based on any field type (Option set, Multioption) can help the user quickly choose the required value.

Also, don’t hesitate to reach out to us if you have any questions on implementing filtering lookups based on option set (picklist) field in Dynamics CRM. You can also connect with our BOLDEnthusiasts by clicking here.

5 thoughts on “Filtering Lookups based on Option set (picklist) field in Dynamics CRM”

  1. Hi,

    The removePreSearch method is depreciated in Unified UI. So is there any alternatives?

    Regards,
    Hasi

  2. Hi,
    How to filter the lookup result from the selected values for Multiselect option set without using fecthXML for crm 365 and POrtal

  3. Hi

    What will be the logic, if user come to this form on same record next time, and user do not change the sports type and directly use the lookup, is this lookup will be filtered? Or we have to run same code on form load as well?

Leave a Reply

Your email address will not be published. Required fields are marked *