AnsweredAssumed Answered

Calculation Using Multiple Fields in Related Table

Question asked by JosefBalles on Jan 3, 2013
Latest reply on Jan 4, 2013 by philmodjunk

Title

Calculation Using Multiple Fields in Related Table

Post

     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.

Outcomes