IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE})

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

OR
{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

 

The problem that I am having is that the formula directly after the "or" is causing people to show up that are no longer employed.  Our system tracks the termination date and the registration date and sometimes people have four or five stints of employment.  I am only wanting if the registration date is after the latest termination, but this formula pulls for all terminations.  How can I recode the formula to get the desired results.

 

 

 

What is happening is the part of the code:

 

{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE} is returning multiple values which I have suppressed.  But the newest value is still showing up on the report, even if the person is no longer with us.  What I am trying to do is get the newest registration date be greater than the newest termination date, if that makes sense.

IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE}) is on an or and does not have the "Physician" "Psychiatrist" drill-down.  Is there any way to get the most recent  {SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} and most recent {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}.

 

After re-linking the tables as you described and used my original formula, (because the new one as stated above came back with people outside the parameter) it gave the same people as before re-linking the tables.