Filter Data in a Subgrid Dynamically

Case: I wanted to display a list of records (say Entity B) in a form of Entity A. The data of Entity B displayed is filtered based on the equality of two lookup fields (namely, customer and supplier) between Entity B and Entity A. Further, there is no relationship that links the two entities.

From a configuration standpoint, this isn’t doable, even if the two have a 1:N relationship. But, as Google is everybody’s best friend, I had to look it up to be absolutely sure. And rightfully so, it isn’t. So I had to turn to scripting to meet the requirement.

There’s a wealth of references available online that tackles the same. I’m no pro-developer so I kind of just depend on what others had written (see references at the bottom), read them (as I think I could) and just take it from there. Here is the entire javascript code that solved it for me:

var SDK = window.SDK || {};
(function () {
this.filterEntityBsGrid = function (executionContext) {
    //---Get the form context using execution context
    var formContext = executionContext.getFormContext();
 
    //---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity's form
    var conSubGrid = formContext.getControl("EntityBSubgrid");
 
    var customer = null;
    var supplier = null;
    if (formContext.getAttribute("new_customer").getValue() != null && formContext.getAttribute("new_supplier").getValue() != null) {
        customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
		supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
    }
 
 
    //Set Grid to Empty if Customer or Supplier is null
	
		var fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_entitybid" descending="false" />'
						+	'<filter type="and">'
						+	'  <filter type="or">'
						+	'	<condition attribute="new_customer" operator="null" />'
						+	'	<condition attribute="new_supplier" operator="null" />'
						+	'  </filter>'
						+	'</filter>'
						+  	'</entity>'
						+	'</fetch>';
 
    if (customer != null && supplier != null) {
        //Show related records
					
		fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_name" />'
						+	'<attribute name="new_supplier" />'
						+	'<attribute name="new_notes" />'
						+	'<attribute name="new_customer" />'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_name" descending="false" />'
						+	'<filter type="and">'
						+	'<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
						+	'<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
						+	'</filter>'
						+	'</entity>'
						+	'</fetch>';
        }
       
    conSubGrid.setFilterXml(fetchXml);
    //Refresh grid to show filtered records only.
    conSubGrid.refresh();
    }
}).call(SDK);

Adding the above code to fire on form load, on save, and on change (of customer and supplier) events, this is how the form behaves:

Wish the screen is a bit brighter and sharper though…

That’s it! 🙂

UPDATE:

The above code didn’t work in a non-UCI environment, specifically, the setFilterXML() method. In lieu of it is the setParameter property of the getgrid() method. Furthermore, I had to add a few more if conditions to cover for the other combinations of the customer and supplier variables.

var FilterOppGrid = window.FilterGrid || {};
(function () {
this.filterOpenOppGrid = function (executionContext) {
    //---Get the form context using execution context
    var formContext = executionContext.getFormContext();
 
    //---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity A's form
    var conSubGrid = formContext.getControl("EntityBSubgrid");
    var customer = null;
    var supplier = null;
    
    if (formContext.getAttribute("new_customer").getValue() != null) {
        customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
    }
    if (formContext.getAttribute("new_supplier").getValue() != null) {
		supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
    }
 
    //Create FetchXML for sub grid to filter records
 
    //Set grid to empty if Customer or Supplier is null
	
	var fetchXml = null;
    if (customer == null && supplier == null) {
         //Show related records
        fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_entitybid" descending="false" />'
						+	'<filter type="and">'
						+	'<condition attribute="new_customer" operator="null" />'
						+	'<condition attribute="new_supplier" operator="null" />'
						+	'</filter>'
						+  	'</entity>'
						+	'</fetch>';
        }
 
    if (customer != null && supplier != null) {
        //Show related records
		
		fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_entitybid" descending="false" />'
						+	'<filter type="and">'
						+	'<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
						+	'<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
                        +   '<condition attribute="statecode" operator="eq" value="0" />'
						+	'</filter>'
						+	'</entity>'
						+	'</fetch>';
        }
    if (customer != null && supplier == null) {
            //Show related records
            
            fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_entitybid" descending="false" />'
                            +	'<filter type="and">'
                            +	'<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
                            +	'<condition attribute="new_supplier" operator="null" />'
                            +   '<condition attribute="statecode" operator="eq" value="0" />'
                            +	'</filter>'
                            +	'</entity>'
                            +	'</fetch>';
        }
    if (customer == null && supplier != null) {
                //Show related records
                
            fetchXml =	'<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
						+	'<entity name="new_entityb">'
						+	'<attribute name="new_entitybid" />'
						+	'<order attribute="new_entitybid" descending="false" />'
                            +	'<filter type="and">'
                            +	'<condition attribute="new_customer" operator="null" />'
                            +	'<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
                            +   '<condition attribute="statecode" operator="eq" value="0" />'
                            +	'</filter>'
                            +	'</entity>'
                            +	'</fetch>';
        }
       
    //conSubGrid.setFilterXml(fetchXml);
    conSubGrid.getGrid().setParameter("fetchXml", fetchXml);
    //Refresh grid to show filtered records only.
    conSubGrid.refresh();	
    }
}).call(FilterOppGrid);

Lastly, I needed to refresh the subgrid once the form loads as the data in it aren’t what I was looking for. Only until performing some action in the subgrid does it show the filtered data. Obviously, that is not desirable. To refresh the subgrid, I came up with the below code:

var SubgridRefresh = window.SubgridRefresh || {};
(function () {
this.SubgridRefresh = function (executionContext) {
    //---Get the form context using execution context
    var formContext = executionContext.getFormContext();
 
    //---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity A's form
    var conSubGrid = formContext.getControl("EntityBSubgrid");
    
    var customer = null;
    var supplier = null;
    var conSubGridCount = null;
    if (formContext.getAttribute("new_customer").getValue() != null && formContext.getAttribute("new_supplier").getValue() != null) {
        customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
		supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
    }
 
    if (customer != null || supplier != null) {
        var conSubGridCount = conSubGrid.getGrid().getTotalRecordCount();
    }
    if (conSubGridCount > 0) {
        conSubGrid.refresh();
    }
}
}).call(SubgridRefresh);

And hopefully, that really is it.

References:
https://crm365blog.wordpress.com/2019/06/04/filter-subgrid-grid-in-dynamics-365/
https://blog.magnetismsolutions.com/blog/jaredjohnson/2018/07/12/using-fetchxml-with-xrm-webapi-retrievemultiplerecords-in-dynamics-365-v9
https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords

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