Commonly used Formulas in Flows

 

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
  3. Text Functions
  4. Number Functions
  5. Date and Time Functions
  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 ” “.

Formula Example

{!Lead.FirstName}+" "+{!Lead.LastName}
{!Lead.FirstName}&" "&{!Lead.LastName}

Back To List

Result

* 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.

Formula Example

IF ( var = 10, "It is TEN!", "It is not ten")

Back To List

Result

  • 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.

Formula Example

CASE ( var, 
10, "It is TEN!", 
5, "It is FIVE!",
"Not ten nor five")

Back To List

Result

  • ISBLANK ( target )

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

Formula Example

ISBLANK( var )

Back To List

Result

  • 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).

Formula Example

BLANKVALUE ( var )

Back To List

Result

  • 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

Back To List

* 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”).

Back To List

  • 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.

Formula Example

{!Lead.FirstName}&BR()&{!Lead.Email}

Back To List

Result

  • 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.

Back To List

  • 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.

Formula Example

SUBSTITUTE({!Lead.Email}, "test", "replaced")
SUBSTITUTE( target , " ", "")

Back To List

Result

  • 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.

Back To List

  • 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.

Formula Example

LEN({!Lead.Name})

Back To List

Result

  • 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.

Formula Example

FIND({!Lead.Name} , "M")

FIND({!Lead.Name} , "Lwo")

Back To List

Result

  • 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”.

Formula Example

LEFT({!Lead.Name},3)
RIGHT({!Lead.Name},3)
MID({!Lead.Name},4,3)

Back To List

Result

  • Return Substring

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

Get my org’s url:

The $Api.Partner_Server_URL_530 variable returns the endpoint of your Salesforce org. This formula is saying “Starting from the left of the entire URL, return the characters all the way until the position of “/services”.

LEFT({!$Api.Partner_Server_URL_530},FIND("/services", {!$Api.Partner_Server_URL_530}))

Result

Get the email domain:

The previous formula is easy, because the position of a character is the same as “how many characters are in front of this position” . However, since email domain is on the right side, we have to calculate “how many characters are after this position”.

We can achieve that by using LEN( target ) – FIND ( character to find , target ). Then we use the RIGHT() function to get the domain.

Include @: RIGHT({!Lead.Email}, LEN({!Lead.Email}) - FIND("@",{!Lead.Email}) + 1)
Not Include @: RIGHT({!Lead.Email}, LEN({!Lead.Email}) - FIND("@",{!Lead.Email}))

Back To List

Result

* Number Functions
  • VALUE ( )

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

Back To List

  • 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.

Formula Example

currency field / CURRENCYRATE(original currency code) * CURRENCYRATE(desired currency code)
Ex. To convert values from EUR to NOK: 
value in EUR / CURRENCYRATE("EUR") * CURRENCYRATE("NOK")
Note: you can usually combine this function with the CurrencyISOCode field:
Ex. CURRENCYRATE( TEXT ( {!Lead.CurrencyIsoCode} ) )

Back To List

Result

* 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.

Formula Example

TODAY() + 1

Back To List

Result

  • 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.

Formula Example

ADDMONTHS(TODAY(), 2)
ADDMONTHS(TODAY(), -2)

Back To List

Result

  • DATE ( year , month , day )

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

Formula Example

DATE(2019, 1, 1)

Back To List

Result

  • 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.

Formula Example

First of this year: DATE(YEAR(TODAY()),1,1)
First of next year: DATE(YEAR(TODAY())+1,1,1)
First of this month: DATE(YEAR(TODAY()),MONTH(TODAY()),1)
First of next month: 
IF(MONTH(TODAY()) = 12,
DATE(YEAR(TODAY())+1,1,1),
DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))

Back To List

Result

  • 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.

Back To List

  • 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.

Formula Example

TODAY() or {!$Flow.CurrentDate}

NOW() or {!$Flow.CurrentDateTime}

Back To List

Result

  • 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!

Formula Example

The concept is quite straightforward – in any week, the difference between two dates will be the same as the difference of their weekdays, so the equation is Date 1 – Date 2 = Weekday (Date 1) – Weekday (Date 2). If we want to know the Friday of the same week, it becomes: Target Date – Today = 6 (Weekday for Friday) – Weekday (Today). Then move all the known numbers to the right: Target Date = Today + 6 – Weekday(Today)

Then to think about the upcoming Friday, we have to consider if the Friday this week has passed. If it has, add 7 days to get the Friday next week.

Upcoming Friday:
IF(WEEKDAY(TODAY())<=6,
TODAY() - WEEKDAY(TODAY()) + 6,
TODAY() - WEEKDAY(TODAY()) + 6 + 7)

If you want to find another weekday, simply switch the X in the below formula. (Note: 1 is Sunday)

Upcoming ____day:
IF(WEEKDAY(TODAY())<=X,
TODAY() - WEEKDAY(TODAY()) + X,
TODAY() - WEEKDAY(TODAY()) + X + 7)
(X is the number representation of that weekday)

You can also extend this formula to get the Friday after Y weeks by adding Y * 7.

Back To List

Result

  • 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.

No comments:

Post a Comment

Subscribe to: Posts (Atom)