cancel
Showing results for 
Search instead for 
Did you mean: 

Binding FetchXML to Subgrid with reference to RecordID to filter Subgrid dynamically

Subgrids are great but the limitation of needing a direct relationship between the sub entity and the main entity makes it very restrictive. There are many different use cases, if instead of using a pre-configured view that only allows linking to the active Record via a predefined relationship, we could instead bind a custom FetchXML do a subgrid and this custom FetchXML would allow for one token called #RecordID# to reference the currently open record on the form by returning the GUID. 

 

Use Cases of Subgrids that could be placed on an account if we could filter based on the active record: 

  •  fully support subgrids that include records down the hierarchy
    • e.g. show opportunities of this account and all accounts lower in the hierarchy
      (Opportunities WHERE AccountID = #RecordID# OR AccountID.AccountID UNDER #RecordID#)
  • fully support subgrids showing data that is connected via multiple relationships 
    • e.g. show all orders that have the AccountID of the active record in Ship To Account OR Bill To Account
      (Orders WHERE BillToAccount = #RecordID# OR ShipToAccount = #RecordID#

  • fully support subgrids going down 2 or 3 or n levels instead of just the one direct level possible out of the box
    • e.g. show all event registrations off Contacts at this Account
      (Event Registrations WHERE Contact.ParentAccount = #RecordID#)

 

Please either make this available as

  • Solution Option A: via a direct customization option in PowerApps (instead of selecting a view for the subgrid, allow the user to copy/paste a fetchXML query into a text field that can contain a #RecordID# token to link back to the GUID of the record of the currently open form)

  • Solution Option B: by supporting something like the subgrid.control.setParameter("fetchxml") so that we can at least create this fetchXML dynamically and bind it to the subgrid with supported Javascript onLoad of the form or tab.  (the method explained in most of the forum  posts below). This is a reasonable amount of effort if you cannot support Option A. 

  • In a perfect world you support both Option A + B

 

There are hundreds of forum entries around the web using unsupported hacks to achieve the same results.

 

In Classic UI people used the subgrid.control.SetParameter("fetchxml") function but this is now broken in Unified Interface. In Unified Interface it appears a Retrieve Multiple Plugin is the only way to achieve this which is extremely complicated for something as simple as binding a query to a subgrid. 

 

Here is the only way to currently do this: 

https://sank8sinha.wordpress.com/2020/01/07/adding-filtered-views-in-uci-in-dynamics-365-crm-finally...

 

Already existing similar ideas that pretty much are all asking for the same thing:
Subgrid - Read / Write FetchXml / LayoutXml 

Subgrid - Update Fetchxml 

Adding custom fetchXML to a SubGrid - not officially supported after Dynamics CRM 2015 Update 1 

Updating the fetchxml/filter on a subgrid 

Dynamically set the sub-grid FetchXML 

Enable Custom Filters on Subgrids 

addCustomView to subgrid 

SubGrids and Related Items 

 

 

Here are links to various Blogs instructing people how to achieve this with unsupported methods but these no longer work in Unified Interface: 

http://dotnetdust.blogspot.com/2020/06/setting-sub-grid-filterxml-in-unified.html

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/299697/dynamics-365-unified-interf...

https://crm365blog.wordpress.com/2019/06/04/filter-subgrid-grid-in-dynamics-365/

https://medium.com/@meelamri23/dynamically-set-fetchxml-to-subgrid-on-dynamics-365-v9-uci-a4a531200e...

https://goutamdascrm.wordpress.com/tag/subgrid-custom-filter/

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/216881/how-to-set-up-custom-fetchx...

https://stackoverflow.com/questions/44024756/how-to-apply-custom-fetchxml-to-a-subgrid-using-javascr...

https://github.com/MicrosoftDocs/dynamics-365-customer-engagement/issues/974

https://mkdynamicsunfied.blogspot.com/2020/04/filter-subgrid-in-dynamics-unified-hub.html

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/221757/filter-subgrid-records

https://stackoverflow.com/questions/37622413/setparameterfetchxml-fetchxml-doesnt-support-in-crm-201...

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/385753/filtering-subgrid-uci

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/361463/filtering-subgrid-using-jav...

https://community.dynamics.com/crm/b/crmbacklog/posts/crm-2015-applying-custom-fetchxml-to-a-subgrid...

https://community.dynamics.com/crm/b/akmscrmblog/posts/add-custom-fetch-xml-to-subgrid-lookup

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/317465/in-hub-how-to-filter-subgri...

https://community.dynamics.com/365/b/mfasih365crm/posts/filter-subgrid-in-dynamics-365

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/254441/subgrid-filter-by-javascrip...

https://community.dynamics.com/365/unified-interface/f/unified-interface-forum/383077/external-fetch...

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/388336/subgrid-filter-using-javasc...

 

 

A google search for control.SetParameter("fetchXml"  shows almost 1000 search results which shows how important and common this request is. 

 

https://www.google.com/search?rlz=1C1GCEU_enUS897US897&sxsrf=ALeKk01jwF6Ja3CTc7rGhNAuPJ4RUV3eEw%3A15...

 

Status: New
Comments
Kudo Collector

Here is another unsupported solution (working in Unified Interface) which is more elegant than the other ones that I have found online.

http://dotnetdust.blogspot.com/2020/06/setting-sub-grid-filterxml-in-unified.html

Advocate I

I kinda like this idea 🙂

Voting for option A, or at least that some sort of placeholder be respected, then we can handle everything else from FetchXML Builder.