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
Nielf
Regular Visitor

I ran into this challenge again and started googling to see if there had been any updates since I last checked. E.g. that I might be supported to inject custom FetchXML using JavaScript. However, that doesn't seem to be the case. 

 

I must say I am impressed with your effort, @RHOU, driving votes from all around the various forum threads. Hopefully, this will be prioritized at some point. 

NancyK
New Member

We have achieved this functionality using custom pages either as a pop up window or as an embedded canvas app within the form.

 

Both are supported and allow for a great deal of freedom such as adding CTAs and respecting security priviledges. Of course the downsides are delegation issues (depending on the complexity of the query) and implementation time although that should be comparable to at least some of the provided workarounds.

NielsL
Helper V

@NancyK 

 

I it possible for you to provide an example of how you have solved this using a custom page/canvas app? 

NancyK
New Member

We first created a button for the custom page and used the code in the documentation to call it Navigating to and from a custom page in your model-driven app using client API - Power Apps | Micros....

 

Then, we applied an editable grid like Reza Rorrani showcases in his excellent video Power Apps Editable Table/Gallery like Excel (Tutorial) - YouTube using canvas app formulas (dataverse connector) to shape and query the data. 

 

However, if you would like to use fetchxml straight from custom page you can trigger a flow, run the fetchxml and then return the data to power apps. This post shows the different approaches [Canvas Apps – Dataverse] How to get distinct values from datasource ? – XRM Tricks (Power Platform .... using the distinct function example.

 

Our requirements did not require a grid to be shown whenever the user navigated to the form so the custom ribbon button method was preferred. Under different circumstances we might have build an embedded canvas app and gone from there. In both cases you can get the current record details within the power app and use that to form your query.

NielsL
Helper V

Thanks for taking the time to write your example, @NancyK

 

I am trying to show hierarchal data dynamically in a sub-grid. The best option for that might be to use the method in the link by XRM Tricks. 

swenst
New Member

Yes, I support the both A & B.

Adding such feature to MS CE product makes it more flexible to adapt business need to visualize data in the way user works.

Alen
Regular Visitor

I still can't be believe MS isn't providing a .js function to do this in a supported way. Siebel CRM was able to do this in 2007