1 Reply Latest reply on Jan 4, 2012 1:19 PM by philmodjunk

    How can I calculate a related field with related table?

    nazacer

      Title

      How can I calculate a related field with related table?

      Post

      I am making a student attedance project. And Time attedance devices software is using SQL database. I related time records with filemaker database. ID is related field. I planed lessons date, and time an other table. When I choose a student I can see planed lessons and time records. I want to check time records and if student was in lesson it have to fill the status field "Checkin", if student was not in lesson it have to set "Not Come" Here is the related table diagram Main Table; "Create Student" Main Table Fields; ID, Student Number, Class, Name etc... Parent Tables Time Records From Sql (checkinout) ID, Device ID (Location), Time Stamp, Verification Type (Password, Card or Finger Print) Lesson Plans ID, Location, Date, Time, Status SETTINGS Table Late (Global Field) Early (Global Field) The problem is I can see student related lesson plans and Time records. I want to calculate Status field from related time records. When try to do this it is calculating first time records. I couldn't calculate other records. How can I do? Here is the calculation formula ----------------------------------------------------------------- Case ( DATE - CHECKINOUT::DATE = 0 and TIME - CHECKINOUT::TIME < 0; If ( CHECKINOUT::TIME - TIME ≤ SETTINGS::LATE; "CHECKIN"; "NOT CHECKIN" ) ; DATE - CHECKINOUT::DATE = 0 and  CHECKINOUT::TIME - TIME > 0; If ( CHECKINOUT::TIME - TIME ≥ SETTINGS::EARLY; "CHECKIN"; "NOT CHECKIN" ) -------------------------------------------------------------------------- When I use this formula it is checking only first time record. it is not check other related time records. How can I solve this problem? Here is the screen shoot. I am using Fm Pro Adv. 11.0v2. Thanks...

      PROBLEM.jpg

        • 1. Re: How can I calculate a related field with related table?
          philmodjunk

          You have a table for Time Records and a Table for Lesson Plans. Do you also have a table for Students and is this the table on which the above layout is based?

          I think you have these relationships:

          CHECKINOUT>------Students------<LessonPlans

          Students::__pk_StudentID = CHECKINOUT::_fk_StudentID
          Students::__pk_StudentID = LessonPlans::_Fk_StudentID

          Are lesson plans unique to each student or to a class or subject with many students?

          I believe that you have this calculation defined in LessonPlans:

          Case ( DATE - CHECKINOUT::DATE = 0 and TIME - CHECKINOUT::TIME < 0;
                       If ( CHECKINOUT::TIME - TIME ≤ SETTINGS::LATE; "CHECKIN"; "NOT CHECKIN" ) ;
                    DATE - CHECKINOUT::DATE = 0 and CHECKINOUT::TIME - TIME > 0;
                       If ( CHECKINOUT::TIME - TIME ≥ SETTINGS::EARLY; "CHECKIN"; "NOT CHECKIN" )
                   )

          If my analysis is correct (and it could easily be wrong), you are referring to records in CheckINOUT via a two stage relationship (lesson plans to students to checkinout). The intervening relationship will cause this calculation to refer to the first Checkinout record, not the one with the same date as the lesson plan record.

          You'd need this relationship:

          Students---<LessonPlans----CheckInOut

          LessonPlans::_fk_StudentID = CheckInOut::_fk_StudentID AND
          LessonPlans::Date = CheckInOut::date

          So that your calculation can refer directly to the corrected time record from the lesson plan record.

          Let me know if this is correct or not.