Record selection issue - all records displaying despite criteria excluding them

[Former Member]

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.

[abhilash_kumar · Accepted solution]

Hi David,

Try:

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

AND

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

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you for the quick response. I've tried your formula (it's the same as I was entering, except for the brackets) and that's fixed the issue I had with every row appearing, however I still have the problem with the records not appearing if the supplier ID is not on the analysis table - only on the suppliers table.

Is there a way to make the records stay in the table even though the supplier ID doesn't exist on the analysis table?

Thanks

David

[abhilash_kumar · Accepted solution]

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