Operator: Not In

Description

This is a multi-purpose operator that has different uses, depending on the type of the field, they generally fall into 3 categories:

  • Numeric Fields
  • Picklists / Fields with values
  • Sub-selects

Numeric Fields

In numeric fields, the NOT IN operator can be used to express a range of values that should be excluded from the results i.e.

Number NOT IN [50,1000]

Would return all entities which have a number that is not between 1 and 1000 (so either less than 50 or greater than 1000).

Other numeric fields that support "In" are DateTime field:

CreatedAt NOT IN ['2011-1-1','2012-1-1']

And also Time Span fields:

EstimatedDuration NOT IN ['5 minutes','1 day']

Picklists / fields with values

Fields which have a range of values, which include:

  • Picklists (Status, Type, Priority etc.)
  • Custom fields
  • EntityType
  • Project
  • Package

Also support IN - in these cases, the IN statement will act like an "OR", returning any records which match one or more of the values in the list.

Priority IN [High,Medium]

Please refer to the details of each field to see what types of value are supported i.e for packages you can supply either a Package's unique identifier, a package name or a package path, and those values can be mixed within a single IN statement:

Package NOT IN ['cf1264f7-4e53-4e6d-b0a2-9f6c00a3a244', 'Cycle 2', '/Script Library/Cycle 1/User Interface']

Sub-selects

Sub-selects provide a way to filter a query based on the results of a second query - sub-selects differ from an IN statement for a range of values by being surrounded with curly braces { .. } as opposed to square brackets [].

Sub-selects are an advanced feature, and depending on the field the sub-select may "select into" a different index, which exposes a different set of available fields.

Here is an example of a sub-select to retrieve all Scripts which are not related to low priority requirements within a specific project:

EntityType = Scripts AND RelatedTo NOT IN { EntityType = Requirement AND Priority = Low AND Project = 'Project XYZ' }
  • No labels