Formula Field

Calculate a value based on other fields in the Record

Peter Novosel avatar
Written by Peter Novosel
Updated over a week ago

Feature Overview Video

Formula fields make it simple to automatically calculate a value. You can figure out total cost by multiplying the price by quantity, see if a date falls in a particular range, or use advanced functions to compute a risk score based on a formula you specify.


Adding a Formula field

From Grid View

Click the + menu icon after the last column header or open the Column Menu by clicking on a column header's dropdown icon and then select "Add Field to the Right."

Select "Formula," and a window will open to configure the field. You can search the name for a quick find.

From Record View

The fastest way to add a field from an open Record View to is simply click the + sign next to a current field to add a new field below.

See a Formula field being added in Edit Record View below:


When to use Formulas

Here's an example of a formula in action. The screenshot below shows a Total Estimate field that is adding together numbers that represent the estimated level of effort for a software development project:

Here are a few additional ways the formula field can streamline your SmartSuite Tables

Create customized text

You can use a formula to build a customized message by combining other fields' values.

Maybe you want to display the name of a salesperson along with their region when you create a sales performance chart - you could combine a "Salesperson" field with a "Sales Region" field to create a "Sales Rep" field with a value like Mary Smith (West)

Compare dates

Sometimes it's handy to display the number of days until a specified date so that you can quickly understand a timeline. This is a snap with a formula using one of SmartSuite's built-in functions to compare a "Due Date" with today's date.


Field Properties

Formula fields can be added and named just like any other field. To access the Advanced Editor, adjust the toggle on the bottom left side:

When you open the field properties, however, you’ll see how this field is different.

Instead of your typical properties display, the Formula field shows you a formula “builder” interface, along with panels that provide a fields list, a selection of operators and functions, and a help text area. You’ll notice that until you type anything into the formula builder area it contains the text “Write formula here” and a bit of information about the syntax that you can use to builder your formulas.


Formula Builder

The formula builder interface is where the magic happens - this is where you input your formula using a combination of field names, operators and functions. SmartSuite helps you every step of the way with inline help for every option.

If the entry in the formula builder is invalid, you'll know at a glance with an Invalid syntax message displayed just under the text box.


Adding In-Line Comments

Using the # symbol, you can add comments in our Formula Builder. Anything after a # and before a line break will be ignored by our builder and can be kept as referential plain text! Whether you want to add notes describing what the formula is doing, or keep a list of all fields that the formula depends on, this opens up a large number of options for making formulas easier to work with.


Fields List

The Fields list displayed in the lower-left corner of the formula window includes all of the fields in the current Table. Click one and the Help panel to the right will display information about that field and how to reference it in your formula. The list will be filtered as you type in the formula builder, showing only matching field names.

Even easier - just click the Use button next to the field and the field name will be inserted into the formula builder at your current cursor location.


Functions & Operators

The list of functions and operators is where the Function Field gets down to business. This is your toolbox, giving you access to a range of capabilities to evaluate input data and format your field's final output. This list will be filtered as you type in the formula builder, showing only those items that match.

Operators provide you with mathematical functions, allowing you to quickly add or multiply numbers, figure out if two values are equal, and much more.

Functions allow you to determine a value based on certain inputs. Examples of the capabilities represented in the functions library are:

  • Determining the number of days between two dates

  • Counting the number of items in a Linked Record field

  • Counting the number of items in a Sub-Items field

  • Displaying the year portion of a date

  • And dozens more...

The format and output of each function is beyond the scope of this help article, but we've put together a list of all Formatting, Functions, & Operators as an area to easily reference everything you need to know about how to use a Formula Field, or you can also take a look at our dedicated help article as well.

You can also find Text, Date, and Numeric examples in our Formula Operators & Functions solution template for those that are hands-on learners. It has basic formulas already set up so you can see how the information changes as you modify the example data, or even use the examples to create your own more complex formulas.


Formula Field Help Panel

The help panel, displayed in the bottom right corner of the formula interface, displays help for the currently-selected field, operator, or function.

In addition to a description of the selected item and its use in your formula, the help panel will display information about the syntax and example usage of the item.


Field Help Text

Need to make sure your users understand exactly what the select list is used for or what the values represent? Create customized help text that is displayed to users when they are entering data, making it a snap to personalize the user’s experience and guide them through every step.

FAQ

Can a formula reference a cell in a different Table?

Yes, a formula can reference fields in the current Table, a field in a Sub-Items field, or a Field in another Table using a Linked Record field. Learn more about this in our article Reference Data from other Tables.

Why are my numeric values not calculating correctly? For example, when adding 1 + 1 it’s producing the result 11, instead of 2. What's going on?

This particular calculation error occurs because one of the referenced values is a text value, not a number. The addition operator (+) serves double-duty in SmartSuite formulas, either adding numbers together if both values are numeric, or combining (concatenating) values to create a custom bit of text if anything other than numbers are referenced (i.e. dates or text).

To correct this error, check that both values referenced by your formula are numeric types.

Note 📝 : The formula field is treated by SmartSuite as either a number, a date, or as text. If the addition operator is adding numbers you get a number result (allowing for numeric field filtering, charting, etc.) If you are concatenating values, the output is text (so you get access to "contains" and similar filters).

Why is my formula field displaying a red icon instead of a value?

If your formula's value is a red icon, you've got an error in the formula and SmartSuite cannot calculate a value. Click on the icon as a Solution Manager or Admin to open the formula field properties and fix the formula syntax.

Note that the warning "Invalid formula" will be displayed in red text below the formula builder text box if the formula is currently invalid.

Can I use a formula to combine values from two or more fields?

Absolutely - and this is one of the most common uses for the SmartSuite formula field. See our article Combine text from two or more fields for a complete, step-by-step tutorial.

Where can I see a complete list of formula functions and operators?

We've assembled the SmartSuite Formula Field Reference Guide to answer all of your formula function and operator questions! This comprehensive document gives you a list of everything the formula field currently supports, proper syntax, and illustrative examples of each function in use. Enjoy!

Can I use the Files and Images field in a formula?

You can! This can allow you to generate a string containing a list of all of the file names. Ex: CONCAT([Link to Tasks].[Documents])

Related Video Content

Did this answer your question?