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 this 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 within each record, generating 25 totals—one for each
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. By
the way, you're not limited to using formulas within a field. You can
also use them to help you design custom reports or queries. Read on to
learn about what goes into a formula, then see how to construct your own.

Tip: You can also use formulas
to color the background of different rows in a table report. Learn
how.

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 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]. When you do so, QuickBase
replaces 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 "Betty Boop"
for example. You use a field reference to call an application variable
too. (What's an application variable and
why would you use one?)

FAQ: What happens if I change
the name of a field I've used in a formula?
Don't worry about it! QuickBase can roll with the punches (and edits).
If you change the name of a field, the program automatically makes
the same change wherever that name appears in your application, including
formula references.

Literals are values
that are meant to be exactly what they are. For example, the formula
[subtotal] + 23.5 means 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.

Using quotation marks within
a text literal
What happens if the text you want to display includes a quotation mark?
This is a problem, since QuickBase always reads a quotation mark as
the delimiter of a text literal. Thankfully, you have a trick up your
sleeve. The formula language features a special character, the backslash,
which indicates that the character that follows it is part of the
literal and not a delimiter. Example: “The \" character is part
of this literal.” You can also use the backslash if you want to include
a [] 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. Example: “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:

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

Explanation
in English

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.

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 formula field type. Then, access
this field's properties screen 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 result
your formula will produce. For example, if your field will contain
the result of a mathematical calculation, it must be a Formula
- Numeric type field. If the result of your formula will be a
date, it must be a Formula - Date type field.

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. These values are called Number
literals because QuickBase should use the actual number value
presented. 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,
1999”), or ToDate(“1/30/99”). Today’s date can be obtained
by calling the built-in function Today().

Formula - Time of Day.
Displays a time of day.

Timestamps.
If you want to "timestamp" data-driven events like
a change in status, for example, use a Date/Time type
field in combination with dynamic form rules. (Read
how.)

Formula - Duration.
Use this type of field to represent a span of time. Durations
can be created by subtracting two Dates, two Timestamps, two TimeOfDays,
or as the result of certain functions like Hours(2) or
Days(1). Durations can be either positive or negative.
Negative durations result from subtracting a later Date, Timestamp,
or TimeOfDay from an earlier one.

Formula - Checkbox.
Use this type of field for 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 you a 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 hyperlink,
which automatically generates an email when a viewer clicks on
it.

Formula - URL.
Use this type of field to have QuickBase create a URL that takes
a viewer to a specific Web page. Why would you need a formula
URL field as opposed to a regular URL field? Imagine that your
application contains a comprehensive list of orchid types and
you want each record to contain a link to the related picture
on your Web site. Say that one field in your table 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. In other words, formula URL fields let you combine
values from fields to create URLs.

Formula - Work Date.
For certain calculations, a date field doesn't suffice. For example,
if your application uses predecessors
(in other words, the timing of one task depends upon the timing
of another), you'll need to use a work date field instead of a
regular date field. What's the difference? 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 Word
Date - Formula type field. Regular date fields can't calculate
parts of days. So, if you were to add a fractional amount, like
.5, to a regular date field, it won't register. However, when
you add .5 to a work date field, the addition sticks. 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. A regular date field would not. Work Date field types
are only available in applications that already include predecessors.

Formula - Date / Time.
Displays a date and time of day.

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, 2004" You
know this text represents a date, but QuickBase doesn't because it's in
text format, not date format. So, you must turn this text into a date
value. You can do so using the ToDate() function. The
formula ToDate("March 7, 2004")
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 listed in
the QuickBase
Formula Functions Reference.)

Step 3: Writing
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 screen. (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, you probably chose some other field type
instead of a Formula field type in Step 2. To fix
this, click the Change Type button above the Properties tab 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 text box
in QuickBase is too small, type your formula out in Notepad and 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 field and the syntax rules of 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:

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 box displays.

Formula Functions dialog box. 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 in blue. Then click Insert. QuickBase
inserts the function where your cursor sits.

Complete the function.

When QuickBase inserts the function,
it includes placehoders 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 value types ("text") with actual
values or references, so that the function reads something like: Contains ([Job Title], "Manager").

To
insert a field reference:

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

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

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, check out 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 contain unique entries. 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 lay out 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 use the following formula to populate
a checkbox field called Millionaire:

If([net worth]
> 1000000, [millionaire]=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 a true
or false. This result could only apply to a checkbox field which is either
ON (true) or OFF (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: 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. Design the Territory field using the formula
at right:

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. Design the Total
field using the formula at right.

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. Design the Status field using the formula
at right:

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 embed comments
in formulas using double forward 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 embedded 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 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:
Boolean 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. As for text
fields, 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" date 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 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 equal operator
requires information. Undefined values provide no information. The equal
operator could handle a zero (which is a numeric measure), 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 lets you perform a special trick.
This function 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 mathematic calculation (see an example 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 (right-click
the field in a table report and select Edit this field's properties).
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, because
in order 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. Design the Revenue
field using the formula at right.

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 (right-click the field in a table report and select Edit
this field's properties) 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.