cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
farhandsome
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

RowSales IDSystem IDQuantity IDStation
1100000011001 of 2Station #1
2100000011002 of 2Station #1
3100000012001 of 1Station #2
4100000013001 of 1Station #3
5100000021001 of 1Station #1
6100000022001 of 1Station #2
7200000011001 of 2Station #1
8200000011002 of 2Station #1
9200000012001 of 1Station #2
10200000013001 of 1Station #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

IDSale IDSystem Parent IDComponent List
110000001000Table
210000002000Table
320000001000Table

 

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

View solution in original post

4 REPLIES 4
Community Support Team
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.

View solution in original post

farhandsome
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
Community Support Team

Re: Gallery Aggregation Question

HI @farhandsome ,

Could you please show more details about the issue within your formula?

 

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
               }
       )
)

 

If you add a "System Parent ID" column within your AddColumns formula, please modify your formula as below:

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.
farhandsome
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. 

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

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

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

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)