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
- Customer 2
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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.
Note: This will not work with CRM Online. the “document.getElementById” DOM call is not supported anymore.
Great point Dan! This method won’t work with Turbo Forms.
@Dan, Code sample updated to work with CRM 2015 Update 1.
this method should work but i am getting exception at the objSubGrid.control.SetParameter line,
can you tell me any alternative method.
@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.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?
If I click on ‘+’ button, still it showing irrelevant records. How to filter this inline lookup and view in CRM 2016?
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.
@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?