cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Nested rollup fields

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Nested rollup fields

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

View solution in original post

7 REPLIES 7
Highlighted

Re: Nested rollup fields

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.

Highlighted
Helper I
Helper I

Re: Nested rollup fields

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

Highlighted

Re: Nested rollup fields

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

View solution in original post

Highlighted
Helper I
Helper I

Re: Nested rollup fields

@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...

Highlighted

Re: Nested rollup fields

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

Highlighted
Helper I
Helper I

Re: Nested rollup fields

@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...

image.png

Highlighted

Re: Nested rollup fields

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (8,361)