cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sander0123
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
h-nagao 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
mogulman Skilled Sharer
Skilled Sharer

Re: Aggregation nested collection

What is your datasource?

Sander0123
Frequent Visitor

Re: Aggregation nested collection

It's a collection created from Json API response.
mogulman 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.

Sander0123
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?
mogulman 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.

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

Sander0123
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

h-nagao 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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (5,749)