Many to many problem - im stuck

[Barry K]

I have a group of students in my class that are all working on different subjects. Each student has unique key id field as so and each student has a subject they are taking which is another field. What I am trying to do is see which subjects each student has not taken yet. So if the key id has records for (history and math) but not reading, I need to have a new field that tells me that I need to have the student take reading next. I bet this could be done via a script but I just do not have the FMP skills to build the script to do this. If possible I would like the script to be able to output as a spreadsheet the key id and the subject missing for each key id. 

I have attached the filemaker file as well as a screen shot of the problem. 

Any help would be great, thanks for reading this. 

 

students.fp7

Edited February 16, 2017 by Barry K

[comment]

Your title is misleading, because what you really have is a many-to-many relationship between Students and Subjects. And the table that you have is not a table of Students (where each student would have a unique record), but a table of enrollments where each record represents one student taking one subject.

To track this correctly, you should have three tables: Students, Subjects and a join table of Enrollments (which you already have).

 

54 minutes ago, Barry K said:

What I am trying to do is see which subjects each student has not taken yet.

In what form do you need to see this? You could show this in a portal placed on a layout of the Students table (which you would browse one student at a time). If you need to export this as a list that includes all students, then this will be more complicated, because you do not have a record for each subject a student has not taken - and it's difficult to export something you do not have.