Trying to search related fields during field calculation...
This one has me stumped! If you can help, please let me know!
Basically what I have is a table of "Clients" and a related table of "Cases" for each client. Each 'case' has a status like;
So a client could have 3 or 4 'cases' all with different statuses.
What I'm trying to do is create a calculation field in the "Client" table called 'Relationship Status' which will look at all of these statuses and produce a result based on what it finds.
For example: If all of the cases showed 'COMPLETE' then the result would be "All Finished"
If most of the cases were 'COMPLETE' but one of them was 'FIRST' then the result would be "Open Cases"
I've tried using If ( PatternCount ( Claims::Claim Status ; "First" ) ≥ 1 ; "Open Claims" ; ...and further nested IF's... but it seems to only look at the first related record. What I want to do is look at a specific field in all of the related records..