During my recent project, I resolved a few similar tasks – the requirement was to show records that are not directly related to the current one. Depending on the scenario you can use one of 2 ways I provided in this post.
No-Code way
The first way is quite limited in possibilities and applicable scenarios but won’t require a single line of code. Here is the scenario that can be addressed this way – when a user opens the contact form the grid on the form has to show opportunities that are related to the parent account.
Using maker experience or classic solution explorer navigate to the account and create a new “Quick View” form. Place the grid of opportunities to it, save and publish the form:
Open the “Contact” form and add a “Quick View” form to it:
And now, let’s check how it works:
As you may see this approach works fine but it has a limited number of application scenarios. If you need more flexibility you might be interested in the “Code way”.
Code way
During a recent project, I was working on the implementation of “Dynamics 365 for Field Service” application. The task was to show all the Work Orders related to the current or any children’s Functional Location for a given Functional Location.
In the first step, I opened the form of “Functional Location” and added the grid of “Work Orders” with no filtration by the current record:
In the second step, I developed the following JavaScript that queries all the children functional locations for a given functional location, builds the filter, appends that filter to the grid added in the first step, and refreshes the grid to display the results:
|
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 |
function formOnLoad(executionContext) { var formContext = executionContext.getFormContext(); var workOrdersGrid = formContext.getControl("WorkOrders"); if (formContext.ui.getFormType() === 1) { //that's done to hide all the work orders available from the grid when the record is not created yet var blankFilter = [ "<filter>", "<condition attribute='msdyn_functionallocation' operator='eq' value='00000000-0000-0000-0000-000000000000'/>", "</filter>"].join(""); workOrdersGrid.setFilterXml(blankFilter); workOrdersGrid.refresh(); return; } var currentRecordId = formContext.data.entity.getId(); //I build the query to retrieve all the functional locations in the hierarchy var functionalLocationsFetchXml = [ "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>", "<entity name='msdyn_functionallocation'>", "<attribute name='msdyn_functionallocationid' />", "<filter type='and'>", "<condition attribute='msdyn_functionallocationid' operator='eq-or-under' value='", currentRecordId , "' />", "</filter>", "</entity>", "</fetch>" ].join(""); //Querying the records Xrm.WebApi.retrieveMultipleRecords("msdyn_functionallocation", "?fetchXml=" + encodeURIComponent(functionalLocationsFetchXml)).then( function(results) { var functionalLocationsFilter = results.entities.map( function(r) { return ("<value>" + r.msdyn_functionallocationid + "</value>"); }).join(""); //Composing the resulting filter for the grid var filter = [ "<filter>", "<condition attribute='msdyn_functionallocation' operator='in'>", functionalLocationsFilter, "</condition>", "</filter>" ].join(""); workOrdersGrid.setFilterXml(filter); workOrdersGrid.refresh(); }, Xrm.Navigation.openErrorDialog); } |
Put the code to the webresource and register formOnLoad hander as onload form handler (don’t forget to check the “Pass execution context as first parameter” checkbox). And let’s test what we got – here is the hierarchy of functional locations:
And here is the grid of work orders:
I understand that there are more ways available to show such records like embedded canvas app/custom page, PCF controls, and Html/Js WebResources, and all those solutions can be used but in this post, I didn’t want to pull up the “Big Developer Gun”. Unless, if you are aware of any other no-code/low-code ways to implement similar requirements – feel free to leave a comment under this post.
Cover Photo by John Schnobrich on Unsplash
You could also use nested grids no-code, but I’m not in love with the overall user experience.
Jason,
Thanks! That’s another way to show 2 level deep records!
Andrew
https://dianabirkelbach.wordpress.com/2022/10/01/fetch-related-records-with-power-apps-grid-customizer-control/
Your co-MVP Who we all love and adore wrote an article about this preview feature btw.
Thanks! I saw that post. Huge fan of Diana’s blog!
Andrew
Would it work if you add
as a filter? and tags sit at the same level of fetch xml, so setting a link with nested filter should not break xml structure. This, at least, work for similar filter customization in Power Portals.
I believe your Xml was not published. If you mean link-entity – that won’t work, unfortunately. At least I wasn’t able to make it work.
Yep, XML was cut