I’m a proud and unapologetic Salesforce fan. As technologies go, there’s something magical about its simplicity that brings to mind the great Apple products during Steve Jobs’ second tenure.
Salesforce is so simple that any administrator can log in to the system and unlock a toolset that was once held close to the chest by IT departments. Difficult database building can now be achieved in minutes via Schema Builder and complex coding has been replaced with drag and drop Flows.
Formula fields are another key component of any administrator’s bag of tricks. But unlike Schema Builder and Flow, it’s all code.
You’re not left completely on your own though as the Salesforce interface will guide you through selecting fields and functions.
Simple formulas that only rely on a single function and one or two fields, can easily be constructed using the menus provided. But what if you need to build something a little more complicated? What if you need to string a series of functions together? What if you need to make sure only function A or function B is true? What if you need to use a lot of… IFs?
Even though the platform can elegantly bring all this power to an administrator without any programming background, there are still a few handy rules to stick to that can go a long way.
Rule #1 – Put Every Function on a Separate Line
It’s incredibly easy to fall into the trap of keeping an entire formula on a single line, especially if it’s small. Splitting each component out onto individual lines can greatly increase the clarity of your work.
Take a look at the following two examples. We have the same formula repeated, but in the second version, we split the functions over multiple lines.
This helps us see what each piece is trying to achieve and can even help highlight syntax issues with missing parentheses. Rule #2 however, takes this foundation and pushes us to the next level.
Rule #2 – Indent Sections Within Parentheses
Indenting your formulas is an absolute MUST, especially when you involve multiple functions. By indenting the formula every time you hit a “(“, you can visually group sections of code.
Let’s take the example from Rule #1 to see how it looks with indentation.
It’s now much easier to see that the bulk of the formula sits within a single IF statement and that there are two functions that belong to an AND statement. Within the AND, we have ISBLANK, which has a set of parentheses. These indentations help visually isolate each group and make it so much easier to identify and track down a mistake, such as a missing “)”.
I was recently reminded of this on the Success Community, where someone posted a problem with their formula field. The interface was reporting that there was a closing parenthesis missing and the author had spent hours combing the 50 line formula. But she couldn’t see the wood for the trees as the text was clumped together without any room to breathe.
I pasted the broken formula into a text editor (Visual Studio Code) and split every function into separate lines, using indents wherever necessary. The result was a 100 line behemoth that was a frightful beast to view. But thanks to the indentation, I found two opening parentheses that lacked siblings to close them. With a very brief 5 minute investigation (and most of that time spent formatting if I’m being honest), I had solved the mystery of the invalid formula.
In the following two examples, I’ll demonstrate how indentation can help find a stray or missing parenthesis.
With this flat layout, it’s difficult to see where the extra “)” is hiding and there is no visual clue to which parts group together.
The indented text however clearly illustrates that the AND function is only holding a single parameter, instead of multiple we should expect. Additionally, as the entire formula is wrapped in an IF statement, the opening and closing lines of that statement should be the only parts that sit on the far left. As we can see in this example, there are two erroneous lines towards the bottom that should be indented. By identifying the cause of these outdented lines, we can fix the error quickly.
In this case, the AND statement has an extra “)” and removing it gives us correct formatting and a working formula.
There’s one more tip that isn’t necessarily grounded in any development best practice but can be massively helpful when writing formulas. It’s a method that I’ve been using all through this piece in fact AND can make all the functions AND keywords stick out IF you use it.
Writing functions in UPPERCASE can bring that final bit of clarity to a complex formula, creating a clear difference between function and parameter.
None of these tips will actually affect the outcome of your formulas. They won’t change the speed, size or efficiency of the content. But they can help you troubleshoot, they can help you map out and understand your logic and more importantly, when things don’t quite work the way you’d hope, they can help you fix the problem quickly.
If you have other tips on writing formulas, I’d love to hear from you. You can find me on Twitter at @chrisemmett.