Hey SmartSuite community! Our product team has rolled out a slew of new formula functions in the past few weeks, and I wanted to highlight a few of these to grab your attention and get you thinking about all of the workflows they can enable.
These functions have been added in response to your feature requests, and address some very practical problems that previously required either complex workarounds, automations, or even external processes hosted on Make or Zapier.
Let’s look at some of the notable additions by type:
Logic Functions
IFNONE() - Returns a value you specify if an expression is null or empty. This is essentially a shortcut to make empty value detection easier, without requiring an IF statement.
Text Functions
FIND(), RFIND() - Locate a specific substring offset within a text string. FIND() returns the offset of the first instance, while RFIND() finds the last instance.
MID() - Allows you to capture a substring based on start and length parameters, perfectly paired with FIND or RFIND to grab the bit of text you need.
T(), TEXT(), TRIM() - The T() function returns text values, or blank otherwise. TEXT() returns any value as text, and TRIM() removes whitespace from the start and end of the string.
ENCODE_URL_COMPONENT() - Constructing URLs or URIs? This function encodes characters in your input string to make sure your URL is properly formatted.
Array Functions
ARRAYJOIN() - The ARRAYJOIN function allows you to concatenate an array of strings using a separator you specify, optionally grabbing a limited number of items.
ARRAYUNIQUE() - ARRAYUNIQUE does the same thing, except that it returns only distinct items from the array. This is great with Linked Records, making it simple to display a list of represented task types in a long list of project activities.
COUNTA(), COUNTALL() - I could really list this one in math functions, but your primary use cases here are evaluating Linked Records. Use COUNTA() to return the number of non-empty values, and COUNTALL() to count all elements including blank / null values.
Math Functions
NUMBER() - Interprets text as a number. This is great along side the text substring functions, allowing you to use extracted numbers in a calculation.
FLOOR() and CEILING() - The FLOOR() function rounds down a number to the nearest multiple of a given significance, while CEILING() rounds up. Ideal for maintaining precision, and there are a number of algorithms that require these functions.
ODD() and EVEN() - These functions help you round numbers to the nearest odd or even integer, which can be useful for generating alternate data patterns.
ROUNDUP() and ROUNDDOWN() - When precision matters, ROUNDUP() and ROUNDDOWN() come in handy. They allow you to round numbers to a specified number of digits and guarantee the direction of the rounding.
POWER() and SQRT() - If you need to perform more advanced mathematical operations, POWER() calculates exponentiation, while SQRT() finds the square root.
EXP() and LOG() - For exponential and logarithmic calculations, EXP() calculates exponential values, and LOG() returns logarithms with a specified base and precision.
Utility Functions
CHECKLIST_COUNT() - For those of you who are managing tasks with checklists, this function is a game-changer. It counts the items within a checklist, providing a quick way to track progress.
This works with new dot-notation checklist subfields:[Checklist].[Assignee] - Returns a list of assignees from all items
[Checklist].[Completed] - Returns a list of true/false statuses from all items
[Checklist].[Due Date] - Returns a list of due dates from all items
Example: CHECKLIST_COUNT([Checklist], [Checklist].[Assignee] = "Peter")
LAST_MODIFIED_BY() - Returns the name of the SmartSuite user who last modified a field.
LAST_MODIFIED_TIME() - Returns the date and time a field was last modified.
BLANK() - Returns a blank (empty) value. The best part is this works for numbers, so you aren’t forced to return a zero!
I’m hoping that this list of functions makes your SmartSuite builds easier and more elegant. As always I would appreciate your feedback, as well as requests for the next round of formula enhancements. Enjoy!