How to apply custom FetchXML to a Subgrid using JavaScript

2 min read 07-10-2024

How to apply custom FetchXML to a Subgrid using JavaScript

Powering Up Subgrids: Applying Custom FetchXML with JavaScript

Subgrids in Microsoft Dynamics 365 are incredibly versatile tools for displaying related data within a record's context. However, their default views might not always provide the specific information you need. This is where custom FetchXML comes in, allowing you to tailor subgrid views to your exact requirements using JavaScript.

The Challenge: Controlling Subgrid Data with Precision

Imagine you're working with a CRM solution where a "Leads" subgrid on the "Account" entity needs to show only leads with a specific status. The default subgrid view doesn't provide this filtering option, forcing you to manually sift through irrelevant data.

The Solution: FetchXML and JavaScript to the Rescue

Let's use the "Leads" subgrid scenario to demonstrate how custom FetchXML, combined with JavaScript, can solve this problem.

Original Code (Default Subgrid View):

// No specific filtering applied, showing all leads.

Enhanced Code with Custom FetchXML:

// Define your custom FetchXML
var fetchXml = `<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="lead">
    <attribute name="leadid" />
    <attribute name="firstname" />
    <attribute name="lastname" />
    <filter type="and">
      <condition attribute="statuscode" operator="eq" value="1" /> </filter>
  </entity>
</fetch>`;

// Get the subgrid control
var subGridControl = Xrm.Page.ui.controls.get("Leads"); 

// Apply the custom FetchXML
subGridControl.setFetchXml(fetchXml);

Explanation:

FetchXML Definition: We create a FetchXML string that defines our desired subgrid view. The condition element filters the subgrid to display only leads with a statuscode of "1" (representing the "Qualified" status, for example).
Subgrid Control Retrieval: We use the Xrm.Page.ui.controls.get() method to retrieve the "Leads" subgrid control.
Applying the FetchXML: Finally, we use the setFetchXml() method on the subgrid control to apply the custom FetchXML, dynamically modifying the subgrid's view.

The Benefits of Using Custom FetchXML:

Increased Control: You have complete control over the data displayed in the subgrid, filtering by specific criteria, including fields, relationships, and conditions.
Enhanced Efficiency: Eliminate unnecessary scrolling and browsing by presenting only relevant data to users.
Tailored Views: Create personalized subgrid views for different roles or use cases, improving user experience and productivity.
Dynamic Data Filtering: Implement dynamic filters based on user input or changes in related data.

Going Beyond Basic Filtering

Custom FetchXML allows you to do much more than simple filtering. You can:

Sort Data: Control the order of data within the subgrid using the order element.
Join Data: Include data from related entities using link-entity elements, providing richer insights.
Include Calculated Fields: Create custom columns in the subgrid using attribute elements with calculated values.

Resources for Further Exploration:

Microsoft Dynamics 365 FetchXML Reference: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/webapi/fetchxml-reference
Dynamics 365 FetchXML Builder: https://www.fetchxmlbuilder.com/

By mastering custom FetchXML and incorporating it into your JavaScript code, you can elevate the functionality of your subgrids, streamlining data access and empowering users with more informed decision-making.