Records not in a table

[NiteClerk]

Hi, Using CR 2008 version 12.0.0.683

I have a single table and a list of records. I want to find which records are not in the table.
I've tried: (Equip number list greatly shortened)

// No Equipment record
{EQUIP.SITEID} = "BJH" and
({EQUIP.EQNUM} in 
["NE00020283",
"NE00020310",
"SC00021070"])
and IsNull({EQUIP.EQNUM})

I've also tried LBass' solution in thread

http://www.tek-tips.com/viewthread.cfm?qid=1025063&page=1

// No Equipment record
{EQUIP.SITEID} = "BJH" and
not({EQUIP.EQNUM} in 
["NE00020283",
"NE00020310",
"SC00021070"])

And a number of other permutations. Any advice?

Thanks. -brain frozen Bob-

 

[NiteClerk]

Update. I just realized that in my Design section I'm trying to print the EQUIP.EQNUM
Which if it does not exist, will of course not print. So how do I print the Equip Numbers from my list that is not in the table?

 

[Turkbear]

Hi,
To do that you need to create a table with all the possible numbers - then you can use a SQL command instead of picking from the tables and do something like this:

Select EQNUM
from FULLTABLEIBUILT
where 
FULLTABLEIBUILT.EQNUM
NOT IN
(
Select EQNUM
FROM 
EQUIP
)

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

 

[lbass]

Where are these numbers if they are not in the equip table? Do you have them in a spreadsheet? How many are there? You could handle this in formulas referencing an array if there aren't too many (or too many records in total).

-LB