Calculation Using Multiple Fields in Related Table
I have table_1 related to table_2 in a one-to-many relationship. Table_1 is an employee profile and table_2 contains records pertaining to each employee. I'd like the field "status" in table_1 to be calculated from values in table_2.
Each employee has a start date and an end date, recorded in table_2 as separate records with a "date" field and a "record_type" field (record types being either "start date" or "end date").
I want the "status" field to be filled with "active" if there exists a related record in table_2 with the "record_type" field filled with "start date" and the "status" field to be filled with "terminated" if there exists a related record in table_2 with the "record_type" field filled with "end date" AND with the same record having the "date" field filled with a date that is before or on the current date.
Right now, I have "status" as a calculated field using the following formula:
Case ( table_2::record_type = "start date" ; "active" ; table_2::record_type = "end date" and table_2::date =< Get(CurrentDate) ; "terminated" )
The first case works, but I can't figure out how to get the second one to, so the "status" field is filled with "active" for all records in table_1.