Filter CONTAINS on connected values doesn't work

School enrollment database. Records in Students table get linked to one or more records in the Classes table, and the names of the Class records always have two parts: a year value and a class name. So a list in Students might look something like this

Student ClassAssignments
William 2021 French, 2022 English
Joan 2022 French
Anne 2020 English, 2021 Biology

I want users to be able to filter on the ClassAssignments column in order to find all the students enrolled in a class for a given year. Seems to me that a filter that uses this formula should do the trick:

ClassAssignments CONTAINS '2021'

And that ought to return this list:

Student ClassAssignments
William 2021 French, 2022 English
Anne 2020 English, 2021 Biology

That is, removing Joan, because she didn’t take any classes in 2021.

But it does not work! I can select the CONTAINS operator in the filter definition, but I’m not allowed to select anything except a complete value.

So the only way to find everything in a given year is to create a complex filter like this:

ClassAssignments CONTAINS '2021 French' OR
ClassAssignments CONTAINS '2021 Biology'

Is that right? Is it a bug? It feels like a bug. Am I missing something or doing something wrong?

Hello William!
I understand that the filter would work as you expect if you were filtering Classes in a Class table, because the name of the Class is a text field.
But you are filtering Students in a Student table. The Class is an object connected to the Student, that is why the “contains” filter asks you to pick values. It doesn’t let you type because it’s not a text field.

It would be very practical for it to work as you describe!

I’m thinking of a workaround

I think the best would be to model this problem some other way… If each Class had a subject field, and a Year field we may be able to think of a better way for displaying results?

Thanks for the reply. Not the answer I was hoping for but it’s really helpful to have you confirm that I’m not missing anything. Rolling this v0.9 app out to client tomorrow and then going on vacay for a bit and so I’m rushing to get it WORKING at least.

What I’ve done in the meantime is something like the workaround you suggested: There are now TWO fields:

  • YearsEnrolled
  • ClassAssignments

YearsEnrolled now is a text field that contains JUST the year. Like this:

Student YearsEnrolled ClassAssignments
William 2021, 2022 2021 French, 2022 English
Anne 2020, 2021 2020 English, 2021 Biology

I populated the YearsEnrolled field in FileMaker (which I’ve been using to clean the data up) and imported into the database. So it’s now possible for the users to filter on THAT field. The current “year-enrolled” = 2021 (i.e. this value always starts in the fall) so users can filter on YearsEnrolled = 2021 and see current enrollments only.

.

This solves one big problem, but creates two new ones:

  1. I have to require users to enter both a YearEnrolled value and also a ClassAssigned value.
  2. And obviously, since these are separate fields, I’ve now introduced the possibility that mistakes will occur, that is, that somebody will forget to enter the value, or will enter the wrong year or something like that.

So it’s a kludge and probably temporary. But it works fine for the time being. And I will deal with these problems later. Fortunately, it’s a school database, and the spring term just ended, so I now have months to come up with a better solution. :blush:

Thanks again.

1 Like