Filter values that are previously added to the ref field.

[TheGreeeen]

Hi all. I'm very new to AppSheet and I need some help.  I found some similar answers, but it's not what I'm looking for.

I have 3 tables (Students, Modules and Enroll) that I use to enroll Students in to Modules. This is the relation between them:

Students -> Enroll <- Modules

The problem I have is when entering data (enrolling the students into modules) it shows all the students and it's easy to make a mistake and enroll the same student in the same module. What I want is to filter the list of students, so if the student is added to that specific module not to show him in the list, or vice-versa.

Basically, when I use the form to enter data and select a specific module from the drop-down,  I want to exclude the students previously enrolled to that module from the student list. And vice versa, when I select a student I want to filter only the modules he is not enrolled.  

Also, if possible, I want to filter the modules list based on the course the student is enrolled. I have a Courses table that is connected to Students and Modules table.

Thank you in advance.

[Suvrutt_Gurjar]

The general approach will be to subtract the list of students enrolled for the same module ID from the list of all students, something like

{All Students list}- {List of Students for the current module}

You can use this expression in the valid_if of the reference Student column in the Module table.

List expressions - AppSheet Help

Subtract values from a list - AppSheet Help

Check form input validity (Valid_If) - AppSheet Help

[TheGreeeen]

Thanks for the replay, but I'm not directly referencing the Students table from the Module table, the reference goes through the Enroll table. So, there is not a Student column in the Module table.

[Suvrutt_Gurjar]

Okay, got it. Thank you for the update. The general principle will still remain the same. if you wish to have specific expression level assistance from the community, please share the relationship between tables by sharing the screenshots of relevant columns of the three tables , so that referencing relationship is clear and which column you would like to have the expression.

[TheGreeeen]

Sorry about that, here are the screenshots:

[Suvrutt_Gurjar · Accepted solution]

Thank you.

Please try in valid_if of the [Students] column in the Enroll table an expression of 

Students[ID] -SELECT(Enroll[Students],[Module]=[_THISROW].[Module])

You could possibly have suitable slices on the Enroll table such as Future_Enrolls with an expression something like [Date]>=Today() because I think you will enroll students only for future modules. This will reduce burden on SELECT() expressions.

Note: If you are looking for help with the [_THISROW] concept, please take a look at the following informative tip by @MultiTech 

When to use [_thisrow] - Google Cloud Community

[TheGreeeen]

Thank you, it works great. I manage to add the expression (replace Students for Module) for the Module column, and it works great.

I just don't understand how slices work, so I'll try to read more about that. 

Thanks again and all the best.

[AleksiAlkio]

A sidenote.. when using this approach with the Valid_If, you need to add the existing value to it if there is a chance someone edit the Enroll later. Otherwise it won't save the updated record.

[TheGreeeen]

Thanks for the info, I can see now, no more edits possible and I need to add grades later.

So, is there another approach I can use?

[AleksiAlkio]

No. You just need to add the existing value to your validation, for example something like + LIST(LOOKUP([_THISROW].[ID],Enroll,ID,Students))

[TheGreeeen]

Great, it works. Thank you for the formula.