Posted: 22 Aug 2020 10:45 EDT Last activity: 24 Aug 2020 0:16 EDT
Calculate Sum in Pagelist
I am trying to calculate the sum of the property in a page list based on a condition. For example, I have 5 rows in a table, but only two of those rows meet the required condition. I would like to only calculate the sum of a property for those two rows. How can this be accomplished?
This can also be done declaratively using a Declare Expression so that the result is always synchronised with your source data, using a concept called the Clipboard Property Collection which is a syntax that gathers a scalar property value from every member of a page list.
Using the Product List from the UI Gallery as an example, by having a Page List property on my case auto-populated from the results of a Data Page ...
... I can have a separate Property with a Declare Expression that performs an Aggregate Function (in our case, "Sum of") over Prices (pyPrice) from the .AllProducts() page list using the Clipboard Property Collection syntax: .AllProducts().pyPrice. Use of Aggregate Functions also optionally captures an Expression - or a When rule - to apply a filter on which members of the Page List are included in the aggregation. It looks like this:
In addition to Sum, there are also aggregation options for Count, Average, Minimum and Maximum.
The When rule must be resolvable from the Applies To class of the Page List property. In this case, Data-UIGallery-ProductInfo. In our case it is a simple expression, but this could be much more complex depending on the application need:
So even though there are 25 results returned in the ProductInfo data page, only 4 are of type Hardcover:
... and despite the total sum of prices across the result set being much bigger, the Declare Expression correctly narrows this aggregation down to 15 + 15 + 9 + 9 = 48
Declare Expressions are preferable for this so that you don't need to attach rules (e.g. Data Transforms) to UI Action Sets or Flow Action pre/post processing every time the calculation needs to be re-performed. Changes to your Page List data will trigger recalculation of the Declare Expression either as the source data changes, or the next time the Declare Expression property is referenced.