Compare portal contents in Filemaker Pro

The situation: I have two tables in a database. One table resembles a list of students and the classes they are taking (plus other data about that student). So the table might contain five records for Joe in math, in English, etc., for each name-class combination. The second table resembles a list of teachers and - in this world - the exactly two classes that each teacher teaches.

I have two portals, one each to show all the names of the students in each class for that particular teacher record.

The task: I need to find cases where one student is in both classes taught by one teacher. That is, they appear in the list of students in both portals.

Is there a way to compare the contents of two portals or otherwise identify the students taking two classes from the same teacher? The portals are nice for displaying the lists but I don't have to anchor a solution to them. Maybe there's a way to set up the relationships to find those students? I'd prefer to avoid a script solution but certainly that's OK if there isn't a more elegant approach.

FM Pro 18 Advanced, macOS 13.2.1

Solution

In order to establish a common vocabulary, consider the following structure:

Now, assuming there are 2 portals on the layout of Teachers, one to each occurrence of the Enrollments table, and each containing the Student field from the corresponding occurrence:

You can apply conditional formatting to the Enrollments::Student field using the following formula:

not IsEmpty ( FilterValues ( Self ; List ( Enrollments 2::Student ) ) )

and (if you want) apply conditional formatting to the Enrollments 2::Student field using:

not IsEmpty ( FilterValues ( Self ; List ( Enrollments::Student ) ) )

With this arrangement in place, any student that appears in both portals will be highlighted.

Note that this structure has some serious flaws - most notably:

a teacher cannot teach more than two courses;
in order to change a student's name, you need to change it in all their enrollments, thus exposing yourself to update anomaly. In a relational database, names (and any other meaningful data) should be stored only once.