Unanswered

Aggregate rollup field over related table field of related table

(0) Share

Report

Posted on by JurSap82

How can I aggregate a rollup field over a related table field of a related table?

Instead of a many-to-many relation, I chose to use a "Junction" table in between my Order table and Machine table. The Junction table has a lookup to each of the other two tables. The Machine table contains a Price column of type currency. How can I aggregate my rollup column named Total Price (also of type currency) over the Price column in the Machine table?

When editing the rollup field and aggregating the Junction table, it seems I can only acces the lookup field to the Machine table, but I can't go further to acces the columns of the Machine table. Dot notation doesn't work.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

steeevid 323 Super User 2024 Season 1 on at

Like (1)

Report

roll up field will be retired soon it's a complete garbage i wouldnt suggest using it, just use power automate with fetchXML or expanded quary, write a SQL to achieve your logic and translate it into fetchXML, and then use it in power automate.

below is an example:

in my example, purchase invoice table is a child table of the project table.

no matter how many layer of the 2 tables have, all you need to do is to find the filter condition that is able to retrive the related records you want. For example in your case, you might want to use a get row by ID to lookup the related junction table, get the lookup ID to the other table, and then fetchXML with condition lookup value = lookup GUID of the junction table

Or if you familar with expanded quary, you can write that in single action

Was this reply helpful? Yes No
JurSap82 on at

Like (0)

Report

Thank you @Stevieed.
I was able to make it work with a fetchXML query.

Was this reply helpful? Yes No