PICK NOT IN Issue

If I have this action in a Process...

PICK ONE OR MORE FROM Code WHERE (Code IN Customer.Codes)

Then, as expected I get a list of all the Code that are currently already in Customer.Codes

What I really want is a list of all the Code that are NOT currently in Customer.Codes...

I expected to be able to use

PICK ONE OR MORE FROM Code WHERE NOT(Code IN Customer.Codes)

But this fails with a database error (MSSQL)

Can anyone suggest the correct construct for this to work please?

Scrub that.....

Already had same issue long time ago and solved.

Need to use COUNT

Whoops!

Last time the solution was to use COUNT

PICK ONE OR MORE FROM Code WHERE COUNT Code WHERE(Code IN Customer.Codes)=0

This no longer gives and Error but does not return any records either.

If I change it to

PICK ONE OR MORE FROM Code WHERE COUNT Code WHERE(Code IN Customer.Codes)<>0

It returns every record regardless if they are in Customer.Codes or not.

That is totally illogical to me. 😐

Or am I looking at it from the wrong perspective?

I think PICK FROM works best when you use a defined query that gives what your want:

PICK ONE OR MORE FROM CodeQuery...

I tried that Kingsley.

It does not work that way either. 🙁

Fixed it thanx Kingsley.

Does this help at all:

TRY:
PICK ONE OR MORE FROM Code WHERE EXISTS Code WHERE (NOT(Code.Customer=ThisCustomer))