Visit 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.
Simple Formulas
Calculate a Value
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:
[Price] * [Quantity]
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.
Combine Values to Create Custom Text
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:
[Customer Name] + "-" + [Region]
OR
CONCAT([Customer Name], "-", [Region])
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:
Count Characters in a Text Field
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:
LENGTH([Ad Copy])
Intermediate Formulas
Display the number of days separating two dates
Want to see how close your team came to hitting all of their deadlines? See the number of days between estimated and actual dates with the DATEDIFF function in this simple formula:
DATEDIFF([Target Date],[Actual Date],"days")
This formula will output the number of days Actual Date is past Target Date. If the actual date falls before target date, the number will be negative.
You'll notice at the end of the function is the text days. This is the unit of time you want the function to display. DATEDIFF uses the following syntax:
DATEDIFF(date1, date2, unit)
Unit is one of the following:
∙ minutes
∙ hours
∙ days
∙ weeks
∙ months (uses 30 days for calculation)
∙ years
Count the number of selections in a Multiple Select field
What if you are using a SmartSuite Table to capture information about your competitors and are tracking the markets that they compete in. If Markets is a Multiple Select Field, a straightforward way to see the number of markets selected is to create a formula that uses the COUNT function:
COUNT([Markets])
The result will be the number of selections in the Markets field for each record - note that if there are no selections the function will display a zero.
You can do a few other things with the COUNT function, such as count the number of linked records in a Linked Record Field.
See the number of days since a record was created
In the Simple Formulas section above we learned how to compare two dates with DATEDIFF. It is also possible to use functions as arguments within functions. Imagine a customer service issue tracking table - wouldn't it be nice to know at a glance how long it's been since a particular issue was filed? You can do that with DATEDIFF if you compare the created date with today's date... which you can do with the NOW function, like this:
DATEDIFF([First Created], NOW(), "days")
The result will be the number of days since the record was created, with the NOW function dynamically substituting the current date. Easy!
Advanced Formulas
Evaluate the numeric values of Select lists to create a score
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:
[Severity] a Single Select field with values: Low (numeric value = 1), Medium (numeric value = 3), High (numeric value = 5)
[Users Impacted] a Single Select field with values: Few (numeric value = 1), Many (numeric value = 3), All (numeric value = 5)
[Priority] a Formula Field returning either "High", "Medium" or "Low"
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:
IF((NUMERICVALUE([Severity]) * NUMERICVALUE([Users Impacted])) >= 15, "High",
IF((NUMERICVALUE([Severity]) * NUMERICVALUE([Users Impacted])) >= 9, "Medium",
"Low"))
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.
Conditionally sum values of Linked Records
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):
SUMIF([Activities].[Status] = "Complete", [Activities].[Hours Spent])
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?
Use an IF statement to generate overall status
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:
[Analyst Status] Single Select field with values: Pending, In Progress, Complete
[Manager Status] Single Select field with values: Pending, Approved, Rejected
[Overall Status] Formula field that will produce one of the following: New, Analysis, Ready for Review, Project Approved, or Error
Tip: whitespace between functions in your formulas is ignored, so you can space things out to make it more readable.
IF([Analyst Status] = "Pending", "New",
IF([Analyst Status] = "In Progress", "Analysis",
IF( AND(
[Analyst Status] = "Complete",
[Manager Status] = "Pending"
), "Ready for Review",
IF( AND(
[Analyst Status] = "Complete",
[Manager Status] = "Approved"
), "Project Approved",
IF([Manager Status] = "Rejected", "Project Rejected",
"Error - Notify Solution Manager: Mary Smith")))))
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!
Calculate a person's age based on their birthday
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).
IF(
ROUND(DATEDIFF([Birthdate],NOW(),"days") / 365.25, 0) > (DATEDIFF([Birthdate],NOW(),"days") / 365.25),
ROUND((DATEDIFF([Birthdate],NOW(),"days") / 365.25) - 1, 0),
ROUND(DATEDIFF([Birthdate],NOW(),"days") / 365.25, 0)
)
