Formula examples
Multiply 32 and 2.5. |
Formula - Numeric |
|
32 times 2.5 |
Determine net worth by subtracting liabilities from assets. |
Formula - Numeric |
|
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 |
|
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 |
|
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 |
|
Display whichever amount is less: the value in the Balance Due field or 25.00. |
Display a contact's full name. |
Formula - Text |
|
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 |
|
Display the date that is today plus 7 days. |
Discover if net worth of over $1 million. |
Formula - Checkbox |
|
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 |
|
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 |
|
Display whichever amount is less: 41, 23, or the value in the tax field. |
Total some specific values |
|
Add 12.5 to .5 to 3 and display the result, which is 16. |
Find the average of a few values |
|
Display the average of 12, 6 and 9. The result is 9. |
Get today's date |
|
Display today's date. This function doesn't require any arguments, but you still need to supply the parentheses. |
Using special functions
Formulas can contain any number of functions which perform different calculations. For a comprehensive list of Quickbase formula functions and what they do, see the Formula Functions Reference.
Tip: You can specify that formula fields must contain unique entries (that is, no two records may have the same value in that field). To do this, select the Require unique values checkbox within the formula field's properties page. This is helpful for creating autonumbering. Or create a Formula - Text type field that concatenates multiple fields to ensure that users don't enter duplicate records.
Note: Not all formula fields can require unique values. If your formula references Lookup, Date/Time, or Address fields, the formula field can't be unique because the values may change and no longer be unique. For example, if a date formula called Today displays the current date in a field, it can't be marked as unique, because it may not be unique tomorrow. Formulas that include Record ID also can't be marked as unique.
Setting conditions with the If() function
You can set conditions for your formulas. For example, you may want a total to appear on an invoice only if the order is complete. Or maybe you want a Status field to say "Completed" only if the Date Completed field has been filled out. To do this, use an If() function.
In an If() function, you describe a condition for Quickbase to examine and then you specify what the results should be depending on what Quickbase finds. You separate the condition and arguments from each other with commas. The basic syntax of an If() function is as follows:
If(condition, value if condition is true, value if condition is false)
This type of formula is also known as an "If-Then-Else" statement. If is the condition. Then is what Quickbase should do if the condition is met. Else is what Quickbase should do if the condition is not met.
For example, say you want your Quickbase app to show companies with a net worth greater than 1 million dollars. You could create a Formula - Checkbox field called Million, and use the following formula to populate it:
If([net worth] > 1000000, TRUE, FALSE)
This formula says: 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. However, you can also use a Boolean statement as a condition to return another value. For example, say you create a Formula - Text field instead of a Formula - Checkbox field. Then you could use this formula: If([Net Worth]>1000000, [Telephone Number], "not top sales priority")
which means: 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.
Some other examples of formulas that employ the If() function:
Calculate speed. |
Formula - Numeric |
|
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.) 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 |
|
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 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 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
The If() function is great for testing a single condition, but imagine that you want to test many conditions against a single field. For example, say you have a movie review application that contains a field called Rating, which asks viewers to pick a number from one to four. You want to translate this score into a one-word review. You could accomplish this by inserting multiple If() functions in a formula, but there's a better way. The Case() function lets you test many conditions against a single field. The solution for your one-word review field would be to create a Formula - Text type field and design it with the following formula:
Case([rating],1,"poor"
,2,"fair"
,3,"good"
,4,"fantastic" )
This formula says: 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: If you want to keep track of what the different parts of your formula do, make a note to yourself and/or your colleagues. You can include comments in formulas using double slashes (//). Quickbase interprets everything from the double slashes to the end of the line as a comment, not as part of the formula. Here's an example of a formula with comments:
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
The SearchAndReplace
function makes it easier to manipulate text data. In addition to replacing all appearances of one word with another word, you can also perform these tasks:
Use SearchAndReplace
to remove all appearances of a word or phrase from a piece of text, by using a pair of empty quotes. This is useful for filtering out noise in your data. For example, some companies sync in email messages into their Quickbase apps. You could use SearchAndReplace
to remove HTML tags from the body of an email message. This formula would remove all appearances of the term <p>
from the Email Body field:
SearchAndReplace([Email Body],"<p>","")
You can use the SearchAndReplace
function several times in a row to replace several words or phrases in the same piece of text. For example, this formula would search the Notes field and replace those three acronyms with the phrases they represent:
SearchAndReplace(SearchAndReplace(SearchAndReplace([Notes],"GTM","Go-to Market"),"KPI","Key Performance Indicator"),"YoY Growth","Year-over-Year Growth")
You can use SearchAndReplace
with other functions like Left
and Right
. In this example, you want to exclude all text in a field up to the first appearance of the word “Approved,” so you can examine an approval log and see if there are multiple approvals. Using just the Left/Right/NotLeft/NotRight
functions, this wouldn't be possible since they only operate on a single character at a time. What you can do is replace a word with a single character and then use a function such as NotLeft
. First, select a character which does not appear in your source field. In our example, we know that "#" doesn’t appear anywhere in the source data. We use this checkbox
formula to determine if the word "Approved" appears more than once:
If(Contains(NotLeft(SearchAndReplace([Approval Log],"Approved","#"),"#"),"#") = false,true,false)
These are just a few simple examples of function calls. View the complete list of Quickbase function calls in the Formula Functions Reference.