Formula foundations

Introduction to formulas

Quickbase formulas allow you to use existing fields to affect or generate content in another field. Formulas can be used in tables and reports to deliver actionable information to your users, secure and validate your data, and even drive workflow. You can use formulas to do things like:

• Calculate the total price of an item, including sales tax, on an invoice table

• Populate the target completion date of a project 

• Add color to certain values or fields in reports to make them stand out 

• Control what data app users see, based on their role and what information they have entered

• Formulas can also help you customize a report to get exactly those records you want. You accomplish this by adding a custom formula column to the report.

Formula components

All formulas, whether they are simple or complex, are made up of the following components.

Formula function

A formula function is a pre-defined term recognized by Quickbase that performs a specific action on values you specify and generates a new value.

In this example, the formula function is If():

If([Order Complete]=TRUE, [SUBTOTAL] + [TAX], null)

• Formula functions do not have to be used for very basic formulas. However, most formulas will contain at least one function, and complex formulas may contain many.

• You can use the Formula Functions Reference Quickbase app to see all of the available functions. Within in the app, you can narrow the list by looking for specific categories of functions (for example, Dates, Duration, or Text) or by looking for specific result types (text, numeric, user list).

To learn more about common functions and how to use them, see Commonly used formula functions and formula examples.

Argument

An argument is information within a formula function that tells the function what values to act on or produce.

In this example, the argument is [Order Complete]=TRUE, [SUBTOTAL] + [TAX], null.

If([Order Complete]=TRUE, [SUBTOTAL] + [TAX], null)

• Arguments appear within the parentheses that follow a function, with each argument separated by a comma.

• How many arguments you can include, and what type, depend on the function you use.

• Arguments can be exact values, field references, or even other functions. The order in which you list arguments is very important.

Field reference 

A field reference retrieves values from a specific field in the record to display or use in a calculation.

In this example, the field references are [Order Complete], [SUBTOTAL], and [TAX].

If([Order Complete]=TRUE, [SUBTOTAL] + [TAX], null)

• Field references are enclosed in square brackets.

  • Examples: [Manager] or [Annual Salary]

• When you use a field reference in a formula, Quickbase uses the value in the referenced field.

  • Example: A formula written as [First Name] &” “& [Last Name] strings together the values found in the First Name and Last Name fields within each record. The result of the formula might be “John Smith” or “Sara Navarro.”

• You can also use a field reference to call an application variable. Learn more about application variables and using them in formulas.

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.

Literal

A literal is a value that is meant to be used exactly as it is displayed in the formula.

In this example, the literal is 2.

If([Order Complete]=TRUE, [SUBTOTAL] + 2, null)

• Literals can be numbers or text

  • Example: The formula [Subtotal] + 23.5 will add 23.5 to the value from the Subtotal field.

• To set textual literals apart from the rest of your formula, surround them in double quotation marks.

  • Example: If([Discount %] > 0.15,"Enter a discount of 15% or less."
    In this example of a custom rule added to a table, the literal is "Enter a discount of 15% or less." 

• Textual literals can also contain quotation marks. To signal that a character is part of the literal and not a delimiter, precede the character with a backslash \.

  • Example: "The \" character is part of this literal.” 

• You must also use a backslash if you want to include an open or close square bracket. [].

  • Example: “The \[ character is part of this literal.”

• If you want to use a backslash in your literal, you still need to precede it with a backslash.

  • Example: “The \” and the \\ are both special characters.”

Operator 

Operators are special symbols like + and * that act on one or two values to return a new value.

In this example, the operator is +.

[Start Date] + Days(7)

There are two types of operators, unary operators and binary operators.

Unary operators 

Unary operators act on a single value. You can see a list of unary operators in the Formula Functions Reference app.

In a formula, they might look like:

• -5

• +4

• not true

Binary operators

Binary operators act on two values. You can see a list of binary operators in the Formula Functions Reference app.

In a formula, they might look like:

• 3 - 4

• [Start date] + Days(7)

Operator precedence

Quickbase reads operators in a predetermined order, not left to right. In other words, Quickbase acts on certain operators before it acts on others.

For example, *has higher precedence than +. If Quickbase saw the expression 3+4*2, it would first calculate 4*2 and then add 3to that number. This would make the result 11. However, if you evaluated it left to right, you would get a result of 14.

In the following table, operators are listed from highest precedence to lowest precedence.

When binary operators of the same precedence appear next to each other, they are evaluated left to right. When unary operators appear next to each other they are evaluated right to left.

For example, the following expressions are equivalent, and they return the Boolean value true.

3 < 2 + 1 / 5 + --3 and 8 > 3

(3 < ((2 + (1 / 5)) + (-(-3)))) and (8 > 3)

To manually control operator precedence, enclose portions of your formula in parentheses. Quickbase always evaluates operators within parentheses first and treats the result as a single value before moving on to the rest of the formula. (Read more in the  Writing formulas help article.)