5 Replies Latest reply on Dec 14, 2012 12:10 PM by philmodjunk

    Incorporating Related Fields into a Calculation

    keebrook

      Title

      Incorporating Related Fields into a Calculation

      Post

           I am wondering if it is possible to create a Calculation (or script, or conditional formatting...) that will notify me if related data meets certain criteria.

           I have a "Students" table and a "Classes" table, joined by a "Student-to-Classes" table. I have a portal on the Classes page that lists all the Students registered for that class. What I would like is a notification to appear within that portal next to a student's name if they meet certain criteria. (I could settle for a notification on the Student's page as well.)

      Criteria: A student must complete courses 1, 2, 3, 4, 5, & 6 OR courses 1, 2, 3, 4, 6, & 7 OR courses 1, 2, 3, 4, 6, & 8 OR courses 9, 6, 7, & 5 OR courses 9, 6, 7, & 8 in order to qualify for an Award.

           When I register a student for the last class that fulfills one of the above criteria, I would like a notification to appear telling me to issue an Award. (once the award has been issued, I will mark a check box and make the notification disappear)

           I have fiddled around with creating calculations in all three tables with a variety of "If" and "Case" statements but nothing seems to work. Is this even possible?

           I am using Filemaker Pro 2011 on Mac. Thanks!!

        • 1. Re: Incorporating Related Fields into a Calculation
          philmodjunk

               Now that's a challenge! Since you have to use the student and course ID's to look for entire sets of records, this won't be a simple thing to set up.

               First you'll need to set up a way to document which set's of course ID's represent those required for an award. I can think of two approaches: 1) a return separated list of Course ID's in a single file, or 2) a set of related records with one course ID stored in each related record. Not sure yet which approach will be the better option here...

               Then, you'll need to add a table related table that documents which awards have already been awarded to a given student.

               Key question, could a student register to take the same course more than once?

                

          • 2. Re: Incorporating Related Fields into a Calculation
            keebrook

                 A student could register for the same course multiple times.

                 I'm going to try to work with those ideas and see what happens. Let me know if you have any more ideas. Thanks!

            • 3. Re: Incorporating Related Fields into a Calculation
              philmodjunk

                   The fact that (as I expected) a student can register multiple times for the same course greatly complicates this.

                   I had been thinking at first of using a Return Separated list of IDs as  relationship match key such that the count of related registration records would tell you if an award is justified due to the unique way that a return separated list of values functions as a "match" field. But this will not work given the fact that this method will count all the times that a student has registered for the same course.  If a student needed to take courses 1, 2 and 3, taking course 2 three times would count the same as taking each course once.

                   I think the first challenge will be to produce a set of related records that lists each course taken only once. It's a pity that you aren't using FileMaker 12 as I can conceive of a SQL query used in ExecuteSQL that might be just the ticket for resolving this one.On the other hand, there may be a way, using a join table...

                   I don't have time to really do much testing to figure one out at the moment, I'm neck deep in a database project of my own. Perhaps another forum participant will find that this is a challenge worth taking?

                   and one more question:

                   

              Criteria: A student must complete courses 1, 2, 3, 4, 5, & 6 OR courses 1, 2, 3, 4, 6, & 7 OR courses 1, 2, 3, 4, 6, & 8 OR courses 9, 6, 7, & 5 OR courses 9, 6, 7, & 8 in order to qualify for an Award.

                   Does this describe 1 single award or 5 different awards?

                    

              • 4. Re: Incorporating Related Fields into a Calculation
                keebrook

                     All of those options qualify for one award.

                     I completely understand being too busy for a tiny feature like this. Thank you for your help! I might wind up settling for a warning when a student is "one class away" from getting an Award (surprisingly that is a lot easier to do since I can use post-class exam scores and not course regitrations) ... It's not a perfect solution, but it's better than nothing for now.

                     Though if anyone else knows a simplier way to do this, I welcome all other ideas!

                • 5. Re: Incorporating Related Fields into a Calculation
                  philmodjunk

                       If this were "little" I'd post a quick solution. It's anything but.

                       I had an idea. This isn't the full solution, but may point you in the right direction.

                       You have these relationships:

                       Students----<Student_Classes>-----Classes

                       From students, List ( Classes::ClassID ) returns a list of ClassIDs for which this student has registered.

                       If you had a table of records that lists the courses required for an award (we'll leave out the fact that there are multiple lists for the same award for now), linked to students, then

                       ValueCount ( FilterValues ( List ( Classes::ClassID ) ; List ( Award1::ClassID ) ) )

                       could be used to determine if a student has registered for the correct list of classes to qualify for that award.