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

 

 

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

 

 

View solution in original post

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 382 members 4,575 guests
Please welcome our newest community members: