cancel
Showing results for
Did you mean:
Highlighted
Level: Powered On

## Gallery Aggregation Question

Hi all,

I have a question about gallery aggregation by grouping. It's a bit of a complicated question, so I'll try my best to describe it in detail. I have a gallery populated from multiple different forms. Below is an example of a table of the records one might see:

Table #1

 Row Sales ID System ID Quantity ID Station 1 1000000 1100 1 of 2 Station #1 2 1000000 1100 2 of 2 Station #1 3 1000000 1200 1 of 1 Station #2 4 1000000 1300 1 of 1 Station #3 5 1000000 2100 1 of 1 Station #1 6 1000000 2200 1 of 1 Station #2 7 2000000 1100 1 of 2 Station #1 8 2000000 1100 2 of 2 Station #1 9 2000000 1200 1 of 1 Station #2 10 2000000 1300 1 of 1 Station #3

Each row is one unique component of an assembly of a sale. Let me try my best to describe this table column by column:

Sales ID

Each Sales ID corresponds to a purchase order by a customer. This is the overall filter by sale and these should all be grouped together.

e.g. Rows 1-6 are all apart of the same purchase. (Sales ID: 1000000)

System ID

A system is one entire assembly made up of different components. Sales can be multiple systems. The first digit of the System ID corresponds to a unique system within a sale. The second digit corresponds to which station it came from.

The reason that the station label and the system ID have their own columns is because the third and fourth digit of the System ID can differ from sale to sale.

e.g. Within Sale # 1000000, Rows 1-4 are all apart of one system since they have the same first digit in the System ID column. Rows 1-2 come from Station #1 are from the same station, whereas Row 3 and Row 4 are both from different stations respectively.  (System ID: [1100, 1200, 1300]).

Quantity ID

The number and item ID of components needed from specific station for one system.

e.g. Rows 1 and 2 are from the same station for the same system so that system needs two of those components. (Quantity ID: [1 of 2, 2 of 2])

Station #

This label is to identify which station the component is coming from.

e.g. Rows 1 and 2 are from the same station for the same system. (Station #: 1)

Ideally, I would like to group these results into a new gallery that is aggregated by sale and system. Below is a table of what I'd like to display:

Table #2

 ID Sale ID System Parent ID Component List 1 1000000 1000 Table 2 1000000 2000 Table 3 2000000 1000 Table

As you can see, I would like to group together system ID's for each unique sale. The parent ID is the first digit of the System ID in the original table multiplied by 1000. This would aggregate all the children ID to their parent. That way, in the component list which would be a popup acts basically as a bill of materials for what corresponds to the parent ID.

e.g. Row 1 of Table #2 will be an aggregate of all records with Sale ID 1000000 and Parent ID 1000. This corresponds to Rows 1-4 of the original table since the Sale ID is the same and the System ID falls under the parent ID. The component list would be a table referring to the four original components.

I'm not sure how to use GroupBy and Ungroup in this case, but that's what I feel like I need to do. I want to group together the system ID's by their parent, but still keep the Sale ID ungrouped so that if two sales have the same parent ID's, they won't be grouped together.

I realize it's a complicated question but I figure someone in this group would have some answers! Please let me know if you need more detail, I'm happy to screenshare, chat, etc. Please let me know!

Thanks,

Farhan

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Gallery Aggregation Question

Hi @farhandsome ,

Based on the needs that you mentioned, I think the GroupBy function,  AddColumns function and Collect function could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Add a Button in your app, set the OnSelect property to following:

``````Clear(Table2Col);
ForAll(
GroupBy(
AddColumns(Table1, "ParentID", Left(Text('System ID'),1) * 1000),
"Sales ID",
"ParentID",
"GroupData"
),
Collect(
Table2Col,
{
ID: CountRows(Table2Col) + 1,
'Sale ID': 'Sales ID',
'System Parent ID': ParentID,
'Component List': GroupData
}
)
)``````

Whne you press above button, the Table2 collection would be populated with proper records as that listed in the Table # 2 you mentioned.

You could consider add a Data Table control in your app, then set the Items property to the following:

``Table2Col``

then the available records would be listed there.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team

## Re: Gallery Aggregation Question

Hi @farhandsome ,

Based on the needs that you mentioned, I think the GroupBy function,  AddColumns function and Collect function could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Add a Button in your app, set the OnSelect property to following:

``````Clear(Table2Col);
ForAll(
GroupBy(
AddColumns(Table1, "ParentID", Left(Text('System ID'),1) * 1000),
"Sales ID",
"ParentID",
"GroupData"
),
Collect(
Table2Col,
{
ID: CountRows(Table2Col) + 1,
'Sale ID': 'Sales ID',
'System Parent ID': ParentID,
'Component List': GroupData
}
)
)``````

Whne you press above button, the Table2 collection would be populated with proper records as that listed in the Table # 2 you mentioned.

You could consider add a Data Table control in your app, then set the Items property to the following:

``Table2Col``

then the available records would be listed there.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Level: Powered On

## Re: Gallery Aggregation Question

It's almost working - for some reason, it doesn't recognize the System Parent ID in the collect function even though I added it as a column in the AddColumns() function.

Community Support Team

## Re: Gallery Aggregation Question

HI @farhandsome ,

I have made a test on my side, and don't have the issue that you mentioned. Please consider modify your formula as below (rename the column within your Collect formula):

Add a Button in your app, set the OnSelect property to following:

``````Clear(Table2Col);
ForAll(
GroupBy(
AddColumns(Table1, "ParentID", Left(Text('System ID'),1) * 1000),
"Sales ID",
"ParentID",
"GroupData"
),
Collect(
Table2Col,
{
ID: CountRows(Table2Col) + 1,
SaleID: 'Sales ID',
SystemParentID: ParentID,
ComponentList: GroupData
}
)
)``````

``````Clear(Table2Col);
ForAll(
GroupBy(
AddColumns(Table1, "System Parent ID", Left(Text('System ID'),1) * 1000),
"Sales ID",
"System Parent ID",
"GroupData"
),
Collect(
Table2Col,
{
ID: CountRows(Table2Col) + 1,
SaleID: 'Sales ID',
SystemParentID: 'System Parent ID',   // Reference the 'System Parent ID' column from above AddColumns formula result
ComponentList: GroupData
}
)
)``````

Please take a try with above solution, check if the issue is solved.

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Level: Powered On

## Re: Gallery Aggregation Question

@v-xida-msft  I see the issue now - I was using the wrong column name in the GroupBy function. Thanks for your solution again! You've made my life a lot easier.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

#### Power Platform World Tour

Find out where you can attend!

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,701)