Show a list of each related item of all related items in a model driven app

(0) Share

Report

Posted on by KoJaPD

601

Dear,

I have 3 entities A, B and C with the following relationships:

[A] 1 --> many [B] many <-- 1 [C]

How do I show a list of all [C] records on a form for record [A] (through [B])?

Thanks,

Koen

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (8)

Answers (0)

a33ik 3,306 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hello,
Back in 2011 there was approach called "Deep Queries" - https://cloudblogs.microsoft.com/dynamics365/no-audience/2012/04/16/deep-queries-for-subgrids/
I'm not sure if it works now.
Other than that - I don't recall any OOB options. Obviously, you can build something to implement your requirement.

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (1)

Report
Copy link

Link copied!

If the above relationships are correct (1 to many A->B and many to 1 B->C) then C is an attrib of B, so you can just add C (the column) to the view of B, as well as any other attribs you like off C, then show it on A in a subgrid of B.

Example:
On an Account, I have many contacts, each contact has one Primary Address. I want to show all Primary Addresses on the Account. I create a view of Contacts that shows their primary address column and any attribs off Primary Address I need, such as City, State, Zip. Then on the Account form , I add a subgrid of contacts and select the view I just created.

Was this reply helpful? Yes No
KoJaPD 601 on at

Like (0)

Report
Copy link

Link copied!

Hi @cchannon , you are so right, I just didn't see it that way... One additional challenge however, I would like to see a distinct list 🙂 because in your example if a lot of primary addresses are the same amongst contacts I would see them as duplicates...

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

OK, well in that case there isn't a filter that will get you a distinct list, but you can still accomplish this by just manipulating the relationships as they are updated.

I would create an additional N:N relationship between A and C, then create a PowerAutomate Flow that fires on update of B when column C changes. In that flow, retrieve A (easy, because it is a lookup field on B) then retrieve all C vals on B records related to A. Then update the relationship on A to point to those C vals.

Presto! you have a direct relationship to C now that PowerAutomate can maintain for you.

Was this reply helpful? Yes No
Diana Birkelbach 3,072 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @KoenJanssensPD ,

If I understand right , you need a fetch with distinct, where you select only attributed from C. Something like this (or similar, given the model):

<fetch distinct="true" > <entity name="A" > <link-entity B... intersect="true" > <link-entity C > <attribute name="name" /> </link-entity> </link-entity> </entity> </fetch>

Hope it helps!

Kind regards,

Diana

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

Does the distinct operator work like that in fetch? I must confess, I have never tried but I assumed that it would give you a distinct collection of A with attributes from C, not a distinct collection of A⋅C

Was this reply helpful? Yes No
Diana Birkelbach 3,072 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @cchannon ,

FetchXMl distinct works exactly like SQL. It applies on the attributes you select. So if you select only attributes from C, will apply to that.

Kind regards,

Diana

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

TIL

Was this reply helpful? Yes No