Dynamics CRM

Filtering User Lookup Based On The Team

Requirement


While working on the Dynamics 365 CE implementation where we are using teams, sometimes we want to filter user lookup based on the teams. Our requirement is to check the security type in our project entity if it is project based. And we need to filter user lookups based on the project teams which are created using the project name. So let’s see how we can do that.
 
I am assuming you are already aware of how to write Java script code for Dynamics 365 CE.
 

Solution

 
I am assuming you are already aware of different options to filter lookup field, if not please refer to the following,
The first option is to filter lookup based on the other lookup, but  we can’t use it as we don’t have a second lookup in our case. The second option is about just passing filter to lookup and it will filter the result according to the conditions in the filter. But this option can’t be used if we want to filter our lookup based on the Link Entity, so we need to use a third option. In our case we need to filter user lookup based on different teams, which is another entity. As you can see below,  the relationship between user and team is N:N.
 
Filtering User Lookup Based On The Team
 
So to filter user lookup based on this relationship we need to use addCustomView method from form control. The first thing you should do is design your query using Advanced Find or using tools in XrmToolBox. In our case we wanted to show a user if they belong to HIMBAP teams. We have different teams based on the project name. If we design our requirement, in Advanced Find it will look like the following.
 
Filtering User Lookup Based On The Team
 
We can use Download Fetch XML button and use it in our code. Here is the code to filter different user lookups based on the project team.
  1. if (typeof(HIMBAP) == "undefined") {  
  2.     var HIMBAP= {  
  3.         __namespace: true  
  4.     };  
  5. }  
  6. HIMBAP.ProjectMain = {  
  7.    
  8.     OnLoad: function(executionContext, projectfield) {  
  9.          
  10.         var formContext = executionContext.getFormContext();  
  11.          
  12.         if (formContext.getAttribute(projectfield) != null &&  
  13.             formContext.getAttribute(projectfield).getValue() != null) {  
  14.             var projectID = formContext.getAttribute(projectfield).getValue()[0].id;  
  15.             var projectName = formContext.getAttribute(projectfield).getValue()[0].name;  
  16.                
  17.           //get project details  
  18.             Xrm.WebApi.retrieveRecord("him_project", projectID, "?$select=him_projectsecuritytype").then(  
  19.                 function success(result) {  
  20.    
  21.                     //check for the security type  
  22.                     if (result.him_projectsecuritytype != null) {  
  23.                          
  24.                         var securityType = result["him_projectsecuritytype@OData.Community.Display.V1.FormattedValue"];  
  25.                           
  26.                         if (securityType == "Project Based") {  
  27.                             //setup custm lookup view  
  28.                             try {  
  29.                                 var viewId = "{7A047D7A-D76F-4080-B035-4EDB276C59F5}";  
  30.    
  31.                                 var entity = "systemuser";  
  32.                                  
  33.                                 var ViewDisplayName = "ProjectTeamMember";  
  34.                                  
  35.                                 var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +  
  36.                                     "<entity name='systemuser'>" +  
  37.                                     "<attribute name='fullname'/>" +  
  38.                                     "<attribute name='title'/>" +  
  39.                                     "<attribute name='systemuserid'/>" +  
  40.                                     "<attribute name='businessunitid'/>" +  
  41.                                     "<order attribute='fullname' descending='false'/>" +  
  42.                                     "<link-entity name='teammembership' from='systemuserid' to='systemuserid' visible='false' intersect='true'>" +  
  43.                                     "<link-entity name='team' from='teamid' to='teamid' alias='ah'>" +  
  44.                                     "<filter type='and'>" +  
  45.                                     "<filter type='or'>" +  
  46.                                     "<condition attribute='name' operator='like' value='%" + projectName + "%'" + "/>" +  
  47.                                     "</filter></filter></link-entity></link-entity></entity></fetch>";  
  48.    
  49.                                 var layout = "<grid name='resultset' object='8' jump='fullname' select='1' icon='1' preview='1'>" +  
  50.                                     "<row name='result' id='systemuserid'>" +  
  51.                                     "<cell name='fullname' width='300'/>" +  
  52.                                     "</row></grid>";  
  53.                                  
  54.                                 //Completed By  
  55.                                 if (formContext.getControl("him_completedby") != null)  
  56.                                     formContext.getControl("him_completedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);  
  57.                                  
  58.                               //Approved By  
  59.                                 if (formContext.getControl("him_approvedby") != null)  
  60.                                     formContext.getControl("him_approvedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);  
  61.                                  
  62.                               //Reviewedby By  
  63.                                 if (formContext.getControl("him_reviewedby") != null)  
  64.                                     formContext.getControl("him_reviewedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);  
  65.    
  66.                                 //Assigned to  
  67.                                 if (formContext.getControl("him_assignedto") != null)  
  68.                                     formContext.getControl("him_assignedto").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);  
  69.    
  70.                             } catch (error) {  
  71.                                 console.log("Error while fetching Team members, Please contact Admin");  
  72.                             }  
  73.                         }  
  74.                     }  
  75.    
  76.                 },  
  77.                 function(error) {  
  78.                     console.log(error.message);  
  79.    
  80.                 }  
  81.             );  
  82.         }  
  83.    
  84.     }  
  85.    
  86.    
  87. };  
Now we can call this method on the onload of our entity as follows: 
  1. HIMBAP.ProjectMain.OnLoad  
We also need to pass project field name from current entity form to query project entity.

Up Next
    Ebook Download
    View all
    Learn
    View all
    We are expert in Microsoft Power Platform.
    Membership not found