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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,126)