Filter Project Lookup by the Selected Bookable Resource

Business Problem:

Filtering the list of projects displayed when submitting a time entry can be controlled by setting a view where one of the conditions is that the current logged in user is a member of the project. Well and great.

However, what if there is also a case in which subcontractors work on a project and the time they spent on tasks need to be tracked? There should be no problem if they are to be assigned a Dynamics 365 license so they could open the app and submit a entry. But as they are external individuals, obviously, they are not given a license. In this case, the time entries are submitted on their behalf by a licensed user.

Solution:

I wrote a simple code to achieve a filtered projects list. (Note: I’m no pro-developer so I’m not sure how elegant the code was written, but it works!)

The first two functions (SetCurrentUserasBookableResource & SetProjectFilterOnCurrentUse) are called when the form loads. This is just to have the projects filtered based on whoever is logged in. The variable currentuser holds that information.

The function SetProjectFilterOnBookableResourceChange is triggered when the Bookable Resource is changed to some value. The listed projects when selecting a value on the project lookup field is now filtered by the selected Bookable Resource.

Finally, the function HideProjectandTask, as the name suggests, hides the Project and Project Task fields when the Bookable Resource is cleared by the user. If we keep the two fields unhidden, then with the Bookable Resource blank, the project field will show the entire list of projects, which is, of course, something we don’t want to happen.

