calculating from related records

[David Ayer]

New member, possibly naive question:

I have a families database for a small school. 2 tables: families, which contains parent contact information, and children, which contains child information, related to families by a unique index field (FamIndex.

Children has a field (for each child, obviosuly) called EnrollStatus which can have values 'inquiry', 'accepted', 'enrolled', 'alumni', 'declined,'etc. I would like a calculated field in parents which takes the values:

enrolled, if any of the associated children are enrolled,

alumni, if any of the children are alumni,

prospective, if any are inquiry or accepted,

declined, if none of the above are true and any children are declined.

I am using List and PatternCOunt like this:

Case(

PatternCount ( List ( children::EnrolStatusChild ); "enrolled")>0; "enrolled"; PatternCount(List(children::EnrolStatusChild ); "alumni")>0; "alumni";

(PatternCount ( List ( children::EnrolStatusChild ); "inquiry")>0

or

PatternCount ( List ( children::EnrolStatusChild ); "accepted")>0); "prospective";

PatternCount ( List ( children::EnrolStatusChild ); "declined")>0; "declined";

"other"

)

so I guess I have it figured out but it seems kind of inelegant.

Any thoughts?

Thanks,

Dave

[mr_vodka]

How about:

Let ( childstat = List ( children::EnrolStatusChild );

      Case ( not IsEmpty ( FilterValues ( childstat; "enrolled" ) ); "enrolled"; 

             not IsEmpty ( FilterValues ( childstat; "alumni" ) ); "alumni";

             not IsEmpty ( FilterValues ( childstat; "accepted" ) ); "prospective";

             not IsEmpty ( FilterValues ( childstat; "declined" ) ); "declined";

             "other"

           )  

    )