CRM 2015 (Update 1) filter a sub grid to show data based on hierarchy using fetchxml

Code Sample Updated for CRM 2015 Update 1

Scenario

Let’s say you have three Contracts in CRM defined for three Customers (Account).

Contract 1: Customer 1
Contract 2: Customer 2
Contract 3: Customer 3

And Customers 1 – 3 are in Parent Child relationships.

Customer 1
- Customer 2
  - Customer 3

We add a sub grid to the Accounts form and we want to display all Contracts that relate the the current Customer and its parent’s. This is because the Customer is able to take advantage of contracts that are signed by its parent’s.

So if we browse to Customer 3 we should see:

Contract 1
Contract 2
Contract 3

Browse to Customer 2 we should see:

Contract 1
Contract 2

And finally browse to Customer 1 we should see:

Contract 1

The built in Sub Grid only supports “All Record Types” and “Only Related Records”

Let’s take a look at how we can show “Only Related Records (with Hierarchy)” (I made that one up).

The trick to getting this working is to use a FetchXML hierarchy operator, more information at:

https://msdn.microsoft.com/en-us/library/dn817893.aspx

The operator that solves this problem is eq-or-above.

Solution

Figure: Three Customers in a Hierarchy Relationship

Figure: Three Contracts assigned to each customer

OK so now we have three customers and three contracts assigned to each customer. Now we need to wire up the Accounts form and add a Contracts SubGrid to show: “Only Related Records (with Hierarchy)”.

Let’s start by Adding a Contracts SubGrid and “All Record Types”

Figure: Add Contracts SubGrid to Accounts Form

Now browse the A. Datum 1.1.0 and we should expect to see Contract 1 and Contract 2, but for now it will show all three contracts:

Figure: SubGrid showing all Contracts

The next step is to add some Form JavaScript to filter the SubGrid by hierarchy:

function formOnLoad() {

	filterContractsGrid();
}

function filterContractsGrid() {
 
    //get the subgrid 
    var objSubGrid = document.getElementById("accountcontractsgrid");
 
    //CRM loads subgrid after form is loaded.. so when we are adding script on form load.. need to wait until sub grid is loaded. 
    // that's why we are adding a delay..
    if (objSubGrid == null) {
		setTimeout(filterContractsGrid, 2000);
        return;
    }
 
    else {
        //when subgrid is loaded, get GUID
        var GUIDvalue = Xrm.Page.data.entity.getId();
 
        //Create FetchXML for sub grid to filter records based on GUID
		var FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' >" +
							"<entity name='contract' >" +
								"<attribute name='title' />" + 
								"<attribute name='contractid' />" +
								"<attribute name='statecode' />" +
								"<order attribute='title' descending='false' />" +
								"<link-entity name='account' from='accountid' to='customerid' alias='af' >" +
									"<attribute name='name' />" +
									"<filter type='and' >" +
										"<condition attribute='accountid' operator='eq-or-above' value='" + GUIDvalue + "' />" +
									"</filter>" +
								"</link-entity>" +
							"</entity>" +
						"</fetch>";
 
        // Layout of subgrid.
        var LayoutXml = "<grid name='resultset' object='8' jump='title' select='1' preview='1' icon='1'>" +
                            " <row name='result' id='mmp_name'>" +
                                    "<cell name='title' width='300' />" +
                                    "<cell name='af.name' width='200' />" +
                                    "<cell name='statcode' width='100' />" +
                                "</row>" +
                         "</grid>";
 
        //apply layout and filtered fetchXML
        objSubGrid.control.SetParameter("layoutXml", LayoutXml);
        objSubGrid.control.SetParameter("fetchXml", FetchXml);
 
        //Refresh grid to show filtered records only. 
        objSubGrid.control.Refresh();
    }
}

function formOnLoad() {

filterContractsGrid();

}

