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.