3 Replies Latest reply on Jul 6, 2015 7:03 AM by JarlSilvén

    Many-to-many table structure - issue with conditionals when keys are the same.



      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.

        • 1. Re: Many-to-many table structure - issue with conditionals when keys are the same.

          If( Class::Subject = "Biology"; Average( Class::Score ); "" )

          Won't work for the simple reason that Class::Subject is a value from another related table with multiple related records. When a calculation refers to a field from a related table, it only accesses the value of the first related record and so you either get a an average of all related records in Class or nothing--neither of which is the value that you want.

          ExecuteSQL can calculate exactly the total that you want here.

          ExecuteSQL ( "Select Average ( \"Score\" ) FROM \"Class\" WHERE subject = 'Biology' )

          As a precaution, I put "Score" and "Class" inside double quotes on the off chance that either might be a reserved word in FileMaker SQL.

          There are several other ways that could be used to show such aggregate values--such as filtered one row portals using a summary field defined in Class.

          • 2. Re: Many-to-many table structure - issue with conditionals when keys are the same.

            Hm, but I used the average score as an example. Perhaps I should've used something more simple... Like this:






            Here I want to, from the original table, fetch the 'good' values only, through their originID. So an SQL Query would be: ExecuteSQL( " select value from othertable where test = 'y' and value = ? " ; ""; ""; origintable::originID )

            The problem is that the value is a calculation of something else.. and thus the string is received as a number, through ExecuteSQL. So that's why I was wondering if there was a way to work around it, and not use SQL..

            Edit: I should add that I've tried using If( othertable::test = "y"; List( othertable::value ); "" ), but then I got the complications I described in the example.

            • 3. Re: Many-to-many table structure - issue with conditionals when keys are the same.


              I just found out it was because the value calculation was set to numerical value as output...

              On the table it was fine and showed as strings as supposed to, but when selected by ExecuteSQL from the other table it showed the numerical value.

              For days this stupid mistake decieved me! I suppose ExecuteSQL is more useful than it might seem...

              Works as intended now. But thanks for giving me fresh ideas! :)