Accessing The "is not one of" Operator For User Sys ID Queries

[JosephW1]

Hello,

I am trying to run the following query:
"Opened by.Sys ID" "is not one of" "[sysid1],[sysid2],[etc]"

However, when choosing the operator, "is not one of" is not an available option.

I do have "is one of" as an option. ALSO, if I manipulate the url to impose the "is not one of" upon the query by changing the "opened_by.sys_idIN" to "opened_by.sys_idNOT%20IN", the query works and returns the expected "is not one of" results. So, I know that this operator would work on this query if it was available. Manipulating the URL is not a proper method of end-user access to this feature, though, nor is it usable in reporting.

Update 4.19.2021: That URL manipulated query can be pulled into the report designer, from a table query, by right-clicking a column header and choosing "Bar chart" or "Pie chart".

I have checked the "Operators available for filters and queries" document and it states that this operator is available for choice fields containing strings or integers, and I find that it's typically available when building conditions for fields that have choices defined.

Is there a way for me to have the "is not one of" operator available within the drop-down list of operators for this query?

Creating a dynamic filter won't work for my needs, as I need to be able to pass varying parameters on to the query.

Thank you very much for taking the time to read this question!

Kind Regards,
Joseph

[Alberto Consonn]

Hi,

it's not possible to have this operator for query the Sys ID field.

It only shows up when field type is of

1. String
2. Contains String
3. Contains Integer

If I have answered your question, please mark my response as correct and/or helpful so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

Thank you

Cheers
Alberto

[JosephW1]

Thank you for the reply. Actually, the document states that it is available for choice fields containing strings or integers, not for strings. Furthermore, the document doesn't even list "is one of" as an available operator for reference or string fields, yet my query lists it as an available operator. Which leads me to wonder what kind of field ServiceNow is treating this as and if the document is even accurate or not.

I apologize, before I mark something as a correct answer, I would prefer it to be a solution.

As my original post indicates, the "is not one of" operator works on my query through url manipulation, so if I can make it available it should function.

[ggg]

can you use "does not contain" ?

[JosephW1]

I tried that, but it did not work for the string of sys IDs that I am putting into it.
Ex: "Opened by.Sys ID" "does not contain" "123456,234567,345678"