'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?

[Prateek kumar]

Hello Brad

Can we try something like this

var tr = "u_notallowed!=javascript:gs.getUserID()";

var gr = new GlideRecord('incident');

gr.addEncodedQuery(tr);

gr.query();

if(gr.next()){

gs.info(gr.getRowCount());

}

[nthumma · Accepted solution]

bradshryock   Does u_notallowed have value for all records or some of the records are empty?

you can try some thing like this   'watch_listNOT LIKEa8f98bb0eb32010045e1a5115206fe3a^ORwatch_listISEMPTY'

also can you post the screen grab of your records?

[Brad Shryock]

I think you found the missing piece - I do have empty values, so i need to actively include those as well.

Thanks!

[andymcdonald]

I'm pretty sure you do not want quotes around your gs.getUserID() in the query: gr.addQuery('u_notallowed','DOES NOT CONTAIN','gs.getUserID()');

you are passing the literal strring, not the userID