Commonly used formula functions and formula examples

Formula examples

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:

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.