March 30, 2015

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

Written by

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.

Category : Microsoft Dynamics

Comments

9 Responses

  1. Dan Fink says:

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

  2. Sandeep V says:

    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.

  3. 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 says:

    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.

  5. Resam says:

    @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 Reply

Your email address will not be published. Required fields are marked *