Boolean Functions

Menu

Type Conversion
ToBoolean
Arguments (Text x)

Result Type Boolean

[+] more...

Explanation Converts the values "1", "true" or "yes" to true, other values to false. Case is ignored.

Examples ToBoolean("Yes") returns true
ToBoolean("0") returns false

[-] less...
ToBoolean
Arguments (Number n)

Result Type Boolean

[+] more...

Explanation Returns true if the Number n is a non-zero value, otherwise returns false.

Examples ToBoolean(1) returns true
ToBoolean(0) returns false
ToBoolean(null) returns false

[-] less...
ToNumber
Arguments (Boolean b)

Result Type Number

[+] more...

Explanation Returns 0 if b is false, 1 if b is true.

Examples ToNumber(false) returns 0
ToNumber(true) returns 1

[-] less...
ToText
Arguments (<any> x)

Result Type Text

[+] more...

Explanation Returns a Text value containing the print representation of the argument x.

Examples ToText(3.4) returns "3.4"
ToText(null) returns ""
ToText(true) returns "1"
ToText(Date(2000,12,16)) returns "12-16-2000"
...

[-] less...
Text
Begins
Arguments (Text u, Text v)

Result Type Boolean

[+] more...

Explanation Returns true if the text u begins with the text v, otherwise returns false.

Examples Begins("abcdef", "cd") returns false
Begins("abcdef", "abcd") returns true

[-] less...
Contains
Arguments (Text u, Text v)

Result Type Boolean

[+] more...

Explanation Returns true if v is contained in u, otherwise returns false.

Examples Contains("abcdef", "cd") returns true
Contains([Status], "open") returns true if the Status field contains the word "open"

[-] less...
Ends
Arguments (Text u, Text v)

Result Type Boolean

[+] more...

Explanation Returns true if the text u ends with the text v, otherwise returns false.

Examples Ends("abcdef", "cd") returns false
Ends("abcdef", "cdef") returns true

[-] less...
Dates
IsLeapDay
Arguments (Date d)

Result Type Boolean

[+] more...

Explanation Returns true if d is February 29.

Examples IsLeapDay([Date Due]) returns true if the value in the Date Due field falls on a leap day like 2/29/08.

[-] less...
IsLeapYear
Arguments (Date d)

Result Type Boolean

[+] more...

Explanation Returns true if the date d falls in a leap year.

Examples IsLeapYear([Release Date]) returns true if the date in the Release Date field falls in a leap year like 2008. If it occurs in a non leap year, like 2007, the result is false.

[-] less...
IsLeapYear
Arguments (Number y)

Result Type Boolean

[+] more...

Explanation Returns true if the year y is a leap year.

Examples IsLeapYear(2007) returns false.
IsLeapYear(2008) returns true.

[-] less...
IsWeekday
Arguments (Date d)

Result Type Boolean

[+] more...

Explanation Returns true if d is a weekday, otherwise false.

Examples IsWeekday([Deliver On]) returns true if the date in the Deliver On field is a weekday. If not, the result is false.

IsWeekday(ToDate("6/20/2003")) returns true

[-] less...
Aggregation
Count
Arguments (<any> x, ...)

Result Type Number

[+] more...

Explanation Counts the number of non-null arguments. For Text arguments, non-blanks are counted. For Boolean arguments, trues are counted. This function can also be used in the context of a Summary report where it will count the # of Non-null records for each grouping, if used to specify the field to check in a Calculated Column.

Examples Count ("", "abc", true, false, 53) returns 3

For the Summary Report example, a formula might look something like this:

...

[-] less...
Max
Arguments (<any> x, <any> y, ...)

Result Type <same type as x and y>

[+] more...

Explanation This function can take 2 or more arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments. Null values are ignored. For Numbers, returns the argument that is greatest. For Text, returns the argument that sorts last alphabetically. For Durations, returns the argument that is longest. For Dates returns the argument that is latest. For Date/Time, returns the argument that is latest. For TimeOfDays, returns the argument that is latest. For Booleans returns the argument that is largest, treating false as less than true.

Examples Max (Days(2), Weeks(1)) returns 1 week
Max (10, 20, 30, 40) returns 40
Max (10, null, 30) returns 30
Max (null, null, null) returns null

[-] less...
Max
Arguments (RecordListI, Number f)

Result Type Same type as argument

[+] more...

Explanation
This formula query function returns the maximum value in field ID f from record list I. f must be an ID of a numeric field. Null values are ignored.

This function can take arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments.

For Numbers, returns the argument that is greatest.
For Text, returns the argument that sorts last alphabetically.
For Durations, returns the argument that is longest.
For Dates returns the argument that is latest.
For Date/Time, returns the argument that is latest.
For TimeOfDays, returns the argument that is latest.
For Booleans returns the argument that is largest, treating false as less than true.

Examples Max (GetRecords("{6.GT.100}"), 6) returns the maximum value from records where the values in the field ID 6 are greater than 100.

[-] less...
Median
Arguments (<any> x, <any> y, ...)

Result Type Number

[+] more...