var SetBookableResourceandProjectFilter = window.SetBookableResourceandProjectFilter || {};
(function (){
    var currentuser = Xrm.Utility.getGlobalContext().userSettings.userId;
    this.SetCurrentUserasBookableResource = function (executionContext) {
        var formContext = executionContext.getFormContext();
        var fetchXml = '?fetchXml=<fetch mapping="logical">'
        + '<entity name="bookableresource">'
        + '<attribute name="name"/>'
        + '<attribute name="bookableresourceid"/>'
        + '<attribute name="userid"/>'
        + '<filter type="and">'
        + '<condition attribute="userid" operator="eq" uitype="systemuser" value="'
        + currentuser
        + '"/>'
        + '</filter>'
        + '</entity>'
        + '</fetch>';
        
       
        Xrm.WebApi.retrieveMultipleRecords("bookableresource", fetchXml).then(
            function success(result){
                formContext.getAttribute("msdyn_bookableresource").setValue([{id: result.entities[0].bookableresourceid, name: result.entities[0].name, entityType: "bookableresource"}]);
            }
        )
    }
    this.SetProjectFilterOnCurrentUser = function (executionContext) {
        var formContext = executionContext.getFormContext();
        var viewId = "{7da9ff1c-097a-43cc-8483-35951e8dc947}";
        var entityName = "msdyn_project";
        var viewDisplayName = "Filtered Projects";
        var projectfilter = '<fetch mapping="logical">'
            + '<entity name="msdyn_project">'
            + '<attribute name="msdyn_projectid"/>'
            + '<attribute name="msdyn_customer"/>'
            + '<attribute name="msdyn_subject"/>'
            + '<attribute name="msdyn_projectmanager"/>'            
            + '<filter type="and">'
            + '<condition attribute="msdyn_istemplate" operator="eq" value="0"/>'
            + '</filter>'
            + '<link-entity name="msdyn_projectteam" from="msdyn_project" to="msdyn_projectid" link-type="inner" alias="ae">'
            + '<link-entity name="bookableresource" from="bookableresourceid" to="msdyn_bookableresourceid" link-type="inner" alias="af">'
            + '<filter type="and">'
            + '<condition attribute="userid" operator="eq" uitype="systemuser" value="'
            + currentuser
            + '"/>'
            + '</filter>'
            + '</link-entity>'
            + '</link-entity>'
            + '</entity>'
            + '</fetch>';
        var layoutXml = '<grid name="resultset" jump="msdyn_subject" select="1" preview="1" icon="1" object="10064"><row name="result" id="msdyn_projectid"><cell name="msdyn_customer" width="100"/><cell name="msdyn_subject" width="150"/><cell name="msdyn_projectmanager" width="100"/></row></grid>'
        formContext.getControl("msdyn_project").addCustomView(viewId, entityName, viewDisplayName, projectfilter, layoutXml, true);
    }
    this.SetProjectFilterOnBookableResourceChange = function (executionContext) {
        var formContext = executionContext.getFormContext();
        var bookableresource;
        
        if (formContext.getAttribute("msdyn_bookableresource").getValue() !== null){
            bookableresource = formContext.getAttribute("msdyn_bookableresource").getValue()[0].id;
            var viewId = "{7da9ff1c-097a-43cc-8483-35951e8dc058}";
            var entityName = "msdyn_project";
            var viewDisplayName = "Filtered Projects";
            var projectfilter = '<fetch mapping="logical">'
                + '<entity name="msdyn_project">'
                + '<attribute name="msdyn_projectid"/>'
                + '<attribute name="msdyn_customer"/>'
                + '<attribute name="msdyn_subject"/>'
                + '<attribute name="msdyn_projectmanager"/>'            
                + '<filter type="and">'
                + '<condition attribute="msdyn_istemplate" operator="eq" value="0"/>'
                + '</filter>'
                + '<link-entity name="msdyn_projectteam" from="msdyn_project" to="msdyn_projectid" link-type="inner" alias="ae">'
                + '<link-entity name="bookableresource" from="bookableresourceid" to="msdyn_bookableresourceid" link-type="inner" alias="af">'
                + '<filter type="and">'
                + '<condition attribute="bookableresourceid" operator="eq" uitype="bookableresource" value="'
                + bookableresource
                + '"/>'
                + '</filter>'
                + '</link-entity>'
                + '</link-entity>'
                + '</entity>'
                + '</fetch>';
            var layoutXml = '<grid name="resultset" jump="msdyn_subject" select="1" preview="1" icon="1" object="10064"><row name="result" id="msdyn_projectid"><cell name="msdyn_customer" width="100"/><cell name="msdyn_subject" width="150"/><cell name="msdyn_projectmanager" width="100"/></row></grid>'
            formContext.getControl("msdyn_project").addCustomView(viewId, entityName, viewDisplayName, projectfilter, layoutXml, true);
        } else {
            var viewId = "{7da9ff1c-097a-43cc-8483-35951e8dc058}";
            var entityName = "msdyn_project";
            var viewDisplayName = "Filtered Projects";
            var projectfilter = '<fetch mapping="logical">'
                + '<entity name="msdyn_project">'
                + '<attribute name="msdyn_projectid"/>'
                + '<attribute name="msdyn_customer"/>'
                + '<attribute name="msdyn_subject"/>'
                + '<attribute name="msdyn_projectmanager"/>'            
                + '<filter type="and">'
                + '<condition attribute="msdyn_istemplate" operator="eq" value="0"/>'
                + '</filter>'
                + '<link-entity name="msdyn_projectteam" from="msdyn_project" to="msdyn_projectid" link-type="inner" alias="ae">'
                + '<link-entity name="bookableresource" from="bookableresourceid" to="msdyn_bookableresourceid" link-type="inner" alias="af">'
                + '<filter type="and">'
                + '<condition attribute="bookableresourceid" operator="eq" uitype="bookableresource" value="0"/>'
                + '</filter>'
                + '</link-entity>'
                + '</link-entity>'
                + '</entity>'
                + '</fetch>';
            var layoutXml = '<grid name="resultset" jump="msdyn_subject" select="1" preview="1" icon="1" object="10064"><row name="result" id="msdyn_projectid"><cell name="msdyn_customer" width="100"/><cell name="msdyn_subject" width="150"/><cell name="msdyn_projectmanager" width="100"/></row></grid>'
            formContext.getControl("msdyn_project").addCustomView(viewId, entityName, viewDisplayName, projectfilter, layoutXml, true);
        }
    }
    this.HideProjectandTask = function (executionContext) {
           
        var formContext = executionContext.getFormContext();
        var bookableresource = formContext.getAttribute("msdyn_bookableresource").getValue();
        var type = formContext.getAttribute("msdyn_type").getValue();
        if (bookableresource === null && type === 192350000) {
            formContext.getControl("msdyn_project").setVisible(false);
            formContext.getControl("msdyn_projecttask").setVisible(false);
        }
        else if (bookableresource !== null && type === 192350000){
            formContext.getControl("msdyn_project").setVisible(true);
            formContext.getControl("msdyn_projecttask").setVisible(true);
        }
    
    }
    
}).call(SetBookableResourceandProjectFilter);

Form configuration onload:

Form configuration on change of Bookable Resource:

Hope this helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s