Filter a sub-gallery table using parent gallery field

[kman42]

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!

[Drrickryp]

Hi @kman42 

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

[kman42]

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?

[Drrickryp]

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. 

  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]

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 · Accepted solution]

I got it. Forgot a parethesis. 🙂