AnsweredAssumed Answered

Set field in related table depending on status of last record created in related table

Question asked by apdofficer on Jul 17, 2018
Latest reply on Jul 17, 2018 by apdofficer

I have searched different forums and found some similar calculations that I am looking for, but so far, I haven't had any luck. Any help would be super fantastic.

 

I have a "students" table and a "classes" table. They are related through a third table "studentcourses". One student can have multiple "class" records. I want to set a "status" field, in the students table, as "active", "inactive" or "prospect" according to the last class record, if a previous class record exists. If there is no related record in the class table, I would like for the status in the student table to be set to "prospect". If there are related records in the class table, I would like to look at the last record in order to see its status, "active" or "complete". If it is a completed class, I would like to set the student status to inactive and if it is an active class, I would like to set the student status to active.

 

I have looked at Last() and GetNthRecord, but perhaps the syntax I have tried is not correct.

 

I am still relatively new to filemaker, so any help or advice would be greatly appreciated.

Outcomes