Aggregation nested collection

[Sander0123]

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!

[mogulman]

What is your datasource?

[Sander0123]

It's a collection created from Json API response.

[mogulman]

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]

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

[mogulman]

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]

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]

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.

[Sander0123]

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. 

[h-nagao]

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