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

Re: Aggregation nested collection

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

Re: Aggregation nested collection

What is your datasource?

Highlighted
Frequent Visitor

Re: Aggregation nested collection

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

Re: Aggregation nested collection

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
Frequent Visitor

Re: Aggregation nested collection

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

Re: Aggregation nested collection

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

Re: Aggregation nested collection

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

Re: Aggregation nested collection

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
Frequent Visitor

Re: Aggregation nested collection

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

Re: Aggregation nested collection

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!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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