Field as a "summary" of related fields

[FM::Student]

Hi,

I've run into what seems like a fairly straight-forwards problem but I can't figure it out.

I have my principal record "table1" with my related "table2". "table2" takes "table1"'s primary key.

I have a field in table 2 called status that can be set to "Open" or "Closed". I also have a status in table1 that can be "Open" , "Closed" or "Started". The way I want it to work is:

-If there are no related records in table 2: status = "Started"

-If there are "closed" and "open" related records in table2: status = "Open"

-If there are only "closed" related records in table 2: status = "closed"

I've been trying to figure out a calculation but I don't know where to start.

[comment]

Try:

Case (

IsEmpty ( Child::ParentID ) ; "Started" ;

IsEmpty ( FilterValues ( "Open" ; List ( Child::Status ) ) ) ; "Closed" ; 

"Open"

)

(I have renamed your tables to Parent and Child.)

[FM::Student]

Ok, that worked perfectly, thank you very much. I did not know what FilterValues() did and it's quite useful.