Unanswered

Filtering results if records are in another table

(0) Share

Report

Posted on by mrwilliams

I have a list of pupil assessment results being displayed in a gallery with the source data being a collection. This list is essentially a whole year group of pupils.

I want to add add a drop down to filter the pupils for only the pupils who belong to certain pupil groups. I have called this GroupSelection

I have a Group table, with the following entries which is it's datasource:

Group Name

SEN

EAL

Chorister

I also have a 'Group Member' table which records who is in each group referencing the Group Name as a lookup field:

Pupil Name (lookup to the pupil table)	Group Name (lookup to the Group table)
Bloggs, Joe	SEN
King, Bob	SEN
Scroggins, Jimmy	Chorister
Singalot, Gavin	Chorister
Outoftune, Bob	Chorister
Puddleduck, Jemima	EAL

To show the current yeargoup in the gallery, on the items property I am currently using:

Filter(ResultsTable,yeargroup=yeargroupSelection.selected.text)

So from my year group list of pupils - which may contain 60 entries, I only want to show the records of the pupils in that year group who are in the selected pupil group (SEN, EAL or Chorister). How can I do this?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

BCBuizer 22,819 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @mrwilliams ,

For this you can add additional conditions to your filter, for instance:

Filter( ResultsTable, yeargroup=yeargroupSelection.selected.text && (IsBlank(GroupNameSelection.Selected.Text) || 'Group Name'.Value = GroupNameSelection.Selected.Text) )

In the above, GroupNameSelection.Selected.Text refers to the control where you select the group name, so you will need to update that.

Was this reply helpful? Yes No
mrwilliams 50 on at

Like (0)

Report
Copy link

Link copied!

Thanks @BCBuizer - but I can't see how does any kind of look up to see whether a pupil is in one of the groups. The dropdown control showing SEN, EAL or Chorister is just a list of groups, not who is in the groups. The 'Group Member' table is the table holding the records of which children are in each group. One child for instance could be in two groups, SEN and Chorister.

So there are three tables:
ResultsTable: One pupil record
Group: The list of Groups
Group Member: Holds which pupil is in which group.

Was this reply helpful? Yes No
BCBuizer 22,819 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @mrwilliams ,

It's not exactly clear to me what you are trying to achieve.

Am I correct when I say you wish to retrieve all items from the Pupil table, filtered by the selected group in the SelectGroup control?

If so, the formula looks something like this:

Filter( Pupils, Name in Filter( 'Group Members', 'Group Name'.Value = GroupSelect.Selected.Text ).'Pupil Name'.Value )

Was this reply helpful? Yes No
mrwilliams 50 on at

Like (1)

Report
Copy link

Link copied!

Thanks @BCBuizer - that is the format which makes sense to me for what I'm trying to do. I want to filter my results table for a particular year group assessment, and then filter it down further based on what children are in the selected group.
I've tried using what you've done but I get an error on the second filter where I am trying to say "Filter where the pupil name is in the list of pupils in the Group Member table where the group is equal to the selected group. The error I get says: Invalid scheme, expected one column table.

Feel I am getting closer but not quite there....

Was this reply helpful? Yes No
BCBuizer 22,819 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @mrwilliams ,

Indeed you are very close. Just add .Value after the closing brace for the nested Filter function to select the column from the 'Group Members' table that matches the FullName column in the 'Combined Data' table. This will turn the table, returned by the nested Filter into a single column table which matches the expected schema.

Was this reply helpful? Yes No
mrwilliams 50 on at

Like (0)

Report
Copy link

Link copied!

Ok @BCBuizer - I have done this and then the new error....Here, Fullname is the column in the 'Group Members' table which holds the lookup to the pupil table - which is the same value of the 'Combined Data' 'Pupil Name' column...which again is a look up to the pupil table.

I've spent about 7 hours today on this so going cross-eyed! It is literally the last major thing I need to do to unlock the whole app!

Was this reply helpful? Yes No
BCBuizer 22,819 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @mrwilliams ,

Since the 'Pupil Name' column in the 'Group Members' table is a LookUp type, there are typically only two columns to select: Value and Id. The Value column has the value of the column in the parent table which is is connected to (the 'Pupil Name' column in the 'Combined Data' table) whereas the Id column has the ID of the record in the 'Combined Data' table. A thing to know about these is that the use of the Value column IS delegable, but the Id column isn't. However, since you will be using the in operator, which isn't delegable, that last part isn't all that relevant, so you may try to change your formula a bit to:

Filter( 'Combined Data', 'Assessment Name'.'Assessment Name' = "23/24 - Aut Y6", ID in Filter( 'Group Members', 'Group Name'.'Group Name' = GroupSelect.Selected.'Group Name' ).Id )

Ps. So far I was assuming you are using SharePoint as a data source. Can you please confirm?

Was this reply helpful? Yes No