Formula Advice from the Awesome Admin Community

Learn MOAR is here! For Release Highlights for Admins, check out the blog post here.

Formula Advice From the Awesome Admin Community

Salesforce Admins are experts in their orgs, and they continue to generously share their knowledge with other Trailblazers. In conjunction with the Admin Best Practices: Building Useful Formulas Trailhead LIVE session, we thought it would be fun to hear from admins regarding any formula advice they have to dish.

We put out the question on Twitter: “#AwesomeAdmins, do you have helpful tips for someone who is learning or working with formulas?”

Many #AwesomeAdmins responded to the call. Below are a few we thought you’d find helpful.

Getting started tips

Salesforce MVP Squire Kershner recommends admins “write out what you want the formula to do … Then convert the sentence into a formula phrase by phrase,” as this helps define which function to use and where.

Michelle Hansen, another Salesforce MVP, adds a bonus pro-tip: You can add comments to your formula field using “/* INSERT YOUR COMMENTS HERE*/”. Note: Adding comments using the syntax above will not impact the formula itself.

Here’s an example of a formula with embedded comments:

AND(
/*competitor field is required, check to see if field is empty */
LEN(Competitor__c) = 0,
/* rule only enforced for ABCD record types */
RecordType.Name = "ABCD Value",
/* checking for any closed status, allows for additional closed picklist
values in the future */
CONTAINS(TEXT(StageName), "Closed")
)

Kat Harrison notes that “Debugging can be tricky” which may result in rebuilding the formula one syntax at a time.

Readability improvement tips

Several Twitter users also shared tips about formatting formulas for readability.

Twitter user @CatSubstrate notes that Excel skills can be helpful when starting out with formulas. They recommend using spaces and carriage returns in a formula which “makes it easier to ‘read’ (understand) the formula.”

Mark Jones recommends using software such as “Notepad++ to write and format your formula. Also use tabs and indents, where possible, to help with formatting your formulas.”

Bruce Perry notes, “When your formulas have functions within functions, consider putting each function on a new line. Nested formulas are hard to read.” Yes, they can be, for sure.

Here’s an example of a formatted formula using tabs, indentations with a function on each line:

IF (
            AND (
                      TEXT(Best_Method_of_Contact__c)=”Phone”,
                      ISBLANK(Phone__c)
                      ),
true, false)

Other pearls of wisdom

Other great tips were shared regarding formula ability in reports, resources, keeping a list of formula references, and avoiding page performance issues.

Salesforce MVP David Carnes dishes out report formula knowledge to the community by calling out the formula super powers of row level formulas, summary formulas, and cross block formulas.

Twitter user Paula DW recommends that once you find great formulas, “Make a note of it … Next time you need it, it will be there waiting.”

Sheetal cautions to use formulas wisely. “Too many formula fields impact page performance. For a complex formula, move [it] to Process Builder or Flow.”

John suggests following SteveMo’s Best Answers to “find all the answers to formula questions you didn’t know you had.”

What great tips! It was fun to hear our expert admins on the field offer sound advice on formulas. Curious about tips of my own? Find them below:

1. Google is your friend. You are not expected to know every formula function or write every formula from scratch. I’ve worked on the platform for nearly 10 years and I still Google formulas all the time. Find one that best matches your requirements and modify it to work for your situation. I recall Mike Gerholdt saying at a Dreamforce Keynote, “There is no shame in copying, pasting, and tweaking.”
2. Start off simple, test it out, and then add from there. Do not build a complex formula only to find out it doesn’t work. Troubleshooting formulas is not easy. I make sure the smaller pieces can stand on their own before combining them in a complex formula.
3. Don’t be afraid to ask for help if you’re stuck. Drop a post in the Formulas Help, Tips, and Tricks Trailblazer Community Group.

If you have a powerful formula or tip, be sure to share it on Twitter using the #AwesomeAdmin hashtag and start a conversation in the Admin Trailblazer Community.