Writing formulas – Quickbase Help

In this article:

Tools to help you write formulas
Formula writing tips and best practices

Tools to help you write formulas

Formula box on the field properties page

After you have created a formula field, you add your formula to the Formula box in the field properties.

To learn more about different field types and how to add formula fields to your table, see the Creating formula fields help article.

This box provides a working space for you to write your formula. It allows you to easily add formula functions and field references, and also provides real-time formula troubleshooting.

Adding formula functions

When you click the Formula box from the field settings page, the Choose fields & functions dropdown appears.

Click Select a function… to see a pop-up window that allows you to browse all Quickbase formula functions and choose one to add to your formula.

In the Quickbase Formula Functions pop-up window, you may also filter to see only certain types of functions. To filter the list of functions, click the arrow on the All Functions box.

When you add a function from the Quickbase Formula Functions window, Quickbase includes placeholders for each argument that tell you what data types go in each spot.

For example, if you add the If function, Quickbase will give the following template to help you construct your formula:

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.

Adding field references

When you click Choose fields & functions, you will also see a list of all the fields in your app. If you click on one of the fields in this list, it will be added to your formula as a field reference.

Real-time error checking

As you add your formula to the field properties, Quickbase checks for potential errors in real-time. The system will highlight different parts of your formula and give different warnings and error message.

To learn more about how to interpret these troubleshooting messages, read the Troubleshooting formulas help article.

Formula functions for working with dates

These formula functions help app builders process data that includes dates and times across time zones and systems.

ToFormattedText() includes an input for “ISO8601”. ISO8601 is an international standard used in many systems, including our RESTful APIs.
Example: ToFormattedText(01-01-1970,ISO8601) = 1970-01-01T00:00:00-05:00
ToUnixTime() outputs the numeric unix time of a date, or date/time field, including the milliseconds.
Example: ToUnixTime(01-01-1970) = 18000

Formula Functions Reference app

The Formula Functions Reference app lists all available functions you can use. These are the same functions you can see if you click Select a function… in the field properties page.

From the Formula Functions Reference app, you can filter functions by Category or Result Type.

Each record in the app represents one function. If you view a function, you will see additional examples of how the function can be used.

Automatic Formula Machine app

The Automatic Formula Machine is a Quickbase app anyone can use to quickly generate certain kinds of formulas, including:

Conditional formulas, which you can use to track prices, discounts, or project status.
Image thumbnail formulas for use in reports and drop-down lists.
Text style formulas to format the size, color, and appearance of text.

You can read a Quickbase Community forum article about the app to learn more about it and to give feedback.

To use the app, you step through a series of field choices and a formula is built for you, which you can then copy and paste into the formula field in your own app.

Formula writing tips and best practices

Remember 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. This has a major impact on the outcome of your formula.

To review Quickbase’s operator precedence, see the Formula foundations help article.

Remember that you can change the order of operator evaluation by using parentheses. Quickbase always starts reading a formula within the deepest set of parentheses.

Add notes to your formulas

Adding notes to your formula helps you remember what each part of your formula does. It can also help others understand your formula.

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.

Checkbox and text fields are never null. A Boolean value can only be true or false. For example, a checkbox field is either checked or unchecked.
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)

This formula means: 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 , the formula [Field name]=null would not work. 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.

Example: the IsNull() function returns True if the field is null and False if it's not.
Example: The Nz() 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 mathematical calculation (see an example of this in the table below).
- Tip: Even if you do not use the Nz() function, you can tell Quickbase to treat a null in your numeric field as a zero, so you can use it 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 turns this setting on automatically.

Example formulas that use null:

YOU WANT TO

FORMULA

FORMULA EXPLANATION

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.

Formula variables

When you are writing very complicated formulas, you can create formula variables to help you simplify. To learn more, read the Formula variables help article.