Don't think you need the additional table. While most calculation expressions only access the first related record, certain functions can access all the related records:
List ( Goals::Status ) will produce a return separated list of all the values of all the related fields
Not IsEmpty ( FilterValues ( List ( Goals::Status ) ; "In Progress" ) )
will be true if any of the related goals records has a status of "In Progress"
and PatternCount ( List ( Goals::Status ) ; "Pending" )
would return a nonzero result (True) if any of the related goals records as the word "status" somewhere in their status field.
And ExecuteSQL() could also be used to query the Goals table to test for "in Progress" or "Pending"...
A similar method (again :-); much the same as Phil's, but does some of the "testing" within the table native to the data. This is useful if you have several different values in the entry field, which now want to be seen as the same value. The tested values should be return as indexed.* I'm assuming that the field Status has only 1 value in it, in its native table.
Calculation field, name "_cStatus", result text:
PatternCount ( Status; "Pending" ); "Pending";
PatternCount ( Status; "odd words" ) "odd";
That produces "Pending" for any value which has it; hopefully the word in not mixed up with something else that you DON'T want as "Pending". If you do not need any special tests, then the last line will just return the value.
"Case" returns the first one it matches, so, if necessary, put them in order required. Or, as Phil said, use "FilterValues," which is easier to deal with "false positive matches".
This method will add some data to the file, as it should be an "indexed" field (will happen automatically).
*Likely a ExecuteSQL() might be the fastest. I don't know (yet) about it.
P.S. I wrote much of these earlier, but was distracted by my two grandchildren (3 & 4), who took over both my computers, to play games.
Phil -- I got your method working. Thank you.
Fenton -- I don't understand the indexing you mention, since my understanding is that calc fields can't be stored.... perhaps because of the grandchildren comment, part of your answer is missing?
All calculation fields are stored unless it is impossible for them to be stored or you specifically use storage options to specify that they not be stored.
Fenton is referring to a field in the child table that does some of the calculation needed. Since it is solely defined with references within the same table, it will be stored by default and this in turn can make for faster results than my original suggestion when dealing with large sets of related records.