Discover examples of simple, intermediate, and advanced formulas to help you maximize the power of Formula fields in SmartSuite.

Formula fields automate calculations based on other fields in your records. They can output text, numbers, or dates, offering flexibility for data-driven workflows.

Let's suppose that you have a Table for tracking purchases, and that you have a currency field called Price and a numeric field called Quantity. It would be handy to have the order total at a glance, so we add a formula:

That's all it takes! Your new Total field is ready to go. It's value will update as the Price and Quantity field values change. Note that we're also adding the ability soon to format these formula results to allow a currency symbol and decimal precision.

Another handy use for formula fields is to create a string of text that combines other fields' values. Maybe you want to create a unique name for a record that stores your sales lead data, combining the name of the potential customer with the sales region. You can do that in two ways:

OR

In our first example we used an addition operator, which doubles as a shortcut way to combine text.... if you use a + to combine things that are not all numbers, you'll instead get text that combines the items you specify.
​

The CONCAT function concatenates (combines) the text values of the items you include, separating those values with commas. The result for both will looks something like this:

Sometimes it's useful to know how many characters are in a particular bit of text. Say that you're creating a marketing program and you have limited space for text - it has to be under 200 characters. You could create a Text Area field called Ad Copy and a formula called Ad Copy Length with this simple function:

Use the DATEDIFF function to see how many days separate two dates.

Formula:

Use Case: Track how close your team was to hitting deadlines.

Units: minutes, hours, days, weeks, months (30 days), years.

Count the number of options selected in a Multiple Select field.

Formula:

Use Case: Track how many markets a competitor is operating in.

Find the number of days since a record was created using the NOW function.

Formula:

Use Case: Monitor how long customer service issues remain open.

Did you know that Single and Multiple Select fields can have a numeric value associated with them? Why is that exactly?

The answer to that question is that formulas can use those values, treating your select list just like a number. What that allows you to do is calculate a value mathematically - this can be very handy when you have values that affect each other.

Let's consider a scenario where a quality control manager for a software company is trying to decide which bugs to fix first. The manager is tracking two values in every bug record:

The manager wants to make sure that bugs are prioritized not just by how serious they are (the Severity value) but also by how many users they affect (the Users Impacted value).

Fortunately, this is all possible in the formula field - we'll set the [Priority} field up with the following logic:

There are a number of elements at work, from getting the numbers associated with the Single Select fields' values to using the IF statement to see if we're in the high, medium or low priority range. Let's break these components down.

First, the NUMERICVALUE function is used to retrieve the number associated with the selected option in the Severity and Users Impacted Single Select fields. Numeric values are an advanced option for single and multiple selects that can be accessed by expanding the properties dialog:

Once retrieved these values are multiplied together and then evaluated within the expressions of two nested IF statements.

The IF statement is where you really tap into the formula field's power. It allows you to evaluate an expression - if it is logically TRUE, then the first value is returned, and if it is FALSE then the second expression is returned. The trick we're using here is embedding an IF statement - within another IF statement!

In this particular function, the word "High" will be returned if the product of Severity and Users Impacted is greater or equal to 15. So a "High" severity bug has a priority of "High" unless its Users Impacted is set to "Few." A "Medium" severity bug is also set to a "High" priority if Users Impacted is "All."

If the value isn't 15 or greater, the second IF statement is evaluated, with "Medium" returned if Severity and Users Impacted are both medium (resulting in 3 * 3 = 9), and "Low" otherwise. Mission accomplished, we have automatic prioritization!

This technique can be applied to a great number of circumstances, allowing you to set a value based on any number of criteria.

Linked Record fields allow you to tie your Tables together, establishing connections that provide context and minimize the need for data duplication. You can do a number of interesting things with linked records and their data through formulas - in this use case we'll take a look at adding up values that meet a specified set of criteria.

Let's set up a typical scenario, where SmartSuite is being used to track projects (the Projects table) and project activities (the Activities table). Each project has a multiple select Linked Record field pointing at Activities.

Activities has a number of fields, but the ones we'll focus on are a number field Hours Spent and a Status field with values "Not Started," "In Progress" and "Complete."

To see how many hours have been spent on completed activities in a single glance, the project manager could add the following formula (we'll call it Completed Hours):

The result is a single number, allowing you to create saved views that make the hours spent value immediately available. See how easy that is?

The Status field is great for tracking individual user progress towards a goal, allowing them to move that status forward through a workflow process, tracking an associated due date. Good stuff, and all you need when just one person is involved.

But what can we do when there are multiple moving parts, with several Status fields involved - it sure would be nice to have an overall status for the project. Enter the formula field and its logic to save the day, automating the task of figuring out what project stage we're in!

We can use nested IF statements to create a state machine, a set of logic that determines a state based on specified rules and conditions. Let's set up the scenario, with the following fields and their values present in our Project record:

Tip: whitespace between functions in your formulas is ignored, so you can space things out to make it more readable.

With our nested IF statements we test for different combinations of settings, using those conditions to set the appropriate overall status. If Analyst Status is Pending we know we don't have to go any further, it's in a "New" state. If not, we continue through the other possible conditions - you'll notice the AND function used to require multiple conditions to both be true to return the values "Ready for Review" or "Project Approved."

Finally, if none of the expected conditions are triggered we return an error message that is more descriptive than the system's "broken function field" indicator, complete with instructions to notify our fictitious Solution Manager "Mary Smith." Built in resilience!

While this seems like it may be a simple formula, we want to always round down to get an accurate result. Here's the formula that combines the DATEDIFF function with ROUND, calculating the age of a person based on their birthday (and ensuring that we always round down).

For additional examples and in-depth tutorials, visit our Formula Field Error Messages or Formula Frequently Asked Questions (FAQs). Leverage formulas to unlock powerful automation and data analysis in SmartSuite!