Filtering User Lookup based on the Team

Requirement
While working on the Dynamics 365 CE implementation where we are using teams, sometime we want to filter user lookup based on the teams. Our requirement to check security type in our project entity if it is project based we need to filter user lookups based on the project teams which is created using 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
Assuming you are already aware of different options to filter lookup field, if not please refer following
1. Dependent Lookup Filtering
2. Adding customer filter FetchXML to your lookup
3. By Adding Custom View

The first option is to filter lookup based on the other lookup, so we can’t use as we don’t have second lookup in our case. The second option is about just passing filter to lookup and it will filter result according to the conditions in 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 third option. In our case we need to filter user lookup based on the different teams, which is another entity, if you will see here is the relationship between user and team is N:N.
user1
So to filter user lookup based this relationship we need to use addCustomView method from form control. First thing you should do is to design your query using Advanced Find or using tools in XrmToolBox. In our case we wanted to show only user if they belongs to HIMBAP teams, we have different teams based on the project name. If we design our requirement, in Advanced Find it will look like following.
user2
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.


if (typeof(HIMBAP) == "undefined") {
    var HIMBAP= {
        __namespace: true
    };
}
HIMBAP.ProjectMain = {

    OnLoad: function(executionContext, projectfield) {
      
        var formContext = executionContext.getFormContext();
      
        if (formContext.getAttribute(projectfield) != null &&
            formContext.getAttribute(projectfield).getValue() != null) {
            var projectID = formContext.getAttribute(projectfield).getValue()[0].id;
            var projectName = formContext.getAttribute(projectfield).getValue()[0].name;
            
          //get project details
            Xrm.WebApi.retrieveRecord("him_project", projectID, "?$select=him_projectsecuritytype").then(
                function success(result) {

                    //check for the security type
                    if (result.him_projectsecuritytype != null) {
                      
                        var securityType = result["him_projectsecuritytype@OData.Community.Display.V1.FormattedValue"];
                       
                        if (securityType == "Project Based") {
                            //setup custm lookup view
                            try {
                                var viewId = "{7A047D7A-D76F-4080-B035-4EDB276C59F5}";

                                var entity = "systemuser";
                              
                                var ViewDisplayName = "ProjectTeamMember";
                              
                                var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +
                                    "<entity name='systemuser'>" +
                                    "<attribute name='fullname'/>" +
                                    "<attribute name='title'/>" +
                                    "<attribute name='systemuserid'/>" +
                                    "<attribute name='businessunitid'/>" +
                                    "<order attribute='fullname' descending='false'/>" +
                                    "<link-entity name='teammembership' from='systemuserid' to='systemuserid' visible='false' intersect='true'>" +
                                    "<link-entity name='team' from='teamid' to='teamid' alias='ah'>" +
                                    "<filter type='and'>" +
                                    "<filter type='or'>" +
                                    "<condition attribute='name' operator='like' value='%" + projectName + "%'" + "/>" +
                                    "</filter></filter></link-entity></link-entity></entity></fetch>";

                                var layout = "<grid name='resultset' object='8' jump='fullname' select='1' icon='1' preview='1'>" +
                                    "<row name='result' id='systemuserid'>" +
                                    "<cell name='fullname' width='300'/>" +
                                    "</row></grid>";
                              
                                //Completed By
                                if (formContext.getControl("him_completedby") != null)
                                    formContext.getControl("him_completedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);
                              
                              //Approved By
                                if (formContext.getControl("him_approvedby") != null)
                                    formContext.getControl("him_approvedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);
                              
                              //Reviewedby By
                                if (formContext.getControl("him_reviewedby") != null)
                                    formContext.getControl("him_reviewedby").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);

                                //Assigned to
                                if (formContext.getControl("him_assignedto") != null)
                                    formContext.getControl("him_assignedto").addCustomView(viewId, entity, ViewDisplayName, fetchXML, layout, true);

                            } catch (error) {
                                console.log("Error while fetching Team members, Please contact Admin");
                            }
                        }
                    }

                },
                function(error) {
                    console.log(error.message);

                }
            );
        }

    }


};

Now we can call this method on Onload of our entity like following

HIMBAP.ProjectMain.OnLoad

We also need to pass project field name from current entity form to query project entity.

Leave a Reply