Nested rollup fields

(0) Share

Report

Posted on by KoJaPD

601

Hi,

I have the following related entities:

[ProductFromSupplier]-->[Product]-->[ProductForProject]-->[Project]

each with a one to many relation.

I wish to achieve the following rollup fields in order to calculate the total cost for products assigned to projects:

a [ProductFromSupplier] has the currency field [Price] in which a static amount is entered.
a [Product] has a field [AveragePrice] which should hold the (rollup) calculation of the average price of all related [ProductFromSupplier]
a [ProductForProject] has a field [quantity] and a (rollup) field [TotalPrice] which should be [Product.AveragePrice]*[Quantity]
a [Project] has a (rollup) field [TotalProjectPrice] which should be the Sum of all [ProductForProject.TotalPrice]

I have difficulties on setting this nested rollup calculation: it seems that I cannot create a rollup field of another rollup field, but then, how can I achieve this anyhow?

Thanks,

Koen

Categories:

Microsoft Dataverse with Power Apps

All responses (7)

Answers (1)

Sort by

Joel CustomerEffective 3,224 on at

Like
a
(0)

Report
Copy link

Link copied!
Koen,

Here are the general rules of rollup fields and calculated fields:
rollup fields can roll up calculated fields that don't reference other calculated fields
Rollup fields cannot roll up other rollup fields
With that in mind, rollup and calculated fields likely are not the best fit for what you want to do. Also rollup fields only roll up every 12 hours, so IMO they are of limited use for decision making for anything but slowly changing values.

Look to use scheduled Power Automate jobs or CDS plugins for these more advanced calculation and rollup purposes.
KoJaPD 601 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jlindstrom , thanks for your answer. As you suggested, I started to create a scheduled flow that would calculate the average price for each [Product] as the average of all related [ProductFromSupplier.Price].

I have created a flow that Lists all records of [Product]; then I added a Apply To Each task in which I want to set the [Product.AveragePrice] to something like "Average of (Related.ProductFromSupplier).Price"; how do I get this average? Should I use an additional action "List Records" to get all ProductFromSupplier with a filter where ProductFromSupplier.Product = Product, or is there another way?

Thanks,
Koen
Verified answer

Joel CustomerEffective 3,224 on at

Like
a
(1)

Report
Copy link

Link copied!

The way to do calculations is to create a variable and list records then loop through the list incrementing the variable

see https://youtu.be/7sCF2pg8q5I
so you would need a variable for the total, then you can get the total number using a length expression in a compose field

then do another compose dividing your sum variable by the total number to get your average
KoJaPD 601 on at

Like
a
(0)

Report
Copy link

Link copied!

@jlindstrom Thanks, I would never have found that!
I can hardly believe that (like also mentioned in the video, and being the year 2020), I still have to manually fetch all the related items, loop through them, sum up all the prices and divide by the number of records... I thought there would be an easy way to get an average of a related dataset... Anyway, I will do it like that for now, but I hope that in the near future a more common way of doing calculations with datasets is coming...
Joel CustomerEffective 3,224 on at

Like
a
(1)

Report
Copy link

Link copied!

Also if you don’t get all records in your sum change the pagination settings on list records
KoJaPD 601 on at

Like
a
(0)

Report
Copy link

Link copied!
@jlindstrom Does Microsoft thinks getting an average of related items is rocket science?
Why isn't there an equivalent of the SQL way:

SELECT AVG(price), otherId FROM Products GROUP BY otherId;

Now I have to define 4 variables (tempSum, Sum, Counter, Average) and loop over all products manually (also taking care of dividing by zero for the average when no related products are found), giving this HUGE flow...
Joel CustomerEffective 3,224 on at

Like
a
(0)

Report
Copy link

Link copied!

actually I have another suggestion. I haven't tried this yet, but it should work.

create your flow in a solution
use the common data service (current environment) connector
List records supports fetch
use an aggregate fetch query https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/use-fetchxml-aggregation
Then you can get the aggregate values from list record

so you can get aggregate list records results