Flow: Formulas that are Commonly Used or Nice To Know

I wish there is a smart formula builder where we can just say “I need a formula in flow that does this and that”, and then “BOOM!” it generates that formula for us automatically. Sadly, it doesn’t exist (or yet?), so it becomes an important mission to familiarize ourselves with formulas. It’s a never-ending journey which requires a lot of research, so in this article, I want to list down all the formulas that I commonly use, and how to combine them to create advanced formulas.

I will constantly update this article. If there are any powerful formulas that you often use but are not here, please share with us!

Commonly used or Nice to know Formulas

Here is the official list of all formulas. I will follow a similar categorizing strategy:

1. Text Operators
   • & / +
2. Logical Functions
   • IF
   • CASE
   • ISBLANK
   • BLANKVALUE
   • AND / OR / NOT
3. Text Functions
   • TEXT / ISPICKVAL
   • BR
   • BEGINS / CONTAINS / INCLUDES
   • SUBSTITUTE
   • TRIM
   • LEN
   • FIND
   • LEFT / RIGHT / MID
     • Return Substring
4. Number Functions
   • VALUE
   • CURRENCYRATE
5. Date and Time Functions
   • + / –
   • ADDMONTHS
   • DATE
   • YEAR / MONTH / DAY
   • WEEKDAY
   • TODAY / NOW
     • Find Upcoming Friday
6. Tips of Formula Building

* Text Operators

• & or +

I want to mention this because it is the easiest yet a very powerful way to create a string. You can use & or + to connect several strings into one. If you need to add a literal string in between, use “” to wrap this literal string. For example, to add an empty space, use ” “.

* Logical Functions

• IF ( equation , result if true , result if false )

This has to be the most commonly used formula (as least by me). You enter 3 arguments – first is the expression to be evaluated, then the desired results based on whether the expression is True or False. Sometimes we can even create nested IF formulas.

• CASE ( target, scenario 1, result 1 [, scenario 2, result 2] … , general result )

If you find yourself using too many nested IF, consider CASE() instead. There are three parts for this formula – first is the target that needs to be evaluated. Then two arguments for each scenario. Finally you add a catch-all argument for other scenarios that are not listed.

• ISBLANK ( target )

This returns True or False based on the target you fill in. Use ISBLANK instead of ISNULL.

• BLANKVALUE ( target , result if blank )

If the target is blank, return the specified value. This is the equivalent of IF ( ISBLANK ( target ), result if blank, target).

• AND / OR / NOT

It is nice to know that these are interchangable:

AND ( A , B ) = A && B

OR ( A , B ) = A || B

NOT ( A ) = ! A

* Text Functions

• TEXT ( ) / ISPICKVAL ( picklist field , specified value )

TEXT() is an important function to keep in mind. There are some restrictions of which formula can be used on which data type, so if you bump into this error, try to convert the type into text. Here is worthy to note that TEXT(picklist) = “A” is the same as ISPICKVAL(picklist , “A”).

• BR ( )

This is just a nice to know formula. If you want to add an empty line in Flow text resources, you can use the BR() function. You treat this as a text, so you can use & or + to connect BR() with other strings.

• BEGINS ( ) / CONTAINS ( ) / INCLUDES ( )

Here I want to mention their equivalence in the Decision Element:

BEGINS() = Starts with operator

CONTAINS() / INCLUDES() = Contains operator

Also, CONTAINS() does not work for multi-picklist fields. We need to use INCLUDES() instead.

• SUBSTITUTE ( target , text to be replaced , text to replace )

Replace any characters in your string into other characters! You can also remove the empty spaces inside a string.

• TRIM ( target )

Remove the spaces and tabs from the beginning and end of the target. If you want to remove the spaces inside the target, use SUBSTITUTE() instead.

• LEN ( target )

This returns the number of characters of the target – spaces and symbols are all included. It can also be used to calculate the length of a collection. The length is an important information when we want to work with the index of a string.

Index is the position of a specific character in a string. In Apex or other programming language the index starts from 0, but in Flow it starts from 1. By knowing the length of a string, we will know the range of the index of that string. For example, if the length is 10, we know the index is 0-9, but in Flow it is 1-10.

• FIND ( target [start position] , character(s) to find )

This function returns the index of the specific character you want to find. You can search for only one character (ex. @) or a string (ex. com). If you search for a string, it will return the position of the first character of that string. You can also specify a start position to skip part of the target string.

It is very handy when we are working with some advanced text formula.

• LEFT ( target , number of character to return ) / RIGHT ( target , number of character to return ) / MID ( target , start position , number of character to return )

These three are very powerful functions. You can return a specific number of characters from the target string, either starting from the left, the right, or the middle.

For MID(), the start character will be included as well. For example. MID(“Hello”, 2 , 2) will return “el”.

• Return Substring

Combining LEN(), FIND(), LEFT()/RIGHT()/MID(), we can get a specific substring of a string variable. This applies to many use cases:

* Number Functions

• VALUE ( )

Similar to the TEXT() that turns the variables into the text type, VALUE() turns them into the number type.

• CURRENCYRATE ( currency code )

This is a function that might get forgotten easily, but you can use this to convert the currency fields into the desired currency. It requires two steps – first to divide the conversion rate of the original currency so the value converts to the default currency. Then you multiple it by the desired currency conversion rate.

* Date and Time Functions

• + or –

You can actually use + or – to modify the date. Keep in mind that the unit is always DAY (so + 1 is add one day). If you want to add hours or minutes, use 1/24 or 1/1440.

• ADDMONTHS ( date , number of months to add )

But if you want to add/minus months, using +/- might not be accurate since each month has a different length. In that case, use ADDMONTHS() instead. Note that there is no minusMonths() but the number can be negative.

• DATE ( year , month , day )

You can enter three numbers and make it into a date.

• YEAR ( date ) / MONTH ( date ) / DAY ( date ) …

You can return the year, month, or day of a date and turn it into a number. These is very helpful together with the DATE() function – Ex. to return the first of current/next year, or the first of current/next month.

• WEEKDAY ( date )

This returns the number of weekday from a specific date – 1 for Sunday, 2 for Monday,… 7 for Saturday. This is a very important function for the advanced date calculation.

• TODAY ( ) / NOW ( )

TODAY() returns date and NOW() returns date/time. Alternatively, you can also use the $Flow global variable to get the current date or date/time.

• Find Upcoming Friday

To be honest with you, date-related formulas are my least favorite. It is usually very complex and we have to consider many scenarios. Salesforce even has a whole article to share some examples, and it is just the tip of the iceberg. But I am sharing one formula that I really like – to find the next specific weekday!

• Tips of Formula Building

Here are some tips of how to build formulas more efficiently:

1. Build them in a Screen Flow:

I like to use a Display Text component to check if my formula works properly. This will eliminate many potential errors (ex. wrong indexing) and shorten the troubleshooting time.

2. Use formula builder from any object or a custom component

Flow Builder is getting better at detecting the errors, but the errors don’t prevent us from saving a formula resource. In this case, I will sometimes go to any object and use the formula builder when creating a new field. It is a nice method when you are just starting with formula (remember not to save that field of course).

Alternatively, you can consider this custom component that enables you to build formula directly in Flow.

3. Do A LOT of research and testing

As mentioned in the beginning, it does requires a lot of practice to become proficient in formula building. Try to research and test as much as possible, and note down some good formulas that you have seen (You will thank yourself!). Here is another example sheet from Salesforce. Also, remember there are some formulas we cannot use in Flow.

Is This Helpful? Check Out Flow Use Cases Or Write Us One!