cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kman42
Level: Powered On

Filter a sub-gallery table using parent gallery field

I have a gallery based on a list of people from person_table. I have a sub-gallery inside of that using data from another table (projects_table) that I want to filter by a field (person_code) in the parent gallery, but I can't get it to work.

 

For the sub-gallery, I have Items set to: 

Filter ('[DB].[projects_table]', project_person_code=ThisItem.person_code)

 

I'm then trying to count the items in projects_table that are active (project_status="A") and display in a label. The label Text is set to:

 

CountIf('[DB].[projects_table].project_no,project_status="A")

I get wonky results though, where the count resolves to either '500' or '0' for the different rows, but with no rhyme or reason that I can figure out. Any help would be appreciated.

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
kman42
Level: Powered On

Re: Filter a sub-gallery table using parent gallery field

I got it. Forgot a parethesis. 🙂

 

 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Filter a sub-gallery table using parent gallery field

Hi @kman42 

Have you considered using the GroupBy() function to address your issue? /function-groupby

kman42
Level: Powered On

Re: Filter a sub-gallery table using parent gallery field

That seems like part of the solution, but how would I use GroupBy to get information from two separate tables? Would I somehow use the function in the data source of the top level gallery?
Super User
Super User

Re: Filter a sub-gallery table using parent gallery field

Hi @kman42 

Create a collection by the GroupBy() on the foreign key in the Projects table, so for example

Collect(colProjects,GroupBy('[DB].[projects_table]',"PersonID","RestofData")

This creates a collection of two related tables, one with the foreign key (PersonID) as its only column and a second table with the rest of the data that will contain the project details.

Create a Gallery with colProjects as the Items property.  Insert a label and it will show the PersonID.  Add a second label with the Text property:  Lookup(Person_Code,ID=PersonID,PersonName). (you can use this format to add additional labels from your Person_Code table if you wish by replaceing PersonName with other fields) using Lookup() to fill out the rest of the data from the Parent table in the outer gallery.

Add a subgallery inside your first gallery and resize it in your gallery.  I like to use a light color fill for this gallery so that it is easy to see and work with. Make the Items property of this gallery: RestofData and add labels for the columns in the Projects table. 

 Screenshot_2.png In my example, I used the Orders table and created the collection with the command inside of the button to extract the Company ID. I used that collection as the Items property of the outer gallery. The number in the upper left of the gallery is the company ID.   I used a lookup to show the company name.  The restofdata table was used as the Items property of the subgallery.  The data included is the number of items shipped and shipping date/time of the order. You should be able to do something similar with your two tables. 

Tip: Make sure that you use Flexible height galleries for both outer and subgallery to account for the different number of items in the subgallery.

 

kman42
Level: Powered On

Re: Filter a sub-gallery table using parent gallery field

I feel like I'm close, but I still can't get it to work. I followed your lead and put the Collect function as you described in OnSelect for a navigation button. It gives me an error saying that the name colProjects isn't recognized and it doesn't do anything when I press the navigation button. Do I have to create the collection first somehow so it recognizes it?

kman42
Level: Powered On

Re: Filter a sub-gallery table using parent gallery field

I got it. Forgot a parethesis. 🙂

 

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 315 members 5,607 guests
Please welcome our newest community members: