# How can I calculate a related field with related table?

Question asked by nazacer on Jan 4, 2012
Latest reply on Jan 4, 2012 by philmodjunk

### 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...