Commonly used formula functions and formula examples – Quickbase Help

Formula examples

You want to	Use field type	Formula	Explanation
Multiply 32 and 2.5.	Formula - Numeric	`32 * 2.5`	32 times 2.5
Determine net worth by subtracting liabilities from assets.	Formula - Numeric	`[Assets] – [Liabilities]`	Take the value in the Assets field and subtract from it the value in the Liabilities field.
Determine net worth after receiving a standard tax refund.	Formula - Numeric	`([Assets] + 3000) - [Liabilities]`	Take the value in the Assets field and add 3000. Then take the result and subtract from it the value in the Liabilities field.
Calculate the area of a circle whose radius is in the field named Radius.	Formula - Numeric	`[Radius] * [Radius] * 3.14159`	Take the value in the Radius field and multiply it by the value in the Radius field. Then multiply by 3.14159.
Calculate the minimum payment.	Formula - Numeric	`Min([Balance Due], 25.00)`	Display whichever amount is less: the value in the Balance Due field or 25.00.
Display a contact's full name.	Formula - Text	`[First Name] & " " & [Last Name]`	Display the value in the First Name field. Display a space. Display the value in the Last Name field. Note: To create a space between the names, this formula inserts a text literal. Quickbase displays whatever characters appear between a set of double quotes.
Calculate the date one week from today.	Formula - Date	`Today() + Days(7)`	Display the date that is today plus 7 days.
Discover if net worth of over $1 million.	Formula - Checkbox	`[Net Worth] > 1000000`	Display a true result if the value in the Net Worth field is greater than 1,000,000. Note: This is a Boolean statement, which only returns a true or false. This result only applies to a checkbox field which is either ON (true) or OFF (false).
Find the lesser of two values	Formula - Numeric	`Min(41,23)`	Display whichever amount is less: 41 or 23. The result is 23.
Find the lesser of two values, where one of those values comes from a field in your Quickbase application	Formula - Numeric	`Min(41,23,[tax])`	Display whichever amount is less: 41, 23, or the value in the tax field.
Total some specific values	Formula - Numeric	`Sum(12.5, 0.5, 3)`	Add 12.5 to .5 to 3 and display the result, which is 16.
Find the average of a few values	Formula - Numeric	`Average(12, 6, 9)`	Display the average of 12, 6 and 9. The result is 9.
Find the median of numbers or a duration	Formula - Numeric Formula - Duration	`Median(1, 3, 2)`	Display the median of 1, 3, 2. The result is 2.
Get today's date	Formula - Date	`Today()`	Display today's date. This function doesn't require any arguments, but you still need to supply the parentheses.

Type conversion formulas

Use type conversion functions to transfer data from one data type to another.

You want to	Use field type	Formula	Explanation
Convert a text string to a date	Formula - Date	`ToDate("Jan 30, 2021")`	Converts "Jan 30, 2021," which is a text literal to a date value
Convert text to a boolean value	Formula - Checkbox	`ToBoolean("Yes")`	Converts "Yes," which is a text literal to the Boolean value true
Convert date to text value	Formula - Date Formula Date / Time	`ToFormattedText(Date(2021,01,30),"MMDDYYY")`	Converts a date value to formatted text, in this case 01-30-2021
Convert text to a number	Formula - Numeric	`ToNumber("-12.3")`	Converts the text value -12.3 to a numerical value.
Convert a date field to a work date field	Formula - Work Date	`ToWorkDate([Completion Date])`	Converts the values in the Completion Date field to work dates
Format a date to ISO8601	Formula - Text	`ToFormattedText(01-01-1970,ISO8601) = 1970-01-01T00:00:00-05:00`	Format 01-01-1970 to ISO8601. ISO8601 is an international standard used in many systems, including our RESTful APIs.
Format a date to unix time	Formula - Date / time, Formula - Date	`ToUnixTime(01-01-1970) = 18000`	Output the numeric unix time of 01-01-1970. Result will include milliseconds.

Setting conditions with the If() function

You can set conditions for your formulas.

For example, you may want

Total to appear on an invoice only if the order is complete.
Status field to say "Completed" only if the Date Completed field has been filled out.

