'DOES NOT CONTAIN' glide query, against a list attribute

[Brad Shryock]

All,

I'm having some difficulty putting together a glide query.   The results of my query in Scripts-Background are baffling.   Hopefully somebody can point me in the right direction...

The setup:

I have a custom table, extended from Task.   On this table, I have a variable called 'u_notallowed'.   It's a list, referencing sys_user.   I'm working up a glide query that will allow me to select records on this table, where I am NOT in the list of users stored in u_notallowed.   The problem is, I can't get it to behave the way I want - neither in my business's instance, nor in a personal developer instance.   Am I running up against glideobj/glidelement/array/string issues?   If so, i'm not quite sure how to solve inside the Glide Query.

In my tests, I'll go setup a few records, populate a few users into this "u_notallowed" variable, and make sure I have enough record permutations that my glide query will return objects.

If I do a

gr.addQuery('u_notallowed','DOES NOT CONTAIN','gs.getUserID()');

My results are EXACTLY THE ONE RECORD THAT DOES CONTAIN MY SYS_ID.   Which is exactly the opposite of what I'm trying to achieve, I want all the other records.   It's almost like 'DOES NOT CONTAIN' is doing the exact opposite of what it was intended.   (weird...)

So, i flip it on its head:

gr.addQuery('u_notallowed','CONTAINS','gs.getUserID()');

My results are empty.... (baffling)...   Unless I use the GUI?   My 'contains' filter works in the list view, with a filter set on the table...

So, I tried an encoded query...

gr.addEncodedQuery(u_participantsLIKE[sys_id]);

With good results, I get the few records I am a part of.

Okay, that works, so let's flip it back to DOES NOT CONTAIN:

gr.addEncodedQuery('u_participantsNOT LIKE[sys_id]');

ARGH!   Zero results, in either the GUI or the glide query....

How do write a glide query to find all the records in a table, where my sys_id isn't found in a single in a list attribute of users?

[andymcdonald]

you also need to make sure you're only testing records in which your field u_notallowed is NOT empty.   So, if necessary, grab those records first, then exclude them from your query.

[Brad Shryock]

I think you were close, I need the empties - but I wasn't actively checking for them.

Thanks for the help!

[andymcdonald]

for example, this works well:

var inc = new GlideRecord('incident');

inc.addNotNullQuery('assigned_to');

inc.addQuery('assigned_to','DOES NOT CONTAIN', gs.getUserID());

inc.query();

gs.print(inc.getRowCount());