Advanced SmartSuite Formula Field Configuration

Learn how to create formulas using conditional logic, Linked Records and compound formulas

‍

SmartSuite's formula field is a powerful tool that allows users to perform calculations and manipulate data within their Apps. While many users are familiar with basic uses of the formula field, such as concatenating text or summing numerical values, there are a number of advanced techniques that can take your formulas - and their capabilities - to the next level.

‍

We’ll explore how you can use the conditional logic functions such as IF and SUMIF to evaluate record data that meet certain criteria, learn how to “reach through” Linked Records to pull values from the referenced records, and finally use the values of one formula as inputs in others.

‍

Conditional Logic

The IF() function is one of the most powerful functions, and is used to create conditional formulas. For example, you could use an IF() to automatically assign a "high priority" label to any task with a due date that is less than a week away. This can be a powerful way to automate certain tasks and make your workflow more efficient.

‍

The syntax for IF looks like this:

‍

IF( [Expression], [Value if True], [Value if False] )

‍

Let’s take a look at a sample IF function to see how it works.

‍

IF(

   COUNT([Tasks]) == COUNTIF([Tasks].[Status] == "Complete", [Tasks]),

   "All Tasks Complete",

   "In Progress"

)

‍

One thing to mention here is that whitespace is ignored by the formula parser, so you can break things into several lines to improve the readability of your formulas. I’ve put the expression, the “true” value, and and the “false” value on separate lines in this case.

‍

The IF function requires you to specify a logical statement - the “Expression” - that evaluates to true or false. These can take a lot of different forms, such as:

‍

• [Status] == “Complete” → Status is equal to Complete
• [Count] > 10 → The numeric Count field has a value greater than 10
• [Date1] < MAX([Tasks].[Date]) → The Date1 field is prior to the latest date in Tasks

‍

In our IF example, we’ve got this Expression:

‍

COUNT([Tasks]) == COUNTIF([Tasks].[Status] == "Complete", [Tasks])

‍

What’s happening here is that we’re taking the total number of linked records in the Tasks App and seeing if it is equal to the number of records in Tasks that have a Status field set to “Complete.” Here you can see that it’s possible to combine multiple conditional functions to calculate a formula’s value.

‍

Another thing to point out is that you can use “=” and “==” interchangeably to evaluate equality. Programmers are in the habit of using the double-equals (as generally a single equals is used for value assignment in most programming languages), but SmartSuite supports them both.

‍

The COUNTIF() function lets you count the number of records in an App that meet a specific criteria. In addition to this scenario where we’re evaluating a status, you could use this function to create a formula that counts the number of tasks that are assigned to a specific team member, the number of invoices that have been paid, and similar scenarios.

‍

You can see in this example that you refer to fields in the linked record with a dot notation, specifying the Linked Record name first, then a period, then the name of the field IN the linked record second (also in square brackets).

‍

When those counts are equal, we’re setting the field’s value to “All Tasks Complete,” or “in Progress” if they are different. But wait - we have a small problem. Here is some sample data and the results of the formula:

As you can see, Project B’s Task Status is Tasks Complete - but it has no tasks! That’s because the total count (zero) is equal to the number of tasks with a status of Complete (also zero). To fix it, we’re going to use another advanced formula technique - nested IFs.

‍

We’ll want to see if there are zero tasks first, then proceed with the rest of our evaluation. To do that, we can test for the count being zero, and then perform the rest of our calculation if there is one or more tasks. Here’s the updated formula:

‍

IF(

 COUNT([Tasks]) == 0, "No Tasks",

   IF(

     COUNT([Tasks]) == COUNTIF([Tasks].[Status] == "Complete", [Tasks]),

     "All Tasks Complete",

     "In Progress")

)

‍

You can see that the “False” value of the first IF statement is another IF statement. It’s possible to nest several levels of IFs if needed, and the IF can be part of either the True or False portion of the formula. The updates give us a more refined result:

‍

Compound Formulas

