Using Formulas in QuickBase

Introduction to Formulas

Formula Building Blocks: field references, literals, operators, function calls and arguments

Creating a Formula Field

Select a Formula Field Type

Write a Basic Formula

What You Can Do with Formulas: Using Special Functions

 Setting Conditions with the If() Function

 Setting Multiple Conditions with the Case() Function

 Working with Nulls

Using Formulas in a Report

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, imagine that each record in one of your tables is an invoice. Say you want the total field to show the sum of the values in two other fields: subtotal and tax. If you were doing this manually on a paper invoice, you'd take the amount in subtotal and add it to the amount in tax and enter the result in total.

In QuickBase you'd do the same thing, or rather QuickBase does the same thing. You just need to instruct the program to perform this calculation. You do so by inserting a formula where you want the result to appear. So, you'd enter 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. The formula merely asks the question "What is the subtotal plus the tax?" QuickBase generates the answer and displays it in the formula field called total. This process takes place within every record in the table. So if you have 25 records, QuickBase runs the formula for each record, generating the value for the total field in that record.

You can use formulas to calculate mathematical amounts, as in the example. But that's just the tip of the iceberg. Formulas can do much more. For example, say that in that same invoice you only want QuickBase to generate a total if a staff member has made an entry in the Job Completed Date field. No problem. Or perhaps you'd like QuickBase to automatically populate your Salesperson field based on a selection a user makes in the Territory field.

This topic tells you how to perform these feats and do even more. Read on to learn about what goes into a formula, then see how to construct your own.

Top

Formula building blocks

You construct a formula using some or all of the following building blocks:

These are just a few simple examples of function calls. View the complete list of QuickBase function calls.

Top

Creating a formula field

So how do you create a formula field? Follow the three steps detailed below: You'll add a field, select the desired formula field type. Then, access this field's properties page and write your formula.

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. That's why it's very important to understand what type of fields are players 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:

Note: Some special functions let you turn one type of data into another type of data. For example, say you had the following string of text: "March 7, 2014" You know this text represents a date, but QuickBase doesn't because it's in text format. So, you must turn this text into a date value. You can do so using the ToDate() function. The formula ToDate("March 7, 2014") does the trick. Or, say you have values in Date fields, but you need the result of your formula to work with predecessors. In that case, you'd need to convert the date value into a work date value. The handy ToWorkdate() function would take care of it. (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 itself. To do so, you must access the field's Properties page. (If you're in the field list, click 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 (you'll read how to use this tool in a minute).
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.

Tip: 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. If you find the Formula box in QuickBase is too small, type your formula out in a text editor (Notepad, TextEdit, or similar), then paste it into the Formula box.

The QuickBase formula language uses algebraic notation. This is just a fancy term for the basic rules of expression you learned in your first math class. 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 * 2.5

32 times 2.5

Figure out what each contact in your table is worth. Subtract liabilities from assets.

Formula - Numeric

[Assets] – [Liabilities]

Take the value in the Assets field and subtract from it the value in the Liabilities field.

Figure out what each contact in your table is worth after they get the 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.

Find which contacts are millionaires.

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).

No matter how complex or long a formula grows, 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. Remember that an open parenthesis: ( requires a closing parenthesis: ). If it's missing, QuickBase returns a syntax error. Syntax is grammar for formulas, and QuickBase is an unforgiving grammarian. If you don't express yourself precisely, QuickBase doesn't know what you're asking for.

You need to watch more than your parentheses. Each character in your formula means something to QuickBase. 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 you spell the name of the field wrong, you'll get an error too.

Formulas consist mostly of functions and field references. Because it's hard to remember the exact names of all your fields and the syntax rules for each function, QuickBase helps you out. When you place your cursor in the Formula box, QuickBase displays a Fields & Functions dropdown to the right of the box (refer back to the image above). Use it to add functions and insert field references.

To add a function:
  1. Click in the Formula box to place your cursor exactly where the function should appear within the formula.

  2. Click the Fields & Functions dropdown to the right of the box.

  3. 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.

  4. Select the function.

    Click a function on the left to highlight it. Then click Insert. QuickBase inserts the function where your cursor sits.

  5. 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:
  1. Click in the Formula box to place your cursor exactly where the field reference should appear within the formula.

  2. Click the Fields & Functions dropdown to the right of the box.

  3. 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.)

Tip: If you have an excellent memory and prefer typing, you can also just type in functions or field references.

Top

What you can do with formulas: Using special functions

Formulas can contain any number of functions which perform different calculations. Some of these functions are explained and shown in action below. For a comprehensive list of QuickBase formula functions and what they do, see the Formula Functions Reference. Meanwhile, read on to learn how to solve some specific problems with a QuickBase formula.

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 so, just turn on the Require unique values checkbox within the formula field's properties page. This is handy for creating autonumbering. Or create a Formula - Text type field that concatenates multiple fields to ensure that users don't enter duplicate records. (Please note that not all formula fields can be unique. If your formula references Lookup or User fields, QuickBase won't let you make it unique.)

Setting conditions with the If() function

IF you had the money, you could buy your favorite Caribbean island. IF NOT, you'll have to settle for a brief stay in a hotel room. Life's full of conditions. This is true of your QuickBase data as well. For example, say you 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 handle situations like this, employ the versatile 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: Formula buffs also refer to this 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 application to let you know if any of your contacts are millionaires. To call out millionaires, you could create a Formula - Checkbox field called Millionaire?, 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 Millionaire 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], "poor sales prospect") 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 poor sales prospect.

Some other examples of formulas that employ the If() function:

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.)

Tip: You don't actually need to add the null at the end, QuickBase would default to a null argument automatically, but you can if you want.

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.

Want 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

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,"atrocious"
,2,"passable"
,3,"good"
,4,"fantastic" )

This formula says: If the value in the rating field is 1, display the word atrocious. If the value in the rating field is 2, display the word passable, 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 recognizes an empty Text field ("") as a text string that happens to have zero characters, not as an undefined, or 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. No problem. 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 (IsNull([Temperature]), 98.6, [Temperature])

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.

Nz([Mon]) + Nz([Tues] + Nz([Wed]) + Nz([Thurs]) + Nz([Fri])

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(not IsNull([Submitted for Billing]),[Revenue Forecast])

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.

Top

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.

Related Topics:

 

Return to top   

© 1999-2014   Intuit Inc.  All rights reserved.  Legal Notices.