As some of you know, I was not always in tech. For the first five years of my career after school, I was working in student affairs and student housing at a university. Right before I joined my first tech-related job, my first dive into automation was when I worked as a contract manager. Yep, that’s right, my plunge into tech started with the automation of my day-to-day Excel spreadsheets. To this day, Excel still holds a place in my heart and I often still use it to do quick one-off calculations or data sorting.
Formula Builder at a glance
Rather watch a video instead?
The developer advocates demonstrated the new Formula Builder on Live Coding Happy Hour. Missed the episode? Here it is below. Want to know what other Tokyo content we have lined up for you? Check out the Tokyo Release Blog Post to see what’s coming.
Benefits of a formula over a script
How to use Formula Builder
Formula builder has been added to the existing “Calculated Value” section of a column’s dictionary entry.
As a reminder, calculated values in a table column take values from other fields and the result of that computation is what is shown as the field value (similar to how a cell in Excel could contain a formula like
- Open a field’s dictionary entry
- Navigate to the “Calculated Value” section/tab
- Check the checkbox next to “Calculated”
- Change the “Calculation Type” to
- Build your calculation’s formula in the “Formula” field
- Save your record
If you do not see the “Calculated Value” section/tab, switch to Advanced view via the Related Link UI Action.
Examples of possible formulas
CONCATENATE(first_name , ".", last_name , "@", org.name , ".com")
Combine the first_name field and the last_name field as an email address.
LOWERCASE(CONCATENATE(file_prefix, NOW(), ".tmp"))
Builds a filename with a prefix, the current date, and a file type, all in lowercase letters
AVERAGE(SUM(price_1, fuel_1), SUM(price_2, fuel_2))
Sums two sets a fields then finds the average of the two
SUM(MULTIPLY(celcius_temp, 1.8), 32)
Calculate Fahrenheit based off of a Celcius field
Calculate the difference between a user’s birthday (notice the dot walked field) and today’s date
Formulas can have the following:
- Functions like
- Variables like column names on the current table. This includes dot walked fields. Note that you should use only column names, not display names.
- Operators like
>=(greater than or equals to),
<=. Note that arithmetic binary operators (
*) are not supported. Instead, you would use the arithemtic functions instead (like
- Constants like
"hello"(a string) or
As a reminder, formulas that contain nested functions work as you would expect in Excel: a function called in a parameter of another function must fully resolve before the parent function can continue.
Let’s break down the Celcius to Fahrenheit example above:
SUM(number1, number2, ...)
SUM is the function name, whereas number1 and number2 (and so on) are the function’s parameters
MULTIPLY(number1, number2, ...)
Similarly, MULTIPLY is the function name and number1 and number2 are the function’s parameters
SUM(MULTIPLY(celcius_temp, 1.8), 32)
- The calculated field attempts to SUM its 1st parameter and 2nd parameter together (in this case, the constant number
- The first parameter in the SUM function is another function, MULTIPLY, so the SUM function has to wait now
- The MULTIPLY function tries to multiply its 1st parameter (the value of the celcius_temp field of this record) and its second parameter (in this case, the constant number
1.8). The MULTIPLY function has all that it needs to resolve and comes up with an answer.
- Now that SUM’s 1st parameter has resolved, it can now finally resolve itself too.
- If you misspell a column name, you will receive an “invalid update” error when trying to save the dictionary entry.
- Syntax is validated
- Symbol validation (checking if the field exists)
- Incorrect function names are also validated
At the time of this post being written:
ANDPerforms a logical AND operation on the arguments.
AVERAGEReturns the average value of the arguments.
CONCATENATEJoins one or more input strings into a single string.
DIVIDEReturns the quotient value after dividing argument 2 by argument 1.
IFExecutes the specified statements based on the Boolean output of the conditional expression.
ISBLANKFinds white spaces or blank values in the string and returns true if there are any.
LENGTHReturns the total number of characters in the input string.
LOWERCASEConverts the input string to all lowercase characters.
MAXReturns the highest value in the specified arguments.
MINReturns the lowest value in the specified arguments.
MULTIPLYReturns the multiplied value of the arguments.
NOWReturns the current date and time of the instance in ISO format.
ORPerforms logical OR operation on the arguments.
POWERReturns the result of the base value raised to the power of the exponent value.
REPLACEReplaces characters in the source string with the characters in the target string.
SUBTRACTReturns the result value after subtracting argument 2 from argument 1.
SUMReturns the sum of all the arguments.
TIMEDIFFFinds difference between 2 dates for Duration field.
TITLECASEConverts the input string to all title case characters.
UPPERCASEConverts the input string to all uppercase characters.
Other things to note
- Data types are not checked during validation. For example, if you have a number field and your formula returns a string, the result will be an error. An “Unparsable” error label will appear next to the calculated field in form view.
- The list of available functions can be found on the
Formula Builder in Table Builder
While this blog was within the context of the platform view, the same functionality can be utilized in Table Builder. For details, you can visit the release notes.