Filter truth table

This article is deprecated. Refer to this article instead.

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)



Basic Operators

Filter Operator Data Type Description Example
Equal To = Text
Number
Decimal
Toggle
Boolean
This operator is used to compare or retrieve data
where the value needs
to be exactly matched with the specified value.

Applicable functions: Lookup
BINDAPI
LOOKUP([Sheet1.unique_id,Sheet1.email],Sheet1.email=“olag.gentil@house.gov”)

This formula will fetch record(s) where email is olag.gentil@house.gov

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 “Roth”.

Important Note: Filter happens at the client-side once we get the result from API.
Not Equal To <> Text
Number
Decimal Toggle
Boolean
This operator is used to compare or retrieve all the data where the value does not match the specified value.

Applicable functions: Lookup
BINDAPI
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.

Important Note: Filter happens at the client-side once we get the result from API.
Greater Than > Number
Decimal
Date & Time,
Percent
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. and date & time control.

Applicable functions: Lookup
BINDAPI
LOOKUP([BaseSheet.unique_id,BaseSheet.averagescore],BaseSheet.averagescore>80)

The above formula will fetch all records having an average score greater than 80.
Less Than < Number
Decimal
Date & Time,
Percent
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. and date & time control.

Applicable functions: Lookup
BINDAPI
LOOKUP([BaseSheet.unique_id,BaseSheet.averagescore],BaseSheet.averagescore<50)

The above formula will fetch all records with an average score of less than 50.

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.

Important Note: Filter happens at the client-side once we get the result from API.
Greater Than Equal To >= Number / Decimal
Date & Time,
Percent
This operator is used to compare or retrieve data where the value is greater than or equal to the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time control.

Applicable functions: Lookup
BINDAPI
LOOKUP([Prod.Prodname, Prod.Qty],Prod.Qty>=30)

The above formula will fetch all product records with 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.

Important Note: Filter happens at the client-side once we get the result from API.
Less Than Equal To <= Number
Decimal
Date & Time,
Percent
This operator is used to compare or retrieve data where the value is less than or equal to the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time control.

Applicable functions: Lookup
BINDAPI
LOOKUP([Prod.Prodname, Prod.Qty,Prod.Disc],Prod.Disc <= 23)

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

Advanced Operators

Filter Operator Description Example
Contains ($cn) 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. LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $cn “Development”)

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

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”
NotContains ($ncn) 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. LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $ncn “Development”)

The above formula will fetch all department that does not contain the text “Development”.
StartsWith ($stw) 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. For example, using this formula in Sheet Lookup control 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”.
NotStartsWith ($nstw) 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. For example, using this formula in Sheet Lookup control LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nstw “Acc”)

The above formula will fetch all jobtitles that do not begin with the text “Acc”.
EndsWith ($enw) 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. LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $enw “IV”)

The above example will fetch all jobtitles that ends with “IV”.
NotEndsWith ($nenw) 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. LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nenw “IV”)

The above formula will fetch all jobtitle that do not end with text “IV”.
In ($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. 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.
Not In ($nin) 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.
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nin [“Teacher”,“Professor”])

The above formula will fetch all records that do not have Jobtitle as Teacher or Professor.
ContainsAny ($any) 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 multiselect type control/column. LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $any [“Android SDK”,“TFS”])

The above formula will fetch all skills that has value either Android SDK or TFS or both Android SDK & TFS
NotContainsAny ($nany) 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.
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.
ContainsAll ($all) This operator is used to search if the given control/column contains all the values
from specified multiselect control. This operator is used in Multiselect type controls
viz. Multiselect, Checkbox, Keyword Box, etc.
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”
NotContainsAll ($nall) 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.
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $nall [“Android SDK”,“TFS”])
The above formula will fetch all records that does not have both values “Android SDK” and “TFS”.
MinCount ($mncnt) This operator is used to check if the given control/column has
minimum count as specified in the criteria. This operator is used in Multiselect type controls
viz. Multiselect, Checkbox, Keyword Box, etc.
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.
MaxCount ($mxcnt) 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. 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.
Count ($cnt) This operator is used to return or check the count of the values
in the given
multiselect type control/column. This operator is used in Multiselect type controls
viz. Multiselect, Checkbox, Keyword Box, etc.
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.
Regex ($regex) This operator is used to match a string/text or your data based on a regular expression. LOOKUP([BaseSheet.unique_id,BaseSheet.username],BaseSheet.username $regex “\d”)

The above example will fetch those username
that have number in it using the regular expression.