Formula variables

If you write a lot of formulas, you may find that you're including the same snippet of text again and again within a single formula. Formula variables can save you some time.

A variable is a short stand-in or proxy for a longer snippet of code. You can create variables when you need them. Use a variable to replace portions of code that will repeat within a single formula. For example, say that a store owner wants to give his customers a discount if their total purchase exceeds $100. The formula that handles this calculation would contain repeating "Price times Quantity" code snippet. Here's the entire formula:

            if ([Price] * [Quantity] > 100, [Price] * [Quantity] * 0.9, [Price] * [Quantity])

This formula says: If Price times Quantity is greater than 100, then multiply Price times Quantity by 90%, otherwise, display the result of Price times Quantity.

But you can create a formula variable to fill in for the [Price] * [Quantity] portion of the code. Say you name the variable "total." You'd type the following in the field's formula box:

var Number total = [Price] * [Quantity] ; if ($total > 100, $total * 0.9, $total) 

First, you define the variable. This formula first defines the variable total as [price] * [quantity] and also specifies that the data result of this snippet is a number. At the end of the variable definition, type a semi-colon to separate the var statement from the actual formula. Then type in the formula, inserting the variable, $total, wherever the snippet previously occurred. You indicate the use of a variable with a $ dollar sign.

Note: Variables work within a single formula only. You can't use a variable across different formula fields.

Here's another formula that uses a variable. Here, the data type is text:

var Text fullname = [first name] & "
 " & [last name];
Case([title], "Dr.", [title] & "
 " & $fullname,
"Mr.", [title] & " " & $fullname,
"Ms.", [title] & " " & $fullname,
"Hon.", [title] & " " & $fullname, $fullname)

The variable is defined at the top. It's a text type variable, called fullname, which consists of the string: [first name] & " " & [last name]

This formula says: If [Title] is Dr., then display the title, a space and the fullname (which is the string: [first name] & " " & [last name]). If the title is Mr., display the title, a space and the fullname. If the title is Ms., display the title, a space and the fullname. If the title is Hon., display the title, a space and the fullname. If the titles is none of these, display only the fullname.

Variable Name Guidelines:
When naming a variable, you must adhere to the following rules, or Quickbase spits back a formula error:
- A name must be a single continuous string of letters. Do not include spaces in your variable name.
- Use only letters. Do not insert any special characters like dashes or underscores and don't use numbers. Never use characters that mean something in a Quickbase formula like [ ( or /.

Setting Yes or No (boolean) conditions

Formula variables are especially handy if you've got lots of conditions to set and they appear multiple times within your formula. Use a formula variable to stand in for conditions you need to set. Is the contract approved? Whenever you're setting a yes or no value (does an item meet the condition or not?) like a checkbox, you can use formula variables to make your formulas more readable.

This formula starts by setting three formula variables:

var bool contractApproved = (([Contract Approval] = "Approved") or ([Contract Approval] = "PreApproved")); var bool printApproved = (([PRINT Approval] = "Approved") or ([PRINT Approval] = "Changed"));

Each variable (var) is of data type boolean (bool), which means the result will be yes or no. If the conditions that follow are met, the result is YES, if not, the result is NO. So, the contractApproved variable will be YES if the value in the Contract Approval field is "Approved" or "PreApproved."

Within the formula, you'd just insert the formula variable where you need to lay out these conditions. For example the Contract Status field, once it defines the formula variables you just read, may contain the following formula:

if ($contractApproved and $printApproved and not([Needs VP/Finance Approval]), "In Production", "Pending")

This formula says: If the contractApproved variable is yes and the printApproved variable is yes and Needs VP/Finance Approval checkbox field is not turned on, then status is "In Production", otherwise it's "Pending."

Variable result types

You can create the following types of variables:

  • var bool

  • var number

  • var text

  • var textlist

  • var date

  • var datetime

  • var duration

  • var timeofday

  • var workdate

  • var user

How do you choose which one to use? Again - the data type you specify following var should be the data type that results from the formula snippet you define with the variable.

Related topics: