Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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 IDAnalysis_TypeAnalysis_CodeActive_YN
1234561123y
1234562456y
1234563789y
1234561999n
2345671123y
2345672987y
2345673654y

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.

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question