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();
}