Let’s say we wanted to further enhance our Task management system by adding a formula that calculates the percentage of tasks that are complete. That math is pretty straightforward using this formula:

‍

(COUNTIF([Tasks].[Status]=="Complete", [Tasks]) / COUNT([Tasks]))*100

‍

We divide the count of completed tasks by the total number of tasks, multiplying by 100 to get a whole number for the percentage.

Now we can take that value and use it in our prior formula to add the percentage to our Complete/Incomplete status text, with a bit of formatting. To accomplish this, we’ll reference the Percent Complete formula (which we want to keep separate so we’ve got access to the raw numeric value for reporting purposes, Metrics Widgets in dashboards and so forth) in our original calculation.

‍

Creating compound functions in this way lets you “chain” formula fields, defining a formula in one field and then referencing it in other fields. This can be an effective way to simplify complex formulas and make your App more organized, or as in our case here preserve the percentage as a separate value that can be displayed in grid view or used for other purposes.

‍

The Percent Complete formula we wrote displays its values in 2 decimal places. We can round the number and apply some formatting to make it fit our display a bit better. Here is the updated formula:

‍

IF(

 COUNT([Tasks]) == 0, "No Tasks",

   IF(

     COUNT([Tasks]) == COUNTIF([Tasks].[Status] == "Complete", [Tasks]),

     "All Tasks Complete (100%)",

     CONCAT("In Progress"," (",ROUND([Percent Complete],0),"%)"))

)

‍

You can use the CONCAT function to combine different values into a string of text. In this case, we combine our former “In Progress” message with a space, open parenthesis, then the ROUND function rounds the number to zero decimal places (the nearest whole number), and then finishes out the text with a percent symbol and closed parenthesis. The output looks like this:

Advanced Date Functions

Dates are used extensively throughout SmartSuite Solutions, and formula has some advanced functions to work with them. Consider this scenario that was recently posed by a customer - they were building a course management system where they tracked individual classes, their start dates and times along with end dates and times. Those courses would be scheduled for specific dates, and the customer wanted to apply the same start time and end time when the sessions were scheduled.

‍

To do this we added two date fields with time values in the Courses App to hold Start Date and End Date, setting the daily start time on the start date and the daily end time on the End Date.

‍

We were then able to add calculations that took the time values from those fields and added them to a Date specified for the session. The formula looks like this:

‍

DATEADD(

DATEADD([Date],HOUR([Course].[Start Date]),"hours"),

MINUTE([Course].[Start Date]),"minutes")

‍

What happens here is that we’re taking the “hour” and “minute” values from the Date field in the Course and adding it to the Date field in the Session. The added complexity here is that you need to do this twice, once for the hours and once for the minutes.

‍

In this particular use case, an automation was then used to populate a Date Range field to condense the display of the time information. It ended up looking like this:

This demonstrates another advanced use case in formulas - taking action on a function that is returning a modified result. In this case we’re adding a time value with DATEADD to the output of another DATEADD function. Functions that return a particular value type (text, numbers or dates) can be used in the place of a static reference to a value. Again, with these types of complex, layered formulas I suggest that you use spacing and line breaks to keep organized.

‍

For very long, nested formulas you may want to consider building your formula in an external code editor. There are a number of great freeware programs that you can use (Visual Studio Code, Notepad++ and many others) that will also highlight matching parenthesis, which can be very helpful when your formula looks like this:

‍

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")))))

‍

That’s an example of a nested set of IF statements used to create a “state machine” that determines overall project status from a set of independent status values. Whew!

Wrapping Up

In conclusion, the formula field in SmartSuite is a powerful tool that can be used to perform advanced calculations and automate certain tasks. By mastering these advanced techniques, you can streamline your processes and get access to derived data that is useful in managing and optimizing your work.

‍

I hope that this blog article helps spark your imagination and inspires you to incorporate these techniques in your Solutions and Apps. Please share your use cases and configuration steps with the SmartSuite community!

‍
View our Formula Operations & Functions Solution here.