Operators available for filters and queries
The system provides a set of operators for use with filters, condition builders, and encoded queries. The data type of a field determines what operators are available for it.
Operators available for condition builders
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| AND | \[Active\]\[is\]\[True\]\[AND\]\[Caller\]\[is not empty\] | ^ | active=true^CallerISNOTEMPTY | All records that have both of the following:- Active field is True - Caller field has any value |
| OR condition | \[Short description\]\[is empty\]\[OR\]\[Description\]\[is empty\] | ^OR | short\_descriptionISEMPTY^ORdescriptionISEMPTY | All records that have at least one of the following:- Short description field has no value - Description field has no value |
| OR filter | All these conditions must be met \[Short description\]\[is empty\] OR all these conditions must be met \[Description\]\[is not empty\] | ^NQ new query | short\_descriptionISEMPTY^NQdescriptionISNOTEMPTY | All records that have at least one of the following:- Short description field has no value - Description field has no value |
Operators available for string fields
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| starts with | \[Short description\]\[starts with\]\[SAP\] | STARTSWITH | short\_descriptionSTARTSWITHSAP | All records in which the characters "SAP" appear at the beginning of the value for the Short description field. |
| ends with | \[Short description\]\[ends with\]\[outage\] | - % - ENDSWITH | short\_descriptionENDSWITHoutage | All records in which the string "outage" appears at the end of the value for the Short description field. |
| contains | \[Short description\]\[contains\]\[SAP\] | - \* - LIKE | short\_descriptionLIKESAP | All records in which the characters "SAP" appear anywhere in the value for the Short description field. |
| does not contain | \[Short description\]\[does not contain\]\[SAP\] | - !\* - NOT LIKE | short\_descriptionNOT LIKESAP | All records in which the characters "SAP" do not appear anywhere in the value for the Short description field. |
| is | \[Short description\]\[is\]\[Network storage unavailable\] | = | short\_description=Network storage unavailable | All records in which the Short description says nothing else but "Network storage is unavailable." |
| is not | \[Short description\]\[is not\]\[Network storage unavailable\] | != | short\_description!=Network storage unavailable | All records in which the value for the Short description field says anything but "Network storage is unavailable." |
| is empty | \[Short description\]\[is empty\] | ISEMPTY | short\_descriptionISEMPTY | All records in which there is no value in the Short description field. |
| is not empty | \[Short description\]\[is not empty\] | ISNOTEMPTY | short\_descriptionISNOTEMPTY | All records in which there is any value in the Short description field. |
| is anything | \[Short description\]\[is anything\] | ANYTHING | short\_descriptionANYTHING | All records in which the Short description field is one of the following:- any value - empty - NULL |
| is empty string | \[Short description\]\[is empty string\] | EMPTYSTRING | short\_descriptionEMPTYSTRING | All records in which there is no value in the Short description field. |
| less than or is | \[Short description\]\[less than or is\]\[s\] | <= | short\_descriptionlt;=s | All records in which the string in the Short description field is one of the following:- the first letter is any letter between "a" and "s" - the exact value is "s" |
| greater than or is | \[Short description\]\[greater than or is\]\[s\] | >= | short\_description>=s | All records in which the string in the Short description field is one of the following:- the first letter is any letter between "s" and "z" - the exact value is "s" |
| between | \[Short description\]\[between\]\[q\] and \[t\] | BETWEEN | short\_descriptionBETWEENq@t | All records in which the first letter in the Short description field is "q," "r," "s," or "t." |
| is same | \[Short description\]\[is same\] as \[Description\] | SAMEAS | short\_descriptionSAMEASdescription | All records in which there exist matching values for the Short description and Description fields. |
| is different | \[Short description\]\[is different\] from \[Description\] | NSAMEAS | short\_descriptionNSAMEASdescription | All records in which there exist differing values for the Short description and Description fields. |
Operators available for reference fields
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| is | \[Caller\]\[is\]\[Don Goodliffe\] | = | caller\_id=9ee1b13dc6112271007f9d0efdb69cd0 | All records in which the Caller is no one else but Don Goodliffe. |
| is not | \[Caller\]\[is not\]\[Don Goodliffe\] | != | caller\_id!=9ee1b13dc6112271007f9d0efdb69cd0 | All records in which the Caller field is populated by any user record but Don Goodliffe. |
| is empty | \[Caller\]\[is empty\] | ISEMPTY | caller\_idISEMPTY | All records in which the Caller field has no value. |
| is not empty | \[Caller\]\[is not empty\] | ISNOTEMPTY | caller\_idISNOTEMPTY | All records in which there is any value in the Caller field. |
| starts with | \[Caller\]\[starts with\]\[Don\] | STARTSWITH | caller\_idSTARTSWITHDon | All records in which the name in the Caller field begins with "Don." |
| ends with | \[Caller\]\[ends with\]\[liffe\] | - % - ENDSWITH | caller\_idENDSWITHliffe | All records in which the name in the Caller field has the letters "liffe" at the end. |
| contains | \[Caller\]\[contains\]\[on\] | - \* - LIKE | caller\_idLIKEon | All records in which the characters "on" appear anywhere in the name for the Caller field. |
| does not contain | \[Caller\]\[does not contain\]\[on\] | - !\* - NOT LIKE | caller\_idNOT LIKEon | All records in which the characters "on" do not appear anywhere in the name for the Caller field. |
| is anything | \[Caller\]\[is anything\] | ANYTHING | caller\_idANYTHING | All records in which the Caller field is one of the following:- any value - empty - NULL |
| is same | \[Caller\]\[is same\] as \[Assigned to\] | SAMEAS | caller\_idSAMEASassigned\_to | All records in which the same user record is referenced in the Caller and Assigned to fields. |
| is different | \[Caller\]\[is different\] from \[Assigned to\] | NSAMEAS | caller\_idNSAMEASassigned\_to | All records in which the user records in the Caller and Assigned to fields do no match. |
| is empty string | \[Caller\]\[is empty string\] | EMPTYSTRING | caller\_idEMPTYSTRING | All records in which the Caller field has no value. |
| is \(dynamic\) | \[Caller\]\[is \(dynamic\)\]\[Me\] | DYNAMIC | caller\_idDYNAMIC54635e965f510100a9ad2572f2b4774c | All records in which your user record populates the Caller field. |
| is in hierarchy | \[Manager\]\[is in hierarchy\]\[Manager Hierarchy\] starting at \[Don Goodliffe\] which is \[Included\] | IN\_HIERARCHY | managerIN\_HIERARCHYfdd0fa0f93f5d2505bd0bd886cba1015%409ee1b13dc6112271007f9d0efdb69cd0 | All records throughout the Manager hierarchy in which the manager is Don Goodliffe or a user reporting up to Don Goodliffe. |
| is in hierarchy \(dynamic\) | \[Manager\]\[is in hierarchy \(dynamic\)\]\[Manager Hierarchy\] starting at \[Me\] which is \[Included\] | IN\_HIERARCHY\_DYNAMIC | managerIN\_HIERARCHY\_DYNAMICfdd0fa0f93f5d2505bd0bd886cba1015%4090d1921e5f510100a9ad2572f2b477fe | All records throughout the Manager hierarchy in which the manager is you or a user reporting up to you.Note: A record hierarchy between related records in the same table must be established before you can use this operator. See Building hierarchical queries. |
Note: Not all operators are available for all reference types. Depending on the reference you select, you may see a shorter list of operators.
Operators available for choice fields containing strings
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| is | \[Subcategory\]\[is\]\[Email\] | = | subcategory=email | All records in which the Subcategory is nothing else but Email. |
| is not | \[Subcategory\]\[is not\]\[Email\] | != | subcategory!=email | All records in which the Subcategory field is populated by any value except Email. |
| is one of | \[Subcategory\]\[is one of\]\[DB2, MS SQL Server, Oraclel\] | IN | subcategoryINdb2,sql server,oracle | All records in which the Subcategory field is populated by one of the following values:- DB2 - MS SQL Server - Oraclel |
| is not one of | \[Subcategory\]\[is not one of\]\[DB2, MS SQL Server, Oraclel\] | NOT IN | subcategoryNOT INdb2,sql server,oracle | All records in which the Subcategory field is populated by anything except the following values:- DB2 - MS SQL Server - Oraclel |
| contains | \[Subcategory\]\[contains\]\[Em\] | LIKE | subcategoryLIKEem | All records in which the characters "Em" appear anywhere in the value for the Subcategory field. |
| starts with | \[Subcategory\]\[starts with\]\[Em\] | STARTSWITH | subcategorySTARTSWITHem | All records in which the characters "Em" appear at the beginning of the value for the Subcategory field. |
| ends with | \[Subcategory\]\[ends with\]\[il\] | - % - ENDSWITH | subcategoryENDSWITHil | All records in which the characters "il" appear at the end of the value for the Subcategory field. |
| does not contain | \[Subcategory\]\[does not contain\]\[Em\] | NOT LIKE | subcategoryNOT LIKEem | All records in which the characters "Em" do not appear anywhere in the value for the Subcategory field. |
| is anything | \[Subcategory\]\[is anything\] | ANYTHING | subcategoryANYTHING | All records in which the Subcategory field is one of the following:- any value - empty - NULL |
| is same | \[Subcategory\]\[is same\] as \[Category\] | SAMEAS | subcategorySAMEAScategory | All records in which there exist matching values for the Subcategory and Category fields. |
| is different | \[Subcategory\]\[is different\] from \[Category\] | NSAMEAS | subcategoryNSAMEAScategory | All records in which there exist differing values for the Subcategory and Category fields. |
Operators available for choice fields containing integers
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| is | \[Impact\]\[is\]\[1 - High\] | = | impact=1 | All records in which the value in the Impact field is nothing else but 1 - High. |
| is not | \[Impact\]\[is not\]\[1 - High\] | != | impact!=1 | All records in which the value in the Impact field is anything but 1 - High. |
| is one of | \[Impact\]\[is one of\]\[1 - High, 2 - Medium\] | IN | impactIN1,2 | All records in which the Impact field is populated by one of the following values:- 1 - High - 2 - Medium |
| is not one of | \[Impact\]\[is not one of\]\[1 - High, 2 - Medium\] | NOT IN | impactNOT IN1,2 | All records in which the Impact field is populated by anything except the following values:- 1 - High - 2 - Medium |
| is empty | \[Impact\]\[is empty\] | EMPTY | impactISEMPTY | All records in which the Impact field has no value. |
| is not empty | \[Impact\]\[is not empty\] | NOTEMPTY | impactISNOTEMPTY | All records in which the Impact field has any value. |
| less than | \[Impact\]\[less than\]\[2 - Medium\] | < | impact<2 | All records in which the Impact field has a value of 1 - High. |
| greater than | \[Impact\]\[greater than\]\[2 - Medium\] | > | impact>2 | All records in which the Impact field has a value of 3 - Low |
| less than or is | \[Impact\]\[less than or is\]\[2 - Medium\] | <= | impact<=2 | All records in which the Impact field has a value of 1 - High or 2 - Medium. |
| greater than or is | \[Impact\]\[greater than or is\]\[2 - Medium\] | >= | impact>=2 | All records in which the Impact field has a value of 2 - Medium or 3 - Low. |
| between | \[Impact\]\[between\]\[1 - High\] and \[3 - Low\] | BETWEEN | impactBETWEEN1@2 | All records in which the Impact field has one of the following values:- 1 - High - 2 - Medium - 3 - Low . |
| is anything | \[Impact\]\[is anything\] | ANYTHING | impactANYTHING | All records in which the Impact field is one of the following:- any value - empty - NULL |
| is same | \[Impact\]\[is same\] as \[Urgency\] | SAMEAS | impactSAMEASurgency | All records in which there exist matching values for the Impact and Urgency fields. |
| is different | \[Impact\]\[is different\] from \[Urgency\] | NSAMEAS | impactNSAMEASurgency | All records in which there exist differing values for the Impact and Urgency fields. |
Note: The operators ‘less than or is’ and ‘greater than or is’ are not supported for integer fields in the condition builder. Use a scripted condition instead.
Operators available for date-time fields
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| on | \[SLA due\]\[on\]\[Today\] | ONToday | sla\_dueONToday@javascript:gs.daysAgoStart\(0\)@javascript:gs.daysAgoEnd\(0\) | All records in which the value for the SLA due field matches the date for today. |
| not on | \[SLA due\]\[not on\]\[Today\] | NOTONToday | sla\_dueNOTONToday@javascript:gs.daysAgoStart\(0\)@javascript:gs.daysAgoEnd\(0\) | All records in which the value for the SLA due field is any other date but today. |
| before | \[SLA due\]\[before\]\[Today\] | < | sla\_due<javascript:gs.daysAgoStart\(0\) | All records in which the value for the SLA due field is any date previous to today. |
| at or before | \[SLA due\]\[at or before\]\[Today\] | <= | sla\_due<=javascript:gs.daysAgoEnd\(0\) | All records in which the value for the SLA due field is one of the following:- any date previous to today - today |
| after | \[SLA due\]\[after\]\[Today\] | > | sla\_due>javascript:gs.daysAgoEnd\(0\) | All records in which the value for the SLA due field is any date after today. |
| at or after | \[SLA due\]\[at or after\]\[Today\] | >= | sla\_due>=javascript:gs.daysAgoStart\(0\) | All records in which the date value for the SLA due field is one of the following:- today - any date after today |
| between | \[SLA due\]\[between\]\[Yesterday\] and \[Today\] | BETWEEN | sla\_dueBETWEENjavascript:gs.daysAgoStart\(1\)@javascript:gs.daysAgoEnd\(0\) | All records in which the value for the SLA due field is either yesterday's or today's date. |
| trend \(on or after\) | \[SLA due\]\[trend\]\[on or after\]\[Monday\] | DATEPART | sla\_dueDATEPARTMonday@javascript:gs.datePart\('dayofweek','monday','GE'\) | All records in which the date in the SLA due field is on any day of the week except a Sunday. |
| trend \(on or before\) | \[SLA due\]\[trend\]\[on or before\]\[Monday\] | DATEPART | sla\_dueDATEPARTMonday@javascript:gs.datePart\('dayofweek','monday','LE'\) | All records in which the date in the SLA due field is on either a Sunday or a Monday. |
| trend \(after\) | \[SLA due\]\[trend\]\[after\]\[Monday\] | DATEPART | sla\_dueDATEPARTMonday@javascript:gs.datePart\('dayofweek','monday','GT'\) | All records in which the date in the SLA due field is on any day of the week except a Sunday or a Monday. |
| trend \(before\) | \[SLA due\]\[trend\]\[before\]\[Monday\] | DATEPART | sla\_dueDATEPARTMonday@javascript:gs.datePart\('dayofweek','monday','LT'\) | All records in which the date in the SLA due field is on a Sunday. |
| trend \(on\) | \[SLA due\]\[trend\]\[on\]\[Monday\] | DATEPART | sla\_dueDATEPARTMonday@javascript:gs.datePart\('dayofweek','monday','EE'\) | All records in which the date in the SLA due field is on a Monday. |
| relative \(after\) | \[SLA due\]\[relative\]\[after\]\[1\]\[Hours\]\[ago\] | RELATIVEGT | sla\_dueRELATIVEGT@hour@ago@1 | All records in which the time on the SLA due field is no more than an hour before the time that you entered the query. |
| relative \(before\) | \[SLA due\]\[relative\]\[before\]\[1\]\[Hours\]\[ago\] | RELATIVELT | sla\_dueRELATIVELT@hour@ago@1 | All records in which the time on the SLA due field is less than an hour before the time that you entered the query. |
| is empty | \[SLA due\]\[is empty\] | ISEMPTY | sla\_dueISEMPTY | All records in which the SLA due field has no value. |
| is not empty | \[SLA due\]\[is not empty\] | ISNOTEMPTY | sla\_dueISNOTEMPTY | All records in which the SLA due is populated by any value. |
| is anything | \[SLA due\]\[is anything\] | ANYTHING | sla\_dueANYTHING | All records in which the SLA due field is one of the following:- any value - empty - NULL |
| is same | \[SLA due\]\[is same\] as \[Activity due\] | SAMEAS | sla\_dueSAMEASactivity\_due@day | All records in which there are matching values for the SLA due and Activity due fields. |
| is different | \[SLA due\]\[is different\] from \[Activity due\] | NSAMEAS | sla\_dueNSAMEASactivity\_due@day | All records in which there are differing values for the SLA due and Activity due fields. |
| is more than | \[SLA due\]\[is more than\]\[1\]\[Days\]\[before\]\[Activity due\] | MORETHAN | sla\_dueMORETHANactivity\_due@day@before@1 | All records in which the date value for the SLA due field is two or more days previous to the date in the Activity due field. |
| is less than | \[SLA due\]\[is less than\]\[3\]\[Days\]\[before\]\[Activity due\] | LESSTHAN | sla\_dueLESSTHANactivity\_due@day@before@3 | All records in which the date value for the SLA due field is no more than three days prior to the date in the Activity due field. |
Operators available for numeric fields
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| is | \[Reassignment count\]\[is\]\[0\] | = | reassignment\_count=0 | All records in which the Reassignment count is nothing else but 0. |
| is not | \[Reassignment count\]\[is not\]\[0\] | != | reassignment\_count!=0 | All records in which the value for the Reassignment count is any number but 0. |
| is empty | \[Reassignment count\]\[is empty\] | EMPTY | reassignment\_countISEMPTY | All records in which there is no value in the Reassignment count field. |
| is not empty | \[Reassignment count\]\[is not empty\] | NOTEMPTY | reassignment\_countISNOTEMPTY | All records in which there is any number in the Reassignment count field. |
| less than | \[Reassignment count\]\[less than\]\[2\] | < | reassignment\_count<2 | All records in which the value in the Reassignment count field is any number less than (but not equal to) 2. |
| greater than | \[Reassignment count\]\[greater than\]\[2\] | > | reassignment\_count>2 | All records in which the value in the Reassignment count field is any number greater than (but not equal to) 2. |
| less than or is | \[Reassignment count\]\[less than or is\]\[2\] | <= | reassignment\_count<=2 | All records in which the value in the Reassignment count field is one of the following:- a number less than 2 - 2 |
| greater than or is | \[Reassignment count\]\[greater than or is\]\[2\] | >= | reassignment\_count>=2 | All records in which the value in the Reassignment count field is one of the following:- 2 - a number greater than 2 |
| between | \[Reassignment count\]\[between\]\[1\] and \[3\] | BETWEEN | reassignment\_countBETWEEN1@3 | All records in which the number in the Reassignment count is either 1, 2, or 3. |
| is anything | \[Reassignment count\]\[is anything\] | ANYTHING | reassignment\_countANYTHING | All records in which the Reassignment count field is one of the following:- any value - empty - NULL |
| is same | \[Reassignment count\]\[is same\] as \[Reopen count\] | SAMEAS | reassignment\_countSAMEASreopen\_count | All records in which there are matching numbers for the Reassignment count and Reopen count fields. |
| is different | \[Reassignment count\]\[is different\] from \[Reopen count\] | NSAMEAS | reassignment\_countNSAMEASreopen\_count | All records in which there are differing values between the Reassignment count and Reopen count fields. |
| greater than field | \[Reassignment count\]\[greater than field\]\[Reopen count\] | GT\_FIELD | reassignment\_countGT\_FIELDreopen\_count | All records in which the number in the Reassignment count field is greater than (but not equal to) the number in the Reopen count field. |
| less than field | \[Reassignment count\]\[less than field\]\[Reopen count\] | LT\_FIELD | reassignment\_countLT\_FIELDreopen\_count | All records in which the number in the Reassignment count field is less than (but not equal to) the number in the Reopen count field. |
| greater than or is field | \[Reassignment count\]\[greater than or is field\]\[Reopen count\] | GT\_OR\_EQUALS\_FIELD | reassignment\_countGT\_OR\_EQUALS\_FIELDreopen\_count | All records in which the number in the Reassignment count field is one of the following:- greater than the number in the Reopen count field - equal to the number in the Reopen count field |
| less than or is field | \[Reassignment count\]\[less than or is field\]\[Reopen count\] | LT\_OR\_EQUALS\_FIELD | reassignment\_countLT\_OR\_EQUALS\_FIELDreopen\_count | All records in which the number in the Reassignment count field is one of the following:- less than the number in the Reopen count field - equal to the number in the Reopen count field |
Operators available for Boolean fields
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| is | \[Active\]\[is\]\[true\] | = | active=true | All records in which the Active field is True. |
| is not | \[Active\]\[is not\]\[true\] | != | active!=true | All records in which the Active field is False, empty, or NULL. |
| is empty | \[Active\]\[is empty\] | ISEMPTY | activeISEMPTY | All records in which there is no value in the Active field. |
| is not empty | \[Active\]\[is not empty\] | ISNOTEMPTY | activeISNOTEMPTY | All records in which the Active field is either True or False. |
| is anything | \[Active\]\[is anything\] | ANYTHING | activeANYTHING | All records in which the Active field is one of the following:- True - False - empty - NULL |
| is same | \[Active\]\[is same\] as \[Made SLA\] | SAMEAS | activeSAMEASmade\_sla | All records in which there are matching values for the Active and Made SLA fields. |
| is different | \[Active\]\[is different\] from \[Made SLA\] | NSAMEAS | activeNSAMEASmade\_sla | All records in which there are differing values for the Active and Made SLA fields. |
Operators available for email notifications
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| changes | [State][changes] | VALCHANGES | stateVALCHANGES | All records in which the State field is updated. |
| changes from | [State][changes from][Awaiting User Info] | CHANGESFROM | stateCHANGESFROM4 | All records in which the State field is updated to another value after previously being Awaiting User Info (4). |
| changes to | [State][changes to][Awaiting User Info] | CHANGESTO | stateCHANGESTO4 | All records in which the State field is updated to Awaiting User Info (4) after previously being any other value. |
Operators available for tags
| Operator label | Example condition | Equivalent query operator | Example query | Example output |
|---|---|---|---|---|
| have | [Tag][have][Most Active] | = | sys_tags.6c361e12471331007f47563dbb9a7135=6c361e12471331007f47563dbb9a7135 | All records that have the Tag in the value field. |
| does not have | [Tag][does not have][Most Active] | DOESNOTHAVE | sys_tags.6c361e12471331007f47563dbb9a7135DOESNOTHAVE6c361e12471331007f47563dbb9a7135 | Any records that has a Tag except for the tag mentioned in the value field. |
| excluding | [Tag][excluding][Most Active] | EXCLUDING | sys_tags.6c361e12471331007f47563dbb9a7135EXCLUDING6c361e12471331007f47563dbb9a7135 | All records with and without the Tag except for the tag mentioned in the value field. |
Parent Topic:Create a condition statement using the condition builder
Related topics