AnsweredAssumed Answered

Trying to search related fields during field calculation...

Question asked by neil.simpson@me.com on Jul 27, 2011
Latest reply on Jul 27, 2011 by philmodjunk

Title

Trying to search related fields during field calculation...

Post

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;

NOT RECEIVED

FIRST

SECOND

CONTACT CLIENT

COMPLETE

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..

Any ideas??

Outcomes