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

[ggg]

do you have to use the sys_id?

the number field also uniquely identifies the record.

what table?

[JosephW1]

Yes, I believe I do have to. I am querying all tickets not opened by certain agents.

I am not using User names (as opposed to Sys IDs) and the "Opened By" field because that field does not give the "is one of" operator as an option, so I do not know how to process a string of users with it.

Unfortunately neither fields ("Opened By" or "Opened By.Sys ID") give the "is not one of" operator as an option, which I am trying to resolve here.

I am running this from the incident table.

[rob_pastore]

What is the use case for this?  I wonder if there is another way to go about achieving a similar result.

[JosephW1]

Thank you Rob. I am querying all tickets not opened by certain agents. I already have a function setup that returns the string of User.Sys IDs that I want to process (users within any given group) that is tested and works with the "is one of" operator. Now I am trying to make it usable with the "is not one of" operator.

Edit: I have added more details about the use case to the original post.

[rob_pastore]

'is not one of' can be a slow query, which is probably why it is not available for sys_id.

How much is this list/filter going to be used?  If it's just a one off report for a small group of users it will be ok, but if it is heavily used by a large number of users I would try a different solution. (Maybe a check box on incident that on submit checks if the user is in one of these groups; now there is a simple way to filter)

If the groups you are checking are also dynamic, I can see this getting out of hand pretty quickly.

The only other option you may have (which will not help with performance, but may get you where you need to go) would be to check the sys_id in the result set (while loop).