Using Formulas in Quickbase
Formula Building Blocks: field references, literals, operators, function calls and arguments
What You Can Do with Formulas: Using Special Functions
Setting Conditions with the If() Function
Using the SearchAndReplace function
Introduction to formulas
As your application grows, you may find that you want fields to work together to affect or even generate content in another field. For example, if each record in a table is an invoice, you may want the total field to show the sum of the values in two other fields: subtotal and tax.
To do this, insert a formula in the total field
that tells Quickbase: "Add the value in subtotal
to the value in tax and display it here." This formula
would be: [subtotal]+[tax]. Note that the formula does not contain an equal sign or refer to the
result. You only need to enter the formula once, and it will run for each record in the table.
You can use formulas to calculate mathematical amounts or for many other tasks. In the example above, you could set the formula so that invoice totals only appear if a staff member has made an entry in the Job Completed Date field. Or you could include a formula that automatically populates your Salesperson field based on a selection a user makes in the Territory field.
Formula Functions Reference
You can see a guide to all formulas and operators in the Formula Functions Reference.
Formula building blocks
You construct a formula using some or all of the following building blocks:
-
Field references retrieve values from a specific field in the record. When you insert a field reference in a formula field, you're telling Quickbase "Take the value from the specified field in this record and display it here." or "Take the value and use it in this calculation." To write a field reference, enclose the field name in square brackets, like this:
[Manager]or[Annual Salary]. Quickbase will replace the field reference with the actual value it finds in that field. For example, a formula like[First Name] & " " & [Last Name]strings together the values found in the First Name and Last Name field within each record. The result might be "John Smith" or "Sara Navarro," for example.You use a field reference to call an application variable too. (What's an application variable and why would you use one?)
Note: If you change the name of a field you use in a formula, Quickbase changes the field name wherever it occurs, including formula references.
-
Literals are values that are meant to be exactly what they are. For example, the formula
[subtotal] + 23.5means add the value from the subtotal field to 23.5 (literally, 23.5). Literals can be text too. To set a text literal apart from the rest of your formula, you enclose it within quotation marks:"This is a text literal."Everything between the quotation marks appears as part of the result.If the text you want to display includes a quotation mark, include a backslash to indicate that the character that follows it is part of the literal and not a delimiter: "The \" character is part of this literal.”
You can also use the backslash if you want to include an open or close square bracket [] in your literal. (Usually a square bracket starts or ends a field reference.) Since the backslash itself is a special character, you also need to precede it with a backslash when you want to include one in a text literal: “The \” and the \\ are both special characters.”
-
Operators tell Quickbase what you want to do with the values (be they field references or literals) in your formula. Operators are special symbols like + and * that act upon one or two values to return a new value. There are two types of operators:
-
Unary operators act on a single value, and are written like
-4or+5ornot true. (See a list of unary operators.) -
Binary operators act on two values, and are written like
3 - 4, or23.4 - 0.4. (See a list of binary operators.)
Note: Quickbase reads operators in a predetermined order that's not necessarily left to right. To learn more read about operator precedence.
-
-
Function calls make things happen. A function performs a specific operation on some values that you specify and generates a new value. To compose a function call, you type the function name, followed by a set of parentheses. The parentheses contain the information that the function needs to perform its magic. These values are called arguments.
-
Function Arguments are separate pieces of information within a function call that tell the function what values to act on or produce. Arguments appear within the parentheses that follow a call, with each argument separated by a comma like this: (Argument 1, Argument 2, Argument 3, and so on). Different function calls accept different numbers of and types of arguments. Arguments can be literals, field references or even another function call. The order in which you list arguments is very important. Some examples of a function call with arguments appear below:
You want to
Function
description
Find the lesser of two values
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
Min(41,23,[tax])Display whichever amount is less: 41, 23, or the value in the tax field.
Total some specific values
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
Average(12, 6, 9)Display the average of 12, 6 and 9. The result is 9.
Get today's date
Today()
Display today's date. This function doesn't require any arguments, but you still need to supply the parentheses.
These are just a few simple examples of function calls. View the complete list of Quickbase function calls in the Formula Functions Reference.
Creating a formula field
To create a formula field:
Step 1: Add a field
First, you start the process of adding a new field. (Read Step 2 to understand what formula field type to select.)
Step 2: Select a formula field type
The rules of formula construction differ for various types of data. It's important to understand what type of fields are in your formula. If you combine field types incorrectly, your formula won't work. Any fields that participate in your formula have a data type associated with them. The result of your formula also has a data type, as does the field in which you place the formula. All these data types and the operators you use on them must be compatible or Quickbase displays an "incorrect type" formula error message. (Read about troubleshooting formula errors.)
Formulas can be used to perform calculations on many different types of values, including numeric values, text values, dates, and durations. For each data type, only specific operations make sense. For example, multiplication makes sense on numeric values, but not on text values. Concatenation (linking values together in a chain) is an operation that makes sense on text values, but not dates. Some operations produce the same data type that they act on, while others produce a new data type. For instance, when you subtract one date from another, Quickbase returns a duration, not a date.
When you're creating a formula field, the first step is to tell Quickbase what type of data the formula will produce, by selecting a field type. The field type you select must match the type of data your formula will produce. For example, if your field will contain the result of a mathematical calculation, it must be a Formula - Numeric field. If the result of your formula will be a date, it must be a Formula - Date field.
Formula field types appear at the bottom of the field type list.
You can choose from the following field types:
-
Formula - Text. Use this type of field if your formula results in text or contains literals with alphanumeric characters. When you use a Text literal in a formula, you must set it apart from the rest of the formula by enclosing it in double quotes like this: “Fred”, “Barney”, “Wilma and Betty”.
-
Formula - Numeric. This type of field can only accept numeric results. You'd use this format to display the results of mathematical calculations. Numeric values can be integers or decimals, positive or negative. Your formula can use actual numbers, like 4, -3, +78.2, and -0.4. You could also tell Quickbase to use a number it finds in a specific field. You do this using a field reference like [tax], for example.
-
Formula - Date. If your formula results in a date, you'll use this field type. A date result represents a specific day in the past, present or future. Dates do not have a literal form in formulas. However, if you have a date that's in the form of some text (this would be a Text literal) you can convert it into a date value, using the ToDate() function, which looks like this:
ToDate("Jan 30, 2015"), orToDate("1/30/15"). Today’s date can be obtained by calling the built-in function Today(). -
Formula - Date / Time. Use this field type if your formula results in a date and time of day. If you want to "timestamp" data-driven events like a change in status, for example, use a Date/Time field in combination with dynamic form rules. (
Read
how.) -
Formula - Time of Day. Use this field type if your formula results in a time of day.
-
Formula - Duration. Use this type of field to represent a span of time. Durations can be created by subtracting two Dates, two Timestamps, two Time Of Days, or as the result of certain functions like
Hours(2)orDays(1). Durations can be either positive or negative. Negative durations result from subtracting a later Date, Timestamp, or Time Of Day from an earlier one. -
Formula - Checkbox. Use this type of field if your formula will have a Boolean result.
What's a Boolean result? Boolean formulas always produce one of two values—true or false. Boolean values result from comparison operations, like
4<5. For example, say you pose the question in your formula "Is the revenue field greater than the expenses field?" In the Quickbase formula language this question would read:[revenue] > [expenses]. This statement is either true or it is false. You use Boolean results to turn on or off a checkbox or as a condition for an If() function. (You'll read about the If() function later in this topic.) -
Formula - Phone Number. Insert a formula in this type of field to draw an area code and telephone number together.
-
Formula - Email Address. If your table contains names in one field and email domains in another, you can use a Formula - Email Address field to link them together. Quickbase displays the result as a mailto link, which automatically generates an email when a viewer clicks on it.
-
Formula - User. Use this type of field if your formula will result in a User value.
-
Formula - List-user. Use this type of field if your formula will result in a list of User values.
-
Formula - URL. Use this type of field to have Quickbase create a URL that takes a viewer to a specific Web page. Formula - URL fields let you combine values from fields to create URLs. For example, if your app contains a comprehensive list of orchid types, you may want each record to contain a link to the related picture on your Web site. Or if a field contains your Web site address and another field contains the picture ID for the related image, you could compose a formula that combines them, creating a link in each record that leads to a different image on your Web site.
-
Formula - Work Date. For certain calculations, a Date field doesn't suffice. For example, if your application uses predecessors (in other words, tracks the timing of tasks when one task depends upon another), you'll need to use a Work Date field instead of a regular Date field. Work Date fields let you measure dates in fractional amounts. For example, say that a task begins on one date (recorded in a field called Start Date) and takes 2.5 days (tracked in a field called Duration). You want to create a third field that takes those values and calculates the Finish Date. To do so, you'd need a Formula - Work Date field. Regular Date fields can't calculate parts of days. If you need to add fractional amounts, such as .5 days, you'll need to use a Work Date field. This feature is important in its cumulative effect. For example, say the task takes longer than expected and you add yet another .5 days to it. The Work Date field then advances the date one day, as you'd expect, when a regular Date field would not. Work Date field types are only available in applications that already include a Predecessor field.
Note: Some special functions let you turn one type of data into another type of data. For example, if you have a string of text that reads "September 2, 2017" you can turn this text into a date value using a ToDate() function. Or you can convert Date fields to Word Date fields using a ToWorkdate() function. (These functions and more are described in the Quickbase Formula Functions Reference.)
Step 3: Write a basic formula
Once you've created your formula field and specified its type, you're ready to compose the formula. Access the field's Properties page by clicking the field's name. (Learn other ways to access a field's properties.) The Properties page contains a section called Formula, which provides a box for you to enter your formula:
Compose your field's formula in the Formula box. When you click to place your cursor in the box, you'll see the Fields & Functions dropdown to the box's right. If you don't see a Formula box in the field's properties, you probably chose a regular field type instead of a Formula field type in Step 2. To fix this, click the Change Type button and select a formula type.
Note: Quickbase provides line numbering and highlights the names of the fields and formula functions. To help keep track of parentheses or other formula elements, you can enter line breaks and extra spaces to make the formula easier to read. Quickbase ignores this additional white space. Click the expand icon to expand the formula editor if you need more space.
The Quickbase formula language uses algebraic notation. An algebraic statement consists of values (like
a number or a field reference) with operators (like + or -) between them.
You can use parentheses to change the order of evaluation. For example,
the formula 5 * 3 - 1 doesn't produce the same result as 5 * (3-1). In
the first formula, you'd multiply 5 times 3, which equals 15. Then subtract
one to get the final result: 14. In the latter example, you'd begin within
the parentheses. Three minus one equals two. Take this result, 2, and
process the rest of the formula by multiplying it by 5. The result of
the second formula is 10.
Here are some examples of simple formulas:
|
You want to... |
Use field type: |
Formula |
Explanation in English |
|---|---|---|---|
|
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). |
No matter how complex or long a formula, the basic rules still apply. Quickbase always starts reading a formula within the deepest set of parentheses that it finds. In other words, when sets of parentheses are nested within other sets, Quickbase starts from the inside out. Open parenthesis: ( requires a closing parenthesis: ). If it's missing, Quickbase returns a syntax error. Syntax is grammar for formulas.
Note: If you place your cursor to the right of an opening or closing parenthesis, Quickbase will 1) draw a gray frame around the character, and 2) draw a gray frame around the matching parenthesis, if there is one.
Each character in your formula means something. For example, square brackets [] tell Quickbase that the value between them is a field reference. If one is missing, you'll get a syntax error. If a field name is misspelled, this will also lead to a syntax error. To select a field or function name, place your cursor in the Formula box and use the Fields & Functionsdropdown to add functions and insert field references.
To add a function:
-
Click in the Formula box to place your cursor exactly where the function should appear within the formula.
-
Click the Fields & Functions dropdown to the right of the box.
-
Within the menu that displays, choose Select a function.
The Quickbase Formula Functions dialog displays.
Formula Functions dialog. Not sure which function to choose? Click a function on the left, and information on the function
displays on the right. If you want to limit the list to a particular type of function—like those whose results are dates, say—
you can filter the list. To do so, click the All Functions dropdown and select the type you want to list. -
Select the function.
Click a function on the left to highlight it. Then click Insert. Quickbase inserts the function where your cursor sits.
-
Complete the function.
When Quickbase inserts the function, it includes placeholders for each argument that tell you what data types go in each spot. Replace these with field references or values. For example, when Quickbase inserts the Contains() function you see:
Contains (Text, Text). Replace the two "Text" arguments with actual values or references, so that the function reads something like:Contains([Job Title], "Manager").
To insert a field reference:
You can type in functions or field reference, or:
-
Click in the Formula box to place your cursor exactly where the field reference should appear within the formula.
-
Click the Fields & Functions dropdown to the right of the box.
-
Select a field from the list that displays.
What fields appear here? Fields in the same table as the formula field and any fields from a related table. (Read about relationships.)
What you can do with formulas: 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)
Tip: 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 says in English: 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:
|
You want to... |
Use field type: |
Formula |
Explanation |
|---|---|---|---|
|
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: 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
)
Working with nulls
Most fields can have a special value called the null value. 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.
Note: Checkbox and Text fields are never null. A Boolean value can only be true or false. For example, a Checkbox field is either On or Off. And Quickbase interprets an empty Text field ("") as a text string that happens to have zero characters, not as an undefined (null) value.
The null value is very useful in formulas. For instance, you might want
an invoice to total only if the Delivered on field has
been filled out. In this case you'd create a formula in the total
field that tells Quickbase: "If someone's entered data in the Delivered
on Date field (in other words the field is not null), then add
subtotal and tax. Translated into an
actual formula, this would appear as: If(not IsNull([Delivered
on]),[subtotal] + [tax],null). In English this formula says: If
the Delivered on date field is not null (in other words
it contains a value), then take the value in the subtotal
field and add it to the value in the tax field. Otherwise,
leave the field empty (null).
You can't use all the functions and operators when you work with null values.
Not all functions can handle an undefined value. For example you could
never use [A Field]=null. The equals operator requires information. Undefined
values provide no information. The equals operator could handle a zero
(which is numeric), but not a null. Only a few specific functions
accept null as an argument. For example, the IsNull() function
returns True if the field is null and False if it's not. The Nz()
function is special – it returns zero
if the field is null; otherwise it returns the value of the field. This
option is especially useful if you need to use the field in a mathematical
calculation (see an example of this in the table below).
Tip: Say you don't want to use the Nz() function, but you do want Quickbase to treat a null in your numeric field as a zero, so you can use in calculations. To set this up, access the field's properties page. Turn on the Treat blank as zero in calculations checkbox and save your changes. For Numeric fields, Quickbase actually turns this setting on automatically.
Some formula examples of null functions in action:
|
You want to... |
Formula |
Formula in English |
|---|---|---|
|
Get and display the value from the Temperature field, but if that field is empty (or null) use 98.6 instead. |
|
If the Temperature field is null, then display the result 98.6. Otherwise, display the value from the Temperature field. |
|
Add up the number of hours worked in a week. |
|
Return the value in the Mon field. If the Mon field is empty (null) then return zero. Add that to the value in the Tues field. If the Tues field is empty (null) then return zero. Add that to the value in the Wed field, and so on. Note: You'd use Nz() here instead of IsNull(). To add these values together, Quickbase needs the result to be a number. Nz() generates a zero for a null, which the program can use in the calculation. |
|
Calculate the Revenue field, only if your staff has entered a date in the Submitted for Billing field. Tip: To return a result where a value is not null, just add NOT in front of the IsNull() function. |
|
If someone's entered a value in the Submitted for Billing field (in other words, it's not null), then display the value from the Revenue Forecast field.
|
Note: If a formula that includes a null function isn't working, access the field's properties page and turn off the Treat blank as zero in calculations checkbox.
Using formulas in a report
Formulas can also help you hone a report to get exactly those records you want. You accomplish this by adding a custom formula column to the report. Read how.
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)
