How to use Operators

This article will guide you on how to use Operators in Rules & Validations and Supported functions

Operators are special symbols that perform specific operations on your controls or data. An operator is a character that represents a filter criterion. The filter operators can be used in different functions as well as for specifying Rules and Validations.

Operator Supported Functions

Depending upon the type of data source the filters can be used in the following functions as follows:

LOOKUP([Sheetname.columnname], Filter, Sort column, Order)

DLOOKUP([Sheetname.columnname],Filter, Sort column, Order)

BINDAPI([connectorname.result.rows.columnname1, connectorname.result.rows.columnname2,…], Filter , Sort column,Order)

Note: Filter happens at the client-side once we get the result from API.

FILTER([datastore.columnname1, datastore.columnname2,…], Filter, Sort column,Order)

Operator Format - $Key $Operator $Value

$Key - Column Name / Key to Compare
$Operator - Operator used to compare
$Value - Value to be compared. It can be from any control, variable or Static value

The Operators are categorized as follows: Basic and Advance Operators

Basic Operators

= (Equal To)

Name: Equal To

This operator is used to compare and retrieve data where the key needs to be exactly matched with the specified value.

Example

LOOKUP([BaseSheet.id,BaseSheet.email],BaseSheet.email = “sample@example.co.in”)

This formula will fetch record(s) where email is equal to sample@example.co.in

BINDAPI( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.name = “Roth”)

This function will fetch record(s) from the database where the name is equal to “Roth”.

Note: Filter happens at the client-side once we get the result from API.

Supported types

Key Operator Value Sample value
Text Type = Text Value “Tom”
Numeric Type = Numeric Value 100.25
Toggle Type = Boolean value TRUE


<> (Not Equal To)

Name: Not Equal To

This operator is used to compare or retrieve all the data where the key is not equal to or does not match the specified value.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.name.first_name,BaseSheet.gender],BaseSheet.gender<>“Male”)
The above formula will fetch all the records whose gender is not equal to Male.

Supported types

Key Operator Value Sample value
Text Type <> Text Value “James”
Numeric Type <> Numeric Value 200
Toggle Type <> Boolean value TRUE


> (Greater Than)

Name: Greater Than

This operator is used to compare or retrieve data where the value is greater than the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc.

Example

LOOKUP([BaseSheet.student_id,BaseSheet.averagescore],BaseSheet.averagescore > 80)

The above formula will fetch all records having an average score greater than 80.

Supported types

Key Operator Value Sample value
Numeric Type > Numeric Value 675
Decimal type > Decimal value 56.89
Percent type > Percent value 98%


< (Less than)

Name: Less than

This operator is used to compare or retrieve data where the value is less than the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc.

Example

LOOKUP([Prod_doc_disc.ProdName, Prod_doc_disc.Discount],Prod_doc_disc.Discount < 18%)

The above formula will fetch all product records with an Discount less than 18%.

BINDAPI ( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.author_id < 15)

This will fetch all rows from the connector where the column author_id is less than 15.

Note: Filter happens at the client-side once we get the result from API.

Supported types

Key Operator Value Sample value
Numeric Type < Numeric Value 675
Decimal type < Decimal value 56.89
Percent type < Percent value 98%


>= (Greater than or equal to)

Name: Greater than or equal to

This operator is used to compare or retrieve data where the value is greater than or equal to the specified value.

Example

LOOKUP([Prod.Prodname, Prod.Qty],Prod.Qty >= 30)

The above formula will fetch all product records with a quantity more than or equal to 30.

BINDAPI( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.author_id >= 15)

This will fetch all rows from the connector where the column author_id is more than or equal to 15.

Note: Filter happens at the client-side once we get the result from API.

Supported types

Key Operator Value Sample value
Numeric Type >= Numeric Value 423
Decimal type >= Decimal value 56.89
Percent type >= Percent value 98%


<= (less than or equal to)

Name: Less than or equal to

This operator is used to compare or retrieve data where the value is less than or equal to the specified value.

Example

LOOKUP([Prod.Prodname, Prod.Qty,Prod.Disc],Prod.Disc <= 23)

The above formula will fetch all records with product discounts less than or equal to 23.

Supported types

Key Operator Value Sample value
Numeric Type <= Numeric Value 423
Decimal type <= Decimal value 56.89
Percent type <= Percent value 98%


IsEmpty

This operator will evaluate if the control is empty. It will return true when the control is empty. This operator cannot be used in LOOKUP() or BINDAPI(). It is typically useful for the Rules and Validations.

Example

Supported types

Control values


IsNotEmpty

This operator will evaluate if the control is not empty. It will return true when the controls is not empty. This operator cannot be used in LOOKUP() or BINDAPI().

Example

Supported types

Control Value

Advance Operators

$cn

Name: Contains

This operator checks if a specified sequence of characters is present in a given control/column or not. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $cn “Development”)

which fetches all rows with the department that contains “Development”.

The above formula will fetch all departments that contain the text “Development” as shown in the below image.

BINDAPI( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.name $cn “A”)

which fetches all rows with name containing “A”

Note: Filtering happens at the client-side once we get the result from API.

Supported types

Key Operator Value Sample value
Text Type $cn Text Value “Acco”


$ncn

Name: NotContains

