Eliminate Date Where ID From Table.B Matches Table.A

[rose4567]

Hello all,

I am using Crystal 10. I have two tables and need to exclude data in my report based on where IDs match between the two tables.

TABLEA - shows all purchase orders
TABLEB - contains purchase orders I need to exclude from the larger set of data

I am excluding data where PO_ID and RELEASE_ID in TABLE B show up in TABLE A.

PO_ID and RELEASE_ID are also my primary key linking fields.

I have two issues.

(1) TABLE LINKING: I am not positive I've linked the tables correctly, but I am currently linking them through an inner join, not enforced and "=". I had initially thought it might be effective to use the "!=" on inner join, but it did not seem to product any record results which I know can't be correct.

(2) RECORD SELECTION CRITERIA: I tried to add the following to record selection criteria it did not give the desired effect:
{TABLEA.PO_ID} <> {TABLEB.PO_ID} and
{TABLEA.RELEASE_ID} <> {TABLEB.RELEASE_ID}

Any suggestions on record selection criteria to exclude IDs in TABLEB would be most helpful.

Thank you all!

 

[lbass]

You should use a left outer join FROM table A TO table B, and use a record selection formula like this:

isnull({tableB.PO_ID}) and
isnull({tableB.Release_ID})

This assumes that you only want to show those records where there are no matches on either field.

-LB

 

[rose4567]

I'm sorry. I neglected to mention that PO_ID is actually a string, not a number. RELEASE_ID is a number, but not PO_ID. That effects my ability to use "isnull" correct? I think that is why when I try to use the isnull on PO_ID I get zero results in my record set.

 

[Turkbear]

Hi,
IsNull should work on truly NULL fields, even if string types...To be sure, however, use:

(isnull({tableB.PO_ID}) or Trim({tableB.PO_ID}) ="")
and
isnull({tableB.Release_ID})


This will eliminate blanks which are not the same as NULLs



To Paraphrase:"The Help you get is proportional to the Help you give.."