1 of 16

Tips and Tricks for Salesforce Formulas

Jim Stoltzfus at the

Lancaster, PA Salesforce User Group

July 27th 2024

2 of 16

trailblazer.me/id/ jimstoltzfus

linkedin.com/in/

threads.net/@

3 of 16

limitations

  • Can’t trigger a flow or apex

^ may need to “stamp a value” to get a flow to run

  • Have limits like characters and compile size

^ including referenced formulas!

  • Some gotchas around field types referenceable

​

​

​

For more details, see Formula Field Limits and Restrictions and Tips for Reducing Formula Size

4 of 16

​

  • Always live, don’t have to have a reverse flow
  • No record locking / less troubleshooting
  • Less processing behind the scenes
  • Dummy-proof: can’t delete a referenced field

​

​

​

​

pros

(vs. “stamp a value” automation)

5 of 16

report formulas

  • Only need a formula for one report? Try a Row-level or Summary Formula
  • See trailhead Evaluate Report Data with Formulas

6 of 16

cross-object formulas

  • Needed for list views, and a few other places
  • You probably have a bunch cluttering up your org

​

…however…

​

  • No longer needed on record pages with Dynamic Lightning Record Pages
  • Better in formulas and apex to reference the original field

7 of 16

8 of 16

9 of 16

10 of 16

11 of 16

try a “test formula” to break down complexity

12 of 16

example 1: display a date by week

CASE(WEEKDAY(CloseDate),

1,TEXT(CloseDate)&" to "&TEXT(CloseDate+6),

2,TEXT(CloseDate-1)&" to "&TEXT(CloseDate+5),

3,TEXT(CloseDate-2)&" to "&TEXT(CloseDate+4),

4,TEXT(CloseDate-3)&" to "&TEXT(CloseDate+3),

5,TEXT(CloseDate-4)&" to "&TEXT(CloseDate+2),

6,TEXT(CloseDate-5)&" to "&TEXT(CloseDate+1),

7,TEXT(CloseDate-6)&" to "&TEXT(CloseDate),

'')

Example result for an opp closing today (Jan 24th, 2023) or any day this week would be "2023-01-22 to 2023-01-28"

​

​

see https://ideas.salesforce.com/s/feed/0D58W00009T3msLSAR

13 of 16

Shipping Street 1

​

IF( CONTAINS(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"), '|') ,

SUBSTITUTE(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

RIGHT(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

LEN(ShippingStreet)-FIND("|", SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"))), ""),

SUBSTITUTE(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

LEFT(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

FIND("|", SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"))), ""))

​

Shipping Street 2

IF( CONTAINS(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"), '|') ,

SUBSTITUTE(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

LEFT(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

FIND("|", SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"))), ""),

SUBSTITUTE(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

RIGHT(SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"),

LEN(ShippingStreet)-FIND("|", SUBSTITUTE( ShippingStreet ,SUBSTITUTE($Label.LineBreak, "-", ""),"|"))), ""))

​

(adapted from ideas like those found here: https://salesforce.stackexchange.com/questions/18909/find-line-break-in-formula-field )

example 2: uses custom labels and special characters to separate “street”

14 of 16

example 3: fixing name prefixing with cloning

  • Goal: Add the quote number as a prefix to the quote name

​

'Q-'&QuoteNumber&' - '& PRIORVALUE(Name))

​

  • Problem: cloning kept making name longer and longer, like

“Q-1235 - Q-1234 - Quote Name”

​

'Q-'&QuoteNumber&' - '&RIGHT(PRIORVALUE(Name),LEN(PRIORVALUE(Name))-13),

​

​

15 of 16

resources

16 of 16

these slides stoltzforce.com/formulas