To do this, use an If() function.

When using an If() function:

Describe a condition for Quickbase to evaluate
Specify what to do if the condition is met
Specify what to do if the condition is not met
Separate the condition and arguments from each other with commas

This is the basic syntax of an If() function:

If(condition, value if condition is true, value if condition is false)

If() function example - Boolean statement

You want your Quickbase app to show companies with a net worth greater than 1 million dollars.

Create a Formula - Checkbox field called Million, and enter the following formula:

If([net worth] > 1000000, TRUE, FALSE)

Formula explanation:

If the value in the net worth field is greater than 1,000,000
then turn ON (true) the Million checkbox
Otherwise, turn it OFF (false)

This is a Boolean statement, which only returns True or False.

If() function example - text field

Create a Formula - Text field.

Enter the following formula:

If([Net Worth]>1000000, [Telephone Number], "not top sales priority")

Formula explanation:

If the Net Worth field is greater than 1,000,000
then display the value from the Telephone Number field
If not, then display the text not top sales priority.

More If() examples

You want to	Use field type	Formula	Explanation
Calculate speed.	Formula - Numeric	`If ([Time] > 0, [Distance]/[Time], null)`	If the value in the time field is greater than zero, then display the value in the distance field divided by the value in the time field. Otherwise, leave the field empty. (An empty field is called a null [Link to best practices article].) Tip: You don't need to add the null at the end, as Quickbase defaults to a null argument automatically.
Automatically complete the Territory field, based on who the salesperson is.	Formula - Text	`If([Salesperson]=ToUser("baker@example.com"), "Western", "Eastern")`	Take the email address baker@example.com and convert it to the user value connected with that email account (you can use a user name instead of an email address). If the value in the Salesperson field is that user, then display the word Western, otherwise, display the word Eastern. Tip: Form rules can also automatically populate fields based on other values. To set this up for multiple salespeople and territories, use the Case() function instead. Read how in the next section.
Display an invoice total only if the order is complete.	Formula - Numeric	`If([Order Complete]=TRUE, [SUBTOTAL] + [TAX], null)`	If the Order Complete checkbox is on, then add the value in the subtotal field to the value in the tax field and display it. If not, then leave the field empty (or null).
Automatically set the Status field to "Complete," when a staff member enters a date in the Completion Date field.	Formula - Text	`If(isnull([Completion Date]), "Pending", "Complete")`	If no one's entered a value in the Completion Date field (in other words, that field is null) then display the word Pending. If not, display the word Complete.

Setting multiple conditions with the Case() function

Use the Case() function to evaluate multiple conditions against a single field.

Case() example - turn numeric rating to text

A movie review application contains a field called Rating. Viewers pick a number from one to four.
You want to translate this score into a one-word review.
Create a Formula-Text field and use the Case() function to enter multiple conditions:

Case([rating],1,"poor"
,2,"fair"
,3,"good"
,4,"fantastic" )

Formula explanation

If the value in the rating field is 1, display the word poor
If the value in the rating field is 2, display the word fair, and so on.

Tip: Use double slashes (//) to add comments about what each part of your formula does

If ( Abs([x]) < 5,        //test the value
    "Less than 5",         //return one result
    "Not less than 5"      //otherwise return another result
)

Using the SearchAndReplace function

With theSearchAndReplace function, you can:

Replace words with other words
Remove words

Example - empty quotes to remove a word or phrase

To filter out noise in your data, like removing HTML tags from email messages that have been synced to a Quickbase app:

SearchAndReplace([Email Body],"<p>","")

This formula removes all appearances of <p> from the body of the email.

Example - SearchAndReplace several times in a row

Replace several words or phrases in the same piece of text.

For example,

Create a formula to search the Notes field of an app and replace acronyms with the phrases they represent:

SearchAndReplace(SearchAndReplace(SearchAndReplace([Notes],"GTM","Go-to Market"),"KPI","Key Performance Indicator"),"YoY Growth","Year-over-Year Growth")

Example - SearchAndReplace with other functions

Use SearchAndReplacewith other functions like Left and Right to exclude text in a field.