Explanation This function can take 2 or more arguments of a numeric or duration data type, as long as they are all the same type.
Returns the median of non-null arguments. For arguments with less than 1000 records, the exact median is returned. If an argument includes more than 1000 records, the median is approximated.

Examples Median (1, 3, 2) returns 2

[-] less...
Min
Arguments (<any> x, <any> y, ...)

Result Type <same type as x and y>

[+] more...

Explanation This function can take 2 or more arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments. Null values are ignored. For Numbers, returns the argument that is least. For Text, returns the argument that sorts first alphabetically. For Durations, returns the argument that is shortest. For Dates returns the argument that is earliest. For Date/Time, returns the argument that is earliest. For TimeOfDays, returns the argument that is earliest. For Booleans returns the argument that is smallest, treating false as less than true.

Examples Min (10, 20, 30, 40) returns 10
Min ([Date1], [Date2]) returns whichever date field's value is earlier
Min (Days(2), Weeks(1)) returns 2 days
in (10, null, 30) returns 10
Min (null, null, null) returns null

[-] less...
Min
Arguments (RecordListI, Number f)

Result Type Same type as argument

[+] more...

Explanation
This formula query function returns the minimum value in field ID f from record list I. f must be an ID of a numeric field. Null values are ignored.

This function can take arguments of any data type, as long as they are all the same type. The result is the same data type as the arguments.

For Numbers, returns the argument that is least.
For Text, returns the argument that sorts first alphabetically.
For Durations, returns the argument that is shortest.
For Dates returns the argument that is earliest.
For Date/Time, returns the argument that is earliest.
For TimeOfDays, returns the argument that is earliest.
For Booleans returns the argument that is smallest, treating false as less than true.

Examples Min (GetRecords("{6.GT.100}"), 6) returns the minimum value from records where the values in the field ID 6 are greater than 100.

[-] less...
Null Handling
IsNull
Arguments (<any> x)

Result Type Boolean

[+] more...

Explanation Null means that a field's value is undefined. In other words, no one has entered any data in that particular field. It's empty. Its value is null. The result of this function is true if x is null, otherwise false. The argument x may be of any data type (except text or boolean).

Examples IsNull([Start Date]) returns true if the field named Start Date is undefined or empty.
IsNull(3.4) returns false

[-] less...
Special
Case
Arguments (<any> x, <any> val1, <any> result1, ..., <any> else-result)

Result Type <same type as result1>

[+] more...

Explanation Case() is a variation of the If() function. If you want to test many conditions against a single field, use the Case() function instead of the If() function. QuickBase evaluates the value x and compares it to each of the values that follow (val1 and so on) sequentially. If the value X matches any value, QuickBase returns the corresponding result which lives behind the comma following the matched value. If value x is not equal to any of the values, QuickBase returns the else-result at the end of the formula. The else-result is optional. If omitted, QuickBase assumes it's null (empty). The value x may be of any data type, but all of the values must be of the same type as x.

Examples Case([Grade], "A", 100, "B", 90, null)

This formula says: If the value in the Grade field is A, then return 100. If the value in the Grade field is B, then return 90. Otherwise, return nothing (null).

[-] less...
GetFieldProperty
Arguments (Number fid, Text prop)

Result Type <Boolean, Text, or Number>

[+] more...

Explanation Returns the value of the field property prop, in the field fid, in the current table. The properties currently supported are "currencyFormat", "currencySymbol", "fieldType", "maxLength", "required", and "unique".

Examples GetFieldProperty(15,"required") returns true if field 15 in the current table is required, false if not.

[-] less...
If
Arguments (Boolean condition1, <any> result1, ..., <any> else-result)

Result Type <same type as result1>

[+] more...

Explanation If condition1 is true, returns result1, otherwise returns else-result. You can include additional condition/result pairs before the final else-result (as in the first example). QuickBase evaluates the conditions in sequence until one is found to be true, and then the corresponding result is returned. The else-result is optional. If omitted, QuickBase assumes it's null (or empty - a blank). All conditions must be of type Boolean (return a true or a false). Results may be of any type, but they must all be the same type.

Examples If([Grade]="A", 100, [Grade]="B", 90)

This formula says: if the value in the Grade field is A, then return 100. If the value in the Grade field is B, then return 90.

...

[-] less...
Includes
Arguments (UserList ul, UserList ul1,UserList ul2 ..)

Result Type Boolean

[+] more...

Explanation This function takes 2 or more list-user field types as arguments and returns true if the contents of all the the arguments together, except for the first, are included in the contents of first argument; false otherwise.

Examples Includes ([Assigned To] , [Manager], [Employee])

This will return true if all the users in the Manager field and Employee field are selected for Assigned To field.

[-] less...
IsUserEmail
Arguments (Text x)

Result Type Boolean

[+] more...

Explanation Returns true if x is the email address of the current user.

Examples IsUserEmail("john_smith@example.com") would return true if John Smith were accessing the table containing the formula field.

[-] less...
Binary Operators (act on multiple values)
!=
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Same as <>. Returns true if x is not equal to y, otherwise returns false. x and y must be the same type. As with most other functions, null argument values produce a null result, so it is not possible to test for null with this operator. To test for null, use IsNull() instead.

