Many-to-many table structure - issue with conditionals when keys are the same.
Greetings all, first post on this forum.
To illustrate the problem at hand, let's use the student-teacher example, where a table named Class is used to connect the teachers to the students, and contains the fields TeacherID, StudentID, Subject and Feedback.
Now, imagine we'd want to create a field in Teacher that process student Feedback into some kind of Score in the Teacher table. It would perhaps take numerical values from Feedback and make an average value.
But wait! A teacher can teach several subjects! So Score would perhaps warrant separate fields in Teacher - perhaps BiologyScore or MathScore - that can be empty if a particular teacher don't teach that subject.
So, each [Subject]Score field in the Teacher table would have a calculation function looking something like this:
If( Class::Subject = "Biology"; Average( Class::Score ); "" )
This works as it should, except for the ' Class::Subject = "Biology" ' part. The problem that arise is that in the Class table, the TeacherID field isn't unique, and many records have the same TeacherID. Out of those, the aforementioned part of the average calculation only checks THE FIRST of all records containing the TeacherID, meaning that if the first record does not contain "Biology" then the BiologyScore cell will be null for that teacher, even if other records with a Biology score exists. And also the other way around, where "Biology" happens to be the first, ALL the records following the first will contribute to BiologyScore, even though they're of different subjects.
I have little experience in FileMaker, but in SQL, though it was a while ago, this would be easy. It goes something like: SELECT Score FROM Class WHERE Subject = 'Biology' AND TeacherID = ?. I've tried using ExecuteSQL, but when you desire strings from another calculated field it simply finds the number representing said string.
Solutions to this problem would be: 1. Is there a way to revert the string back from its number? Then I would be able to use the ExecuteSQL easily for my purpose. 2. Is there some simple way, that noobs like me don't know of, to use conditions on function lists? 3. Are these relations not optimal, that I should structure it otherwise, solving this in a simple fashion?
Help would be much appreciated.