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:
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.
Solved! Go to Solution.
Create a collection by the GroupBy() on the foreign key in the Projects table, so for example
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.
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.
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?
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us on June 28 for our monthly User Group leader call!
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.