Filter Subgrid in Dynamics 365

In our day to day tasks related to dynamics 365 CRM, sometimes we need to filter Subgrid dynamically (placed on an entity form) based on data of some fields on the form. In this article, I am going to explain the same using JavaScript code.

Suppose the Subgrid name is “Booking”. This grid load records from an entity “bookable resource booking”. Our requirement was to filter this grid on the fly based on specific category value selected from an option set named “Category”. Here is the complete code.

function filterBookingsGrid(executionContext) {
    //---Get the form context using execution context
    var formContext = executionContext.getFormContext();

    //---Suppose there is a subgrid named "Booking" placed on your entity's form
    var conSubGrid = formContext.getControl("Booking");

    var category = null;
    if (formContext.getAttribute("new_category").getValue() != null) {
        category = formContext.getAttribute("new_category").getValue()[0].id.replace("}", "").replace("{", "");
    }

    //Create FetchXML for sub grid to filter records

    //Set Grid to Empty if Technician is Null
    var fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
                + '  <entity name="bookableresourcebooking">'
                + '    <attribute name="bookableresourcebookingid" />'
                + '    <order attribute="bookableresourcebookingid" descending="true" />'
                + '    <filter type="and">'
                + '      <condition attribute="bookableresourcebookingid" operator="null" />'
                + '    </filter>'
                + '  </entity>'
                + '</fetch>';

    if (category != null) {
        //Show Related Scheduled Bookings
        fetchXml = '<fetch version="1.0" output-format="xml - platform" mapping="logical" distinct="false"> '
                    + '<entity name="bookableresourcebooking" >'
                        + '<all-attributes />'
                        + '   <order attribute="createdon" descending="false" /> '
                        + '   <filter type="and"> '
                        + '       <condition entityname="BookingStatus" attribute="name" operator="eq" value="Scheduled" /> '
                        + '       <condition attribute="new_category" operator="eq" value="' + category + '" /> '
                        + '   </filter> '
                        + '<link-entity name="bookingstatus" from="bookingstatusid" to="bookingstatus" link-type="inner" alias="BookingStatus">'
                        + '<attribute name="bookingstatusid" />'
                        + '</link-entity>'
                        + '</entity > '
                    + '</fetch > ';
    }
    formContext.getControl("Booking").getGrid().setParameter("fetchXml", fetchXml);

    //Refresh grid to show filtered records only.
    formContext.ui.controls.get("Booking").refresh();
}

Author: M. Fasih Akbar

I have 12+ years experience in software industry mainly with Microsoft Technology stack. I have 7+ years of experience with ASP.Net. I am working with Microsoft Dynamics CRM since last 4+ years. Throughout my software development career, I have been engaged in project analyses, as well as in designing applications and databases. My major professional expertise belongs to object oriented web-based technologies like Dynamics CRM, Asp.Net, C#, JQuery, Angular JS, Knockout, Javascript, VB.Net, and MVC.

4 thoughts on “Filter Subgrid in Dynamics 365”

  1. Hello M. Fasih Akbar,
    Above code works perfectly fine in Dynamics 365.
    But in Unified Interface – Dynamics 365, it is failing with “formContext.getControl(…).getGrid(…).setParameter is not a function” error.
    Any help will be appreciated.

    Like

    1. Hi,

      This is known issue as per this thread: https://github.com/MicrosoftDocs/dynamics-365-customer-engagement/issues/974

      Possible tweaks which you can apply are mentioned in this post by Gautam:
      https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/299697/dynamics-365-unified-interface-inject-fetchxml-into-subgrid?pifragment-97030=1#responses

      Another link which might help you is this
      View at Medium.com

      On the other hand, you can see this thread what JavaScript changes came in version 9 are listed in this link
      https://sachinbansal.blog/2018/04/27/all-javascript-changes-dynamics-365-9-0/comment-page-1/

      Hopefully, it will be helpful to find an alternative approach for Unified Interface.

      Thanks,
      M. Fasih

      Like

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