cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sander0123
Level: Powered On

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
h-nagao
Level 10

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

11 REPLIES 11
mogulman
Level 8

Re: Aggregation nested collection

What is your datasource?

Sander0123
Level: Powered On

Re: Aggregation nested collection

It's a collection created from Json API response.
mogulman
Level 8

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.

Sander0123
Level: Powered On

Re: Aggregation nested collection

I understand but it's not Smiley Happy. It's a Woocommerce API. Do you know how to achieve this in powerapps?
mogulman
Level 8

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.

h-nagao
Level 10

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.
h-nagao
Level 10

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 , TotalQuantitySmiley Frustratedum(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.  TotalQuantitySmiley Frustratedum(Filter(ORDERS,NAME=Result),QUANTITY) returns total amount of related QUANTITY field

 

Thanks.

Sander0123
Level: Powered On

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

h-nagao
Level 10

Re: Aggregation nested collection

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