Skip to Content
avatar image
Former Member

Record selection issue - all records displaying despite criteria excluding them

Hi,

I'm having some trouble with the Record Selection in Crystal which I was hoping someone could help with.

I have two tables which I've linked with a left outer join - table SUPPLIERS and table SUPPLIERANALYSIS. Each supplier is represented by a unique numerical identifier, and there are a small number of supplier IDs which appear on the SUPPLIERS table but not the SUPPLIERANALYSIS table. My report will have some info from the SUPPLIERS table and then the ANALYSIS_CODE field from the supplier analysis table.

The supplier analysis table is laid out like this:

Supplier ID Analysis_Type Analysis_Code Active_YN 123456 1 123 y 123456 2 456 y 123456 3 789 y 123456 1 999 n 234567 1 123 y 234567 2 987 y 234567 3 654 y

So every supplier ID will have 3 active records in SUPPLIERANALYSIS , with the 3 ANALYSIS_TYPE fields. There may also be some inactive records in that table.

I only want to display the active codes associated with analysis type 3 on the report, which I've done through the selection expert using this: isNull({SUPPLIERANALYSIS.ANALYSIS_TYPE}) or {SUPPLIERANALYSIS.ANALIS_TYPE} = "3"

However, I also want to exclude the inactive records from the supplier analysis table. I used this formula:
isNull({SUPPLIERANALYSIS.ANALYSIS_TYPE}) or {SUPPLIERANALYSIS.ANALYSIS_TYPE} = "3" and
isNull({SUPPLIERANALYSIS.ACTIVE_YN}) or {SUPPLIERANALYSIS.ACTIVE_YN} = "Y"

This looks like it should work, but it returns every record from the SUPPLIERANALYSIS table (all of the records with ANALYSIS_TYPE of 1, 2 or 3). Any idea why? The ACTIVE_YN column is completed on every row of the analysis table, there are no blanks. It's like the isNull statement is overruling the previous condition, even though there's an 'and' between them.

If I take out the "isNull({SUPPLIERANALYSIS.ACTIVE_YN})" then the correct records are shown, however the supplier IDs which don't exist on SUPPLIERANALYSIS are no longer appearing on the report.

I've tried using {SUPPLIERANALYSIS.ACTIVE_YN} <> "N" instead, but it makes no difference.

Can anyone help? Is there something I'm doing wrong? Thank you in advance for any help.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 26, 2015 at 02:06 PM

    Hi David,

    Try:

    ( isNull({SUPPLIERANALYSIS.ANALYSIS_TYPE}) or {SUPPLIERANALYSIS.ANALYSIS_TYPE} = "3")


    AND


    ( isNull({SUPPLIERANALYSIS.ACTIVE_YN}) or {SUPPLIERANALYSIS.ACTIVE_YN} = "Y")

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Try:

      ( isNull({SUPPLIERANALYSIS.SUPPLIERID}) )

      OR

      (

      ( isNull({SUPPLIERANALYSIS.ANALYSIS_TYPE}) or {SUPPLIERANALYSIS.ANALYSIS_TYPE} = "3")


      AND


      ( isNull({SUPPLIERANALYSIS.ACTIVE_YN}) or {SUPPLIERANALYSIS.ACTIVE_YN} = "Y")

      )

      -Abhilash