Blog, Howto

Showing indirectly related records in the form’s grid

During my recent project, I resolved a few similar tasks – the requirement was to show records that are not directly related to the current one. Depending on the scenario you can use one of 2 ways I provided in this post.

No-Code way

The first way is quite limited in possibilities and applicable scenarios but won’t require a single line of code. Here is the scenario that can be addressed this way – when a user opens the contact form the grid on the form has to show opportunities that are related to the parent account.

Using maker experience or classic solution explorer navigate to the account and create a new “Quick View” form. Place the grid of opportunities to it, save and publish the form:

Open the “Contact” form and add a “Quick View” form to it:

And now, let’s check how it works:

As you may see this approach works fine but it has a limited number of application scenarios. If you need more flexibility you might be interested in the “Code way”.

Code way

During a recent project, I was working on the implementation of “Dynamics 365 for Field Service” application. The task was to show all the Work Orders related to the current or any children’s Functional Location for a given Functional Location.

In the first step, I opened the form of “Functional Location” and added the grid of “Work Orders” with no filtration by the current record:

In the second step, I developed the following JavaScript that queries all the children functional locations for a given functional location, builds the filter, appends that filter to the grid added in the first step, and refreshes the grid to display the results:

function formOnLoad(executionContext) {
    var formContext = executionContext.getFormContext();

    var workOrdersGrid = formContext.getControl("WorkOrders");

    if (formContext.ui.getFormType() === 1) {
        //that's done to hide all the work orders available from the grid when the record is not created yet
        var blankFilter = [
            "<filter>",
            "<condition attribute='msdyn_functionallocation' operator='eq' value='00000000-0000-0000-0000-000000000000'/>",
            "</filter>"].join("");
        workOrdersGrid.setFilterXml(blankFilter);
        workOrdersGrid.refresh();
        return;
    }

    var currentRecordId = formContext.data.entity.getId();

    //I build the query to retrieve all the functional locations in the hierarchy
    var functionalLocationsFetchXml = [
        "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>",
        "<entity name='msdyn_functionallocation'>",
        "<attribute name='msdyn_functionallocationid' />",
        "<filter type='and'>",
        "<condition attribute='msdyn_functionallocationid' operator='eq-or-under' value='", currentRecordId , "' />",
        "</filter>",
        "</entity>",
        "</fetch>"
    ].join("");

    //Querying the records
    Xrm.WebApi.retrieveMultipleRecords("msdyn_functionallocation", "?fetchXml=" + encodeURIComponent(functionalLocationsFetchXml)).then(
        function(results) {
            var functionalLocationsFilter = results.entities.map(
                function(r) { 
                    return ("<value>" + r.msdyn_functionallocationid + "</value>");
                }).join("");
            
            //Composing the resulting filter for the grid
            var filter = [
                "<filter>",
                "<condition attribute='msdyn_functionallocation' operator='in'>",
                functionalLocationsFilter,
                "</condition>",
                "</filter>"
            ].join("");

            workOrdersGrid.setFilterXml(filter);
            workOrdersGrid.refresh();
        }, 
        Xrm.Navigation.openErrorDialog);
}

Put the code to the webresource and register formOnLoad hander as onload form handler (don’t forget to check the “Pass execution context as first parameter” checkbox). And let’s test what we got – here is the hierarchy of functional locations:

And here is the grid of work orders:

I understand that there are more ways available to show such records like embedded canvas app/custom page, PCF controls, and Html/Js WebResources, and all those solutions can be used but in this post, I didn’t want to pull up the “Big Developer Gun”. Unless, if you are aware of any other no-code/low-code ways to implement similar requirements – feel free to leave a comment under this post.

Cover Photo by John Schnobrich on Unsplash

10 Comments

  1. Would it work if you add

    as a filter? and tags sit at the same level of fetch xml, so setting a link with nested filter should not break xml structure. This, at least, work for similar filter customization in Power Portals.

  2. Wow, this article truly sheds light on a subject that’s often overlooked – displaying indirectly related records in forms grid. As someone who’s been navigating the world of data management, this fresh perspective has given me a lot to think about.

  3. Thank you, Andrew, for sharing these two approaches for displaying indirectly related records in a form’s grid. Your insights are valuable for anyone working with Dynamics 365 and facing similar challenges.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.