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:

  1. Contract 1
  2. Contract 2
  3. 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

CRM-2105-A-Datum-Hierarchy
Figure: Three Customers in a Hierarchy Relationship

CRM-2105-Contract-ADatum
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”

CRM-2015-Contracts-SubGrid
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:

CRM-2015-ADatum-All-Contracts
Figure: SubGrid showing all Contracts

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

For CRM 2015 Update 1 use the following snippet:

 

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

CRM-2015-ADatum-Filtered-Contracts
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.

9 Comments

  1. Dan Fink on July 29, 2015 at 11:48 PM

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

    • Mehmet Ozdemir on August 14, 2015 at 8:19 AM

      Great point Dan! This method won’t work with Turbo Forms.

    • Mehmet Ozdemir on August 18, 2015 at 2:47 PM

      @Dan, Code sample updated to work with CRM 2015 Update 1.

  2. Sandeep V on 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.

    • Mehmet Ozdemir on 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.

  3. Benjamin Rutledge on 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?

  4. Charan on 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?

    • Mehmet Ozdemir on 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.

  5. Resam on 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?

Leave a Comment