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,

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, 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.

 

 

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.

 

 

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["[e-mail]"];

                        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 the onload of our entity as follows: 

HIMBAP.ProjectMain.OnLoad

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