function filterContractsGrid() {

//get the subgrid

var objSubGrid = document.getElementById("accountcontractsgrid");

//CRM loads subgrid after form is loaded.. so when we are adding script on form load.. need to wait until sub grid is loaded.

// that's why we are adding a delay..

if (objSubGrid == null) {

setTimeout(filterContractsGrid, 2000);

return;

}

else {

//when subgrid is loaded, get GUID

var GUIDvalue = Xrm.Page.data.entity.getId();

//Create FetchXML for sub grid to filter records based on GUID

var FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' >" +

"<entity name='contract' >" +

"<attribute name='title' />" +

"<attribute name='contractid' />" +

"<attribute name='statecode' />" +

"<order attribute='title' descending='false' />" +

"<link-entity name='account' from='accountid' to='customerid' alias='af' >" +

"<attribute name='name' />" +

"<filter type='and' >" +

"<condition attribute='accountid' operator='eq-or-above' value='" + GUIDvalue + "' />" +

"</filter>" +

"</link-entity>" +

"</entity>" +

"</fetch>";

// Layout of subgrid.

var LayoutXml = "<grid name='resultset' object='8' jump='title' select='1' preview='1' icon='1'>" +

" <row name='result' id='mmp_name'>" +

"<cell name='title' width='300' />" +

"<cell name='af.name' width='200' />" +

"<cell name='statcode' width='100' />" +

"</row>" +

"</grid>";

//apply layout and filtered fetchXML

objSubGrid.control.SetParameter("layoutXml", LayoutXml);

objSubGrid.control.SetParameter("fetchXml", FetchXml);

//Refresh grid to show filtered records only.

objSubGrid.control.Refresh();

}

For CRM 2015 Update 1 use the following snippet:

function formOnLoad() {

	filterContractsGrid();
}

function filterContractsGrid() {
 
    //get the subgrid 
    var objSubGrid = window.parent.document.getElementById("accountcontractsgrid");
 
    //CRM loads subgrid after form is loaded.. so when we are adding script on form load.. need to wait until sub grid is loaded. 
    // that's why we are adding a delay..
    var isGridLoaded = null;
    try {isGridLoaded = objSubGrid.control.getEntityName();}
    catch(err) 
    {  
        setTimeout(filterContractsGrid, 2000);
	return;
    }
    if (isGridLoaded != null) 
    { 
        //when subgrid is loaded, get GUID
        var GUIDvalue = Xrm.Page.data.entity.getId();
 
        //Create FetchXML for sub grid to filter records based on GUID
		var FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' >" +
							"<entity name='contract' >" +
								"<attribute name='title' />" + 
								"<attribute name='contractid' />" +
								"<attribute name='statecode' />" +
								"<order attribute='title' descending='false' />" +
								"<link-entity name='account' from='accountid' to='customerid' alias='af' >" +
									"<attribute name='name' />" +
									"<filter type='and' >" +
										"<condition attribute='accountid' operator='eq-or-above' value='" + GUIDvalue + "' />" +
									"</filter>" +
								"</link-entity>" +
							"</entity>" +
						"</fetch>";
 
        // Layout of subgrid.
        var LayoutXml = "<grid name='resultset' object='8' jump='title' select='1' preview='1' icon='1'>" +
                            " <row name='result' id='mmp_name'>" +
                                    "<cell name='title' width='300' />" +
                                    "<cell name='af.name' width='200' />" +
                                    "<cell name='statcode' width='100' />" +
                                "</row>" +
                         "</grid>";
 
        //apply layout and filtered fetchXML
        objSubGrid.control.SetParameter("layoutXml", LayoutXml);
        objSubGrid.control.SetParameter("fetchXml", FetchXml);
 
        //Refresh grid to show filtered records only. 
        objSubGrid.control.Refresh();
    }
}

function formOnLoad() {

filterContractsGrid();

}

function filterContractsGrid() {

//get the subgrid

var objSubGrid = window.parent.document.getElementById("accountcontractsgrid");

//CRM loads subgrid after form is loaded.. so when we are adding script on form load.. need to wait until sub grid is loaded.

// that's why we are adding a delay..

var isGridLoaded = null;

try {isGridLoaded = objSubGrid.control.getEntityName();}

catch(err)

{

setTimeout(filterContractsGrid, 2000);

return;

}

if (isGridLoaded != null)

{

//when subgrid is loaded, get GUID

var GUIDvalue = Xrm.Page.data.entity.getId();

//Create FetchXML for sub grid to filter records based on GUID

var FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' >" +

"<entity name='contract' >" +

"<attribute name='title' />" +

"<attribute name='contractid' />" +

"<attribute name='statecode' />" +

"<order attribute='title' descending='false' />" +

"<link-entity name='account' from='accountid' to='customerid' alias='af' >" +

"<attribute name='name' />" +

"<filter type='and' >" +

"<condition attribute='accountid' operator='eq-or-above' value='" + GUIDvalue + "' />" +

"</filter>" +

"</link-entity>" +

"</entity>" +

"</fetch>";

// Layout of subgrid.

var LayoutXml = "<grid name='resultset' object='8' jump='title' select='1' preview='1' icon='1'>" +

" <row name='result' id='mmp_name'>" +

"<cell name='title' width='300' />" +

"<cell name='af.name' width='200' />" +

"<cell name='statcode' width='100' />" +

"</row>" +

"</grid>";

//apply layout and filtered fetchXML

objSubGrid.control.SetParameter("layoutXml", LayoutXml);

objSubGrid.control.SetParameter("fetchXml", FetchXml);

//Refresh grid to show filtered records only.

objSubGrid.control.Refresh();

}

Now when A-Datum 1.1.0 is loaded the Contracts SubGrid will be filtered by Equal and Above:

Figure: The Contracts SubGrid is now filter to show only hierarchy related records

So that’s a quick demo on showing the new hierarchy features in CRM 2015 along with the new hierarchy Fetch XML operators, hopefully as CRM 2015 develops some of these cool new features will be baked into the form designer.

11 thoughts on “CRM 2015 (Update 1) filter a sub grid to show data based on hierarchy using fetchxml”

Dan Fink says:

July 29, 2015 at 11:48 PM

Note: This will not work with CRM Online. the “document.getElementById” DOM call is not supported anymore.

1. Mehmet Ozdemir says:
  
  August 14, 2015 at 8:19 AM
  
  Great point Dan! This method won’t work with Turbo Forms.
  
2. Mehmet Ozdemir says:
  
  August 18, 2015 at 2:47 PM
  
  @Dan, Code sample updated to work with CRM 2015 Update 1.
  
Sandeep V says:

August 25, 2015 at 6:21 PM

this method should work but i am getting exception at the objSubGrid.control.SetParameter line,
can you tell me any alternative method.

1. Mehmet Ozdemir says:
  
  August 26, 2015 at 11:00 AM
  
  @Sandeep, I would suggest you add debugger; just before the .SetParameter call. You should then be able to use the browser developer tools (F12) to inspect the objSubGrid object to see if you have control and SetParameter.
  
Benjamin Rutledge says:

September 4, 2015 at 4:21 AM

Is this useful to me if I wish to use the Turbo Opportunity Form in CRM 2015 update 1 to display all of the Marketing Lists that the selected Account (selected in the Opportunity record) is a member of?

Charan says:

December 28, 2016 at 6:27 PM

If I click on ‘+’ button, still it showing irrelevant records. How to filter this inline lookup and view in CRM 2016?

1. Mehmet Ozdemir says:
  
  March 3, 2017 at 8:02 AM
  
  Hi Charan,
  
  I’m unsure of the [+] button as in my example it was only used to for display and navigation purposes. In a later version of that form, I ended up hiding the [+] button.
  
Resam says:

August 7, 2017 at 12:17 AM

@mozdemir_au , Mehmet Azdemir your Solution was very helpful to me. I want to know is it possible to use this instruction on associated Record views?? and if it is possible how can i find their sub-grid name and how can i filter it Run when choose special view?

Tashi Dorji says:

September 23, 2020 at 3:01 PM

Hi,
This is a wonderful post. I was trying to replicate this on trail instance of CRM and it is not working. Will not run on trail instance?

Regards

1. Mehmet Ozdemir says:
  
  October 6, 2020 at 2:00 PM
  
  Hey Tashi, Assuming you’re using a trial instance in 2020 some the javascript will require tweaks for it to work correct (at a minimum). I will update this post when get a chance to reuse that snippet.