Filtering Lookups based on Option set (picklist) field in Dynamics CRM
Mehak Narmeen
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.
Read more about Dynamics 365: A Deep Dive Into Its CRM Capabilities
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.
To change the following lookup field to show the relevant values, we need to incorporate the custom code mentioned below:
Transform Your CRM Experience
Discover how AlphaBOLD can help optimize your Dynamics CRM. Reach out to us today for expert advice and implementation support.
Request a DemoSchema:
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.
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.
Final Thoughts
As the option set changes, the lookup field also filters out the 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.
Elevate Your CRM Efficiency with AlphaBOLD
Discover how AlphaBOLD's expertise in Dynamics CRM can transform your data management.
Request a DemoExplore Recent Blog Posts
Hi,
The removePreSearch method is depreciated in Unified UI. So is there any alternatives?
Regards,
Hasi
Hi Hasi,
This is not depreciated as per Microsoft docs:
https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/removepresearch
Hi,
How to filter the lookup result from the selected values for Multiselect option set without using fecthXML for crm 365 and POrtal
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?
Hi Arif,
The feature you are inquiring about will be covered in an upcoming blog! Stay tuned.