This might be real easy to accomplish, but I'm kinda stuck on this one.
Here's what I need: I need a count of related records that match certain criteria.
My setup is as follows:
I have a Main Table with Records, each representing a patient.
Every Record for a Patient also includes one Doctor and one insurance carrier.
Here's the challenge: Certain Doctor's don't work for certain carriers.
So, I set up a related table (that's related to 'Doctor') called 'Refuse'.
Table Refuse contains just two fields, Doctor and Carrier.
What I need to get done is to throw a warning whenever a patient record is created in the main table that has a Doctor/Carrier combination that is blacklisted by a 'Refuse' table entry.
I have tried the related.countif approach, but that only seems to work with matching records agains firm criteria - Refuse being a related table Doctor = Doctor:
related.countif ( Refuse::Carrier ; "value = " & Carrier ; "number" ) - does not work
related.countif ( Refuse::Carrier ; "value = Anthem" ; "number" ) - would work, but is of no use because I need to match against the Carrier that's on file for the patient.
I also tried to look into self-join relationships, but this also yielded no results.
Is there any foolproof approach to this problem?
Thanks!
Tucky