Examples (3 / 4 <> 0.75) returns false

[-] less...
&
Arguments <any> u, <any> v

Result Type Text

[+] more...

Explanation Returns a text value that is the concatenation of u and v. Use this operator to link strings of characters together in a formula text field. If u or v is not a text value, QuickBase automatically inserts the function ToText() to convert the value to text before concatenating it. Does not support User or UserList.

Examples "abc" & "def" returns "abcdef"
"abc" & 5 returns "abc5"
[First Name] & " " & [Last Name] concatenates the values in the First Name and Last Name field with a space in between them.

[-] less...
<
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Returns true if x is less than y, otherwise returns false. x and y must be the same type. For Numbers, the comparison is numerical. For Durations, the comparison is done using length of time represented. For Text, the comparison is done by alphabetical sort order. For Dates, Date/Time, and Time of Day values, the comparison is done by chronological order. For Booleans, false is less than true.

Examples 3 < 4 returns true
4 < 3 returns false
"abcdef" < "gh" returns true

[-] less...
<=
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Returns true if x is less than or equal to y, otherwise returns false. x and y must be the same type. For Numbers, the comparison is numerical. For Durations, the comparison is done using length of time represented. For Text, the comparison is done by alphabetical sort order. For Dates, Date/Time, and Time of Day values, the comparison is done by chronological order. For Booleans, false is less than true.

Examples 3 <= 4 returns true
"abcdef" <= "gh" returns true

[-] less...
<>
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Same as !=. Returns true if x is not equal to y, otherwise returns false. x and y must be the same type. As with most other functions, null argument values produce a null result, so it is not possible to test for null with this operator. To test for null, use IsNull() instead.

Examples (3 / 4 <> 0.75) returns false

[-] less...
=
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Returns true if x is equal to y, otherwise returns false. x and y must be the same type. As with most other functions, null argument values produce a null result, so it is not possible to test for null with this operator. To test for null, use IsNull() instead.

Examples (3 / 4 = 0.75) returns true

[-] less...
>
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Returns true if x is greater than y, otherwise returns false. x and y must be the same type. For Numbers, the comparison is numerical. For Durations, the comparison is done using length of time represented. For Text, the comparison is done by alphabetical sort order. For Dates, Date/Time, and Time of Day values, the comparison is done by chronological order. For Booleans, false is less than true.

Examples 3 > 4 returns false

[Actual Completion Date] >= [Projected Completion Date] returns true if the value in the the Actual Completion Date field is greater than the date in the Project Completion Date field.

...

[-] less...
>=
Arguments <any> x, <any> y

Result Type Boolean

[+] more...

Explanation Returns true if x is greater than or equal to y, otherwise returns false. x and y must be the same type. For Numbers, the comparison is numerical. For Durations, the comparison is done using length of time represented. For Text, the comparison is done by alphabetical sort order. For Dates, Date/Time, and Time of Day values, the comparison is done by chronological order. For Booleans, false is less than true.

Examples [Actual Completion Date] >= [Projected Completion Date] returns true if the value in the the Actual Completion Date field is greater than or equal to the date in the Project Completion Date field.

ToDate("Jan 1, 2000") >= ToDate("Feb 1, 1999") returns true

[-] less...
and
Arguments Boolean a, Boolean b

Result Type Boolean

[+] more...

Explanation Returns true if a and b are both true, otherwise returns false. Use this operator to link conditions together.

Examples (true and false) returns false

[Decision - Technical Lead]="Approved" and
[Decision - Project Manager]="Approved" and
...

[-] less...
or
Arguments Boolean a, Boolean b

Result Type Boolean

[+] more...

Explanation Returns true if either a or b is true, otherwise returns false.

Examples (true or false) returns true

[-] less...
Unary Operators (act on single values)
not
Arguments Boolean b

Result Type Boolean

[+] more...

Explanation Returns the logical negation of b (if b is true, returns false, otherwise returns true).

Examples (not true) returns false
(not not false) returns false
not isnull([Finish Date]) returns true if the Finish Date field has content--in other words, it's not null, or empty. (Read more about nulls at: https://www.quickbase.com/help/using_formulas_in_quickbase.html#null)

[-] less...
TextList
Contains
Arguments (TextList textList, Text textToSearchFor)

Result Type Boolean

[+] more...

Explanation Returns true if textToSearchFor is contained in textList, otherwise returns false. Comparison is not case sensitive.

Examples Contains([Skills Required], "Kung fu") returns true if the "Skills required" field has as one of its selected options "Kung fu"

[-] less...
UserList
Contains
Arguments Contains (UserList userList, User userToSearchFor)

Result Type Boolean

[+] more...

Explanation Returns true if userToSearchFor is contained in userList, otherwise returns false.

Examples Contains([Team members], [Assigned To]) returns true if the value of the "Assigned To" field appears in the "Team members" list

[-] less...

Quickbase Support Center

My apps
App home
Recently viewed

Search

TABLES REPORTS

Formula Functions Reference
Sample Formulas
Full site
Sign Out