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.
Code Block |
---|
|
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:
Code Block |
---|
|
CreatedAt NOT IN ['2011-1-1','2012-1-1']
|
And also Time Span fields:
Code Block |
---|
|
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.
Code Block |
---|
|
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:
Code Block |
---|
|
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:
Code Block |
---|
|
EntityType = Scripts AND RelatedTo NOT IN { EntityType = Requirement AND Priority = Low AND Project = 'Project XYZ' }
|