This operator is used to return a value indicating whether a specified substring does not occur within the searched control/column. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. It can be used in LOOKUP() as well as in BINDAPI() functions. It is important to remember that for BINDAPI(), the filtering happens at the client-side once we get the result from API.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $ncn “Development”)

Supported types

Key Operator Value Sample value
Text Type $ncn Text Value “Dev”


$stw

Name: StartsWith

This operator is used to search a text/string that begins with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $stw “Acc”)

will fetch all Job titles that start with “Acc”. For e.g Accounts executive, Accounts Head.

BINDAPI( [AuthorMongoDB.result.rows.author_id,AuthorMongoDB.result.rows.name,AuthorMongoDB.result.rows.last_name], AuthorMongoDB.result.rows.name $stw “R”)

will fetch all rows containing the name that starts with “R”. For e.g “Roth A”.

Note: Filter happens at the client-side once we get the result from API.

Supported types

Key Operator Value Sample value
Text Type $stw Text Value “Acc”


$nstw

Name: NotStartsWith

This operator works exactly opposite to StartsWith, where it is used to search a text/string that does not begin with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nstw “Acc”)

The above formula will fetch all job titles that do not begin with the text “Acc”.

Supported types

Key Operator Value Sample value
Text Type $nstw Text Value “Dept”


$enw

Name: EndsWith

This operator is used to search a text/string that ends with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $enw “IV”)

The above example will fetch all job titles that end with “IV”

Supported types

Key Operator Value Sample value
Text Type $enw Text Value “Inc”


$nenw

Name: NotEndsWith

This operator works exactly opposite to EndsWith, where it is used to search a text/string that does not end with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nenw “IV”)

The above formula will fetch all jobtitle that do not end with text “IV”.

Supported types

Key Operator Value Sample value
Text Type $nenw Text Value “Dept”


$in

Name: In

This operator is used to search a text/string where the value of the specified field is one of the multiple specified values. The values are specified as a comma-delimited list , surrounded by parentheses. Using $in is equivalent to using multiple EQUALS (=) statements, but is shorter and more convenient. For example, using this formula in Sheet Lookup control.

Example

LOOKUP([Candidate_experience_doc.unique_id,Candidate_experience_doc.Education], Candidate_experience_doc.Education $in [“Post graduation”, “High school”])

will return all those rows that contain the Candidate education as Post graduation or High school.

Supported types

Key Operator Value Sample value
Text Type $in Text Value “Teacher”


$nin

Name: not In

This operator is used to search for data where the value of the specified field is not one of the multiple specified values. Using $nin is equivalent to using multiple NOT_EQUALS (!=) statements, but is shorter and more convenient. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nin [“Teacher”,“Professor”])

This formula will fetch all records that do not have jobtitle as Teacher or Professor.

Supported types

Key Operator Value Sample value
Text Type $nin Text Value “ABC”


$any

Name: ContainsAny

This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. This operator is used to search the specified text/string in a given multi-select type control/column as shown in the below image -

.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $any [“Android SDK”,“TFS”])

The above formula will fetch all skills having value either Android SDK or TFS or both Android SDK & TFS

Supported types

Key Operator Value Sample value
Multiselect $any Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$nany

Name: NotContainsAny

This operator is used to search if the specified text/string is not present in the given control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $nany [“Android SDK”,“TFS”]`)

This will fetch all skills that do not have Android SDK, or TFS or Android SDK & TFS.

Supported types

Key Operator Value Sample value
Multiselect $nany Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$all

Name: ContainsAll

This operator is used to search if the given control/column contains all the values from the specified multi-select control. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $all [“Android SDK”,“TFS”])

The above formula will fetch those record that have both the values “Android SDK” and “TFS”

Supported types

Key Operator Value Sample value
Multiselect $all Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$nall

Name: NotContainsAll

This operator is used to search and return all the records that does not contain all the values of the specified control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $nall [“Android SDK”,“TFS”])

The above formula will fetch all records that do not have both values “Android SDK” and “TFS”.

Supported types

Key Operator Value Sample value
Multiselect =$nall Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$mncnt

Name: MinCount

This operator is used to check if the given control/column has a minimum count as specified in the criteria. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $mncnt 3)

The above formula will fetch those skills that have at least 3 values in a cell as shown in the below image.

Supported types

Key Operator Value Sample value
Multiselect $mncnt Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$mxcnt

Name: MaxCount

This operator is used to check if the given control/column has the maximum count as specified in the criteria. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control.

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $mxcnt 1)

The above formula will fetch those skills that have at most 1 value in a cell as shown in the below image.

Supported types

Key Operator Value Sample value
Multiselect $mxcnt Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$cnt

Name: Count

This operator is used to return or check the count of the values in the given multi select type control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $cnt 2)

The above formula will fetch those skills that have only 2 values in a cell as shown in the below image.

Supported types

Key Operator Value Sample value
Multiselect $cnt Array of numbers / Array of Strings [1,2,3,4]
[“jack”,“john”]


$regex

Name: Regex

This operator is used to match a string/text or your data based on a regular expression. For example, using this formula in Sheet Lookup control

Example

LOOKUP([BaseSheet.unique_id,BaseSheet.username],BaseSheet.username $regex “\d”)

The above example will fetch those usernames that have numbers in them using the regular expression \d as shown in the below image.

Supported types

Key Operator Value Sample value
Text Type $regex Text Value