I am trying to create a report to tell me who requires a certain qualification/license for their job (based on the data in our system), and then display those who do not have it in the system.

Ex: Rachel Test is compliant because she has an RN license, which is required for her job, along with other qualifications that I don’t care about. Joan Test is not compliant because she does not have an RN license, but it is required for her job, along with other qualifications that I don’t care about.

My results are displaying the following:

Employee        Qualification Code

Rachel Test     RN                             

Rachel Test     AA Degree

Rachel Test     Background Check

Joan Test         AA Degree

Joan Test         Background Check

How do I get Crystal to display a line item for Joan Test to indicate that she is not compliant because she does not have an RN license?

When I use a formula, I am still not about to get it to work properly as it returns the following:

Formula=

if {TABLE.CODE} <> ["RN"] then

    "Non-Compliant"

Else

    "Compliant"

Results=

Employee        Qualification Code     License Availability

Rachel Test     RN                              Compliant

Rachel Test     AA Degree                  Non-compliant

Rachel Test     Background Check     Non-compliant

Joan Test         AA Degree                  Non-compliant

Joan Test         Background Check     Non-compliant

Any assistance is greatly appreciated!