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

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 believe this could be achieved by looking at =

not IsEmpty ( FilterValues ( Carrier ; List ( Refuse::Carrier ) ) )