You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 11 Next »

he tables below provide information about the available condition operators for specific column types.

Operator Display Name – contains the displayed operator name.

System name – contains the system operator name that is used in condition strings or scripts.

Script – contains the example of the script.

Condition builder – contains the condition and its result.

  • The input data for Date/Time fields must have the following format: YYYY-MM-DD hh:mm:ss.
  • Conditions for Time fileds require the input time in UTC.

List of operators

Operator Display NameSystem nameScriptCondition builder

is

=

current.addQuery('active', true);

current.addQuery('active', '1');

Active is Yes 

The output is all records where the Active field value is Yes only (the attribute value = true).

The operator is case insensitive.

The following column types have this operator:

  • Boolean
  • Choice
  • Document ID
  • JSON
  • List
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Reference
  • Record Class
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is not

!=

current.addQuery('active', '!=', true);

current.addQuery('active', '!=', '1'); // true

Active is not Yes 

The output is all records where the Active field is not enabled (the attribute value = false).


The operator is case insensitive.

The following column types have this operator:

  • Boolean
  • Choice
  • Document ID
  • JSON
  • List
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Reference
  • Record Class
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is same

SAMEAS

current.addQuery('active', 'sameas', 'published');

Active is same Published 

The output is all records where Active and Published have the same value (both fields have Yes or No).

The operator is case insensitive.

The following column types have this operator:

  • Boolean
  • Choice
  • Date
  • Date/Time Specific
  • JSON
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Reference
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is anything

ANYTHINGcurrent.addQuery('active', 'anything');

Active is anything

The output is all records where Active and Published have any value (both have Yes or No).

The following column types have this operator:

  • Boolean
  • Choice
  • Date
  • Date/Time Specific
  • JSON
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Reference
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is different

NSAMEAScurrent.addQuery('active', 'nsameas', 'published');

Active is different Published 

The output is all records where Active and Published have different values (one has Yes and the other has No or vice versa).

The operator is case insensitive.

The following column types have this operator:

  • Boolean
  • Choice
  • Date
  • Date/Time Specific
  • JSON
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Reference
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is one of

IN// High OR Moderate
current.addQuery('priority', 'in', ['2', '3']);

Priority is one of High, Moderate 

The output is all records where Priority value is "High" or "Moderate".

The is one of operator is not available for Document ID fields in the condition builder.

The values in the input field should be comma-separated or separated by the line break. Spaces are not allowed.

The following column types have this operator:

  • Choice
  • Document ID
  • Reference 
  • Record Class
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

is not one of

NOT IN// not High AND not Moderate
current.addQuery('priority', 'not in', ['2', '3']);


Priority is not one of High, Moderate 

The output is all records where Priority value is any other than "High" or "Moderate"

In the condition builder, the is one of operator is not available for:

  • Document ID
  • Record Class and numeric fields. It is used for filtering in scripts.

The following column types have this operator:

  • Choice
  • Document ID
  • Reference 
  • Record Class
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

greater than

>current.addQuery('priority', '>', '3') // High


Priority is greater than High 

The output is all records where Priority value is greater than High.

The following column types have this operator:

  • Choice
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

less than

<current.addQuery('priority', '<', '3') // High


Priority is less than High 

The output is all records where Priority value is less than High.

The following column types have this operator:

  • Choice
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

greater than or is

=>current.addQuery('priority', '=>', '3') // High

Priority is greater than or equal to High 

The output is all records where Priority value is greater than or equal to High.

The following column types have this operator:

  • Choice
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

less than or is

<=current.addQuery('priority', '<=', '3') // High

 

Priority is less than or equal to High

The output is all records where Priority value is less than or equal to High.

The following column types have this operator:

  • Choice
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

greater than field

GT_FIELDcurrent.addQuery('taskpd', 'gt_field', 'completed_tasks');


Tasks per day greater than field Completed tasks 

The output is all records where the field value of Tasks per day is greater than the field value of Completed tasks (not including its value).

The following column types have this operator:

  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

less than field

LT_FIELDcurrent.addQuery('taskpd', 'lt_field', 'completed_tasks');


Tasks per day less than field Completed tasks 

The output is all records where the field value of Tasks per day is less than the field value of Completed tasks (not including its value).

The following column types have this operator:

  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

greater than or is field

GT_OR_EQUALS_FIELDcurrent.addQuery('taskpd', 'gt_or_equals_field', 'completed_tasks');


Tasks per day greater than or is field Completed tasks 

The output is all records where the field value of Tasks per day is greater than the field value of Completed tasks or is equal to the value.

The following column types have this operator:

  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

less than or is field

LT_OR_EQUALS_FIELDcurrent.addQuery('taskpd', 'lt_or_equals_field', 'completed_tasks');


Tasks per day less than or is field Completed tasks 

The output is all records where the field value of Tasks per day is less than the field value of Completed tasks or is equal to the value.

The following column types have this operator:

  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

between

BETWEEN

current.addQuery('state', 'between', '1', '3')

// between Low and High


Priority is between Low and High 

The output is all records where Priority value is between "Low" and "High" or is "Low" or "High" (Critical priority is not included).

The following column types have this operator:

  • Choice
  • Date
  • Date/Time Specific
  • Time
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

contains

LIKE

current.addQuery('state', 'like', 'publish');


State contains publish 

The output is all records where State contains substring "publish" in choice title.

This condition operator returns records with partial matches and requires input datetime in UTC.

  • The contains operator is not available for numeric fields in the condition builder. It is used for filtering in scripts. 
  • The operator is case insensitive.

The following column types have this operator:

  • Choice
  • Date
  • Date/Time Specific
  • Time
  • Document ID
  • Field Name
  • JSON
  • List
  • Reference
  • Record Class
  • HTML 
  • Script
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

does not contain

NOTLIKEcurrent.addQuery('state', 'notlike', 'publish');


State does not contain publish 

The output is all records where State does not contain these characters in choice title. 

This condition operator returns records with partial matches and requires input datetime in UTC.

  • The does not contains operator is not available for numeric fields in the condition builder. It is used for filtering in scripts. 
  • The operator is case insensitive.

The following column types have this operator:

  • Choice
  • Date
  • Date/Time Specific
  • Time
  • Document ID
  • Field Name
  • JSON
  • List
  • Reference
  • Record Class
  • HTML 
  • Script
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

starts with

STARTSWITHcurrent.addQuery('state', 'startswith', 'publish');

State starts with publish 

The output is all records where State contains these characters at the beginning of choice title.

The operator is case insensitive.

The following column types have this operator:

  • Choice
  • Field Name
  • Reference
  • Record Class
  • HTML 
  • Script
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

ends with

ENDSWITHcurrent.addQuery('state', 'endswith', 'ed');

 

State ends with ed

The output is all records where State contains these characters at the end of choice title.

The operator is case insensitive.

The following column types have this operator:

  • Choice
  • Field Name
  • Reference
  • Record Class
  • HTML 
  • Script
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is empty

ISEMPTYcurrent.addQuery('state', 'isempty'); // Null or empty string


State is empty 

The output is all records where State does not contain any value (None).

Note that in case the Reference field is configured to On delete = No Action, the sys_id of the deleted record remains in the field (then the reference leads to a non-existing record). Such records will not be displayed in the "Reference field is empty" output. In order to exclude records with deleted reference in the output, use this condition:

Reference Field → ID is empty.

The following column types have this operator:

  • Choice
  • Date
  • Date/Time Specific
  • Time
  • Document ID
  • Field Name
  • Reference
  • List
  • JSON
  • Image
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

is not empty

ISNOTEMPTYcurrent.addQuery('state', 'isnotempty'); // not Null and empty string


State is not empty 

The output is all records where State contains any value.

Note that if the Reference field has the attribute On delete with the No Action value, the ID of the deleted record remains in the field (then the reference leads to a non-existing record). Such records will be displayed in the output of "Reference field is not empty". In order to exclude records with deleted reference from the output, use this condition:

Reference Field → ID is empty.

The following column types have this operator:

  • Choice
  • Date
  • Date/Time Specific
  • Document ID
  • Field Name
  • Reference
  • List
  • JSON
  • Image
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration
  • Phone
  • String
  • Text
  • Translated Text
  • Conditions
  • URL

on

ON

current.addQuery('expired_at', '2019-01-01');

current.addQuery('expired_at', '=', '2019-01-01');


Expire at on 2019-01-01 

The output is all records where Expire at field value is "2019-01-01" exactly.

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

not on

NOTONcurrent.addQuery('expired_at', '!=', '2019-01-01');


Expire at not on 2019-01-01 

The output is all records where Expire at is any value but "2019-01-01".

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

before

<current.addQuery('expired_at', '<', '2019-01-01');


Expire at before 2019-01-01 

The output is all records where Expire at date before "2019-01-01".

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

after

>current.addQuery('expired_at', '>', '2019-01-01');


Expire at after 2019-01-01 

The output is all records where Expire at date after "2019-01-01".

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

at or before

<=current.addQuery('expired_at', '<=', '2019-01-01');


Expire at at or before 2019-01-01 

The output is all records where Expire at is "2019-01-01" or the date before it.

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

at or after

>=current.addQuery('expired_at', '>=', '2019-01-01');


Expire at at or after 2019-01-01 

The output is all records where Expire at is "2019-01-01" or the date after it.

The following column types have this operator:

  • Date
  • Date/Time Specific
  • Time

year is

YEAR_IScurrent.addQuery('expired_at', 'year_is', '2020');


Expire at year is 2020 

The output is all records where the value (in UTC) of Expire at contains the corresponding year.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

month is

MONTH_IScurrent.addQuery('expired_at', 'month_is', '9');


Expire at month is September 

The output is all records where the database value (in UTC) of Expire at contains the corresponding month.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

quarter is

QUARTER_IScurrent.addQuery('expired_at', 'quarter_is', '2');


Expire at quarter is II 

The output is all records where the database value (in UTC) of Expire at contains the corresponding quarter.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

week is

WEEK_IScurrent.addQuery('expired_at', 'week_is', '32');

 

Expire at week is 32

The output is all records where the database value (in UTC) of Expire at contains the corresponding week.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

day is

DAY_IScurrent.addQuery('expired_at', 'day_is', '10');


Expire at day is 10 

The output is all records where the database value (in UTC) of Expire at contains the corresponding day of a month (10th day of any month).

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

day of week is

DAY_OF_WEEK_IScurrent.addQuery('expired_at', 'day_of_week_is', '1');


Expire at day of the week is Monday 

The output is all records where the database value (in UTC) of Expire at contains the corresponding day of the week.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date
  • Date/Time Specific

hour is

HOUR_IScurrent.addQuery('sys_created_at', 'hour_is', '10');

 

Created at hour is 10

The output is all records where the database value (in UTC) of Created at contains the corresponding hour.

This condition operator returns records with partial matches and requires input datetime in UTC.

The following column types have this operator:

  • Date/Time Specific
  • Time

are

AREcurrent.addQuery('keywords', 'are', 'mail');


Keywords are mail 

The output is all records where the Full text search column contains the corresponding value.

The operator is available for the columns of String type whose name is keyword.

has

HAScurrent.addQuery('categories', 'has', '155988310508800001,157688891108786828');


Categories has Article, Model 

The output is all records where Categories contains the corresponding lists of references in this order exactly.

"Categories has Article, Model"
and "Categories has Model, Article" are different conditions.

To get all records with the Article, Model categories, build the following condition:


Categories has Article AND Categories has Model

The operator is available for the columns of List type.

has not

HASNOTcurrent.addQuery('categories', 'hasnot', '155988310508800001,157688891108786828');


Categories has not Article, Model 

The output is all records where Categories does not contain the corresponding lists of references in this order exactly.

"Categories has not Article, Model"
and "Categories has not Model, Article" are different conditions.

To exclude all records with "Article" and "Model" categories build the following condition:


Categories has not Article AND Categories has not Model

The operator is available for the columns of List type.

contains (dynamic)

CONTAINS_DYNAMICcurrent.addQuery('followers', 'contains_dynamic', '160516373014385744'); // 160516373014385744 ID of the dymanic filter Me


Followers contains (dynamic) Me 

The output is all records where Followers contains the ID of the current user.

Note that when you use dynamic filters to return the sequence of IDs, the sequence is taken into account. 

The operator is available for the columns of List type.

does not contain (dynamic)

DOESNOTCONTAIN_DYNAMICcurrent.addQuery('followers', 'doesnotcontain_dynamic', '160516373014385744'); // 160516373014385744 ID of the dymanic filter Me

 

Followers does not contain (dynamic) Me

The output is all records where Followers does not contain ID of the current user. 

Note that when you use dynamic filters to return the sequence of IDs, the sequence is taken into account. 

The operator is available for the columns of List type.

is (dynamic)

DYNAMICcurrent.addQuery('assignment_group', 'dynamic', '1589643105003304555'); 1589643105003304555 ID of the dymanic filter My Group


Assignment Group is (dynamic) One of My Group 

The output is all records where Assignment Group contains one of the current user group ID.

If the dynamic filter returns a list of IDs, the order of the IDs is ignored.


The following column types have this operator:

  • Reference
  • Big Integer 
  • Integer
  • Small Integer 
  • Decimal
  • Float
  • Percent Completed
  • Days of Week
  • Duration

is not (dynamic)

ISNOT_DYNAMICcurrent.addQuery('assigned_user', 'isnotdynamic', '164643105003300222');


Assigned User is not (dynamic) Me 

The output is all records where Assigned User does not contain the ID of the current user (empty values are suitable).


If the dynamic filter returns a list of IDs, the order of the IDs is ignored.


The operator is available for the columns of Reference type.

Available Extra Operators

Condition operators changes, changes from, changes to and is calculated are applicable to any type of conditions. They are used to keep track of updates and changes in a particular field during the insertion or update of a record.

To use the VALCHANGES, CHANGESFROM, CHANGESTO, IS_CALCULATED operators, you need to configure it as a column extra attribute first.

Operator Display Name

System nameScriptCondition builder

changes

VALCHANGEScurrent.addQuery('priority', 'valchanges')

Priority changes

Returns true if the Priority field value changes during the record insertion or update.

When a record is inserted, the changes operator returns true for fields of any type. For example, if a business rule that runs on Insert has condition "Subject changes", then it will run when the Subject field is inserted with any value (except for null and the same as the Default Value).

changes from

CHANGESFROMcurrent.addQuery('priority', 'changesfrom', '3')

Priority changes from High 

Returns true if the State field value changes from "High" to any other value during the record insertion or update.

changes to

CHANGESTOcurrent.addQuery('priority', 'changesto', '3')


Priority changes to High 

Returns true if Priority field value changes to "High" during the record insertion or update.

is (calculated)

IS_CALCULATED current.addQuery('priority', 'is_calculated', '4')

Priority is (calculated) 4 

The output is all records where the value of the Priority field is Critical (Critical = 4).

  • No labels

© 2019-2024, SimpleOne

https://simpleone.ru