cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Aggregation nested collection

Hi, 

 

I'm trugeling with a function for a few hours now and I'm hoping someone out there can help me out.

 

I've a collection ORDERS with a nested table LINE_ITEMS that contains a field NAME (product name) and a field QUANTITY. My goal is to sumarise the the quantities per unique product name in a collection.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Nested collection can be expanded by Ungroup function, so you can get summary table (collection) containing unique product name and total quantity by

 

ForAll(Distinct(Ungroup(ORDERS,"LINE_ITEMS"),NAME),Collect(SummaryList,{ProductName:Result , TotalQuantity: Sum(Filter(Ungroup(ORDERS,"LINE_ITEMS"),NAME=Result),QUANTITY)}))

 

Just in case, below is screenshot of collections in my test app:

summarycollection.png

View solution in original post

11 REPLIES 11
Highlighted
Skilled Sharer
Skilled Sharer

What is your datasource?

Highlighted

It's a collection created from Json API response.
Highlighted

If your datasource is a database like SQL Server you could create a view and do the aggregation on the server.  Much simpler and more efficient.

Highlighted

I understand but it's not :). It's a Woocommerce API. Do you know how to achieve this in powerapps?
Highlighted

I don't have any direct experience with this but you obviously need to use the GroupBy function and the Sum function.  You may need to use the UnGroup function to make it a flat table first then do the GroupBy.  Sorry, I can't be of more help.

Highlighted
Impactful Individual
Impactful Individual

Not complete solution for your question, but I think this would be achieved by using Distinct(ORDERS,NAME) and SUM(Filter(ORDERS, ORDERS.NAME = <distinct result>).QUANTITY)

Thanks.
Highlighted
Impactful Individual
Impactful Individual

I have found the solution for your question.

On any triggers (OnSelect of Button or OnStart of Screen), you set

 

ForAll(Distinct(ORDERS,NAME),Collect(SummaryList,{ProductName:Result , TotalQuantity:Sum(Filter(ORDERS,NAME=Result),QUANTITY)}))

 

1. Distinct(ORDERS,NAME) returns list of NAMEs removing duplication, "Result" is NAME field

2.  ForAll(XXXX, Collect(XXXXX)) create rows for each NAMEs

3.  TotalQuantity:Sum(Filter(ORDERS,NAME=Result),QUANTITY) returns total amount of related QUANTITY field

 

Thanks.

Highlighted

Thanks but unfortunately this will not work since it's a nested tables. To clarrify below a screen shor of the orders table with column line_items that contains the tables containing the columns name and quantity. 

Line_items.JPG

Highlighted
Impactful Individual
Impactful Individual

Ah, sorry for my misunderstanding, and thanks to clarify your situation.
I will try again.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,301)