Is there a way to perform a "NOT" comparison for a Reference Qualifier?

[bcronrath]

I ask because I am trying to set up a reference qualifier for a watch list field against the sys_user table, but the qualifier I'm using is only supposed to filter out a small set and still return most of the entries of the sys_user table.   Our sys_user table is huge, and having the reference qualifier script go through every single record that qualifies and then return the id back is taking so long that it won't be acceptable for me to implement it.   If I could just do the reverse, and rather have the function return the sys_id values of the entries I *do not* want to show in the reference qualifier, it should be much faster.   The problem I'm running into is it seems the reference qualifier only really seems to work based on an "IN" type clause for sys_id.   Is there way to force it to run as "NOTIN"?

Best regards,

Brian

[Chuck Tomasi · Accepted solution]

Hi Brian,

Yes, reference qualifiers can have negative logic. Here's one I threw together on the sys_user table where Location != San Diego.

location!=108752c8c611227501d4ab0e392ba97f

You could also do something like "does not contain"

locationNOT LIKEsan diego

[bcronrath]

Perfect!   Thank you, actually it looks like my main issue is I wasn't including the space between NOT and IN but seeing the NOT LIKE you just put there made it all the more clear

[Chuck Tomasi]

Helpful tip, Ralph...

Build your query on a list first, then right click on the right-most breadcrumb and choose Copy Query. Very useful when built scripts and reference qualifiers.

😉

[Sashi K1]

How about trying like this, below example exclude list of Incidents. You can change it to use for sysids

numberNOT LIKEINC0490199,INC0490197

Thanks