Formulas in Salesforce are an awesome admin tool but can sometimes be difficult to understand. This blog series, ‘The Everyday Function’ by Tom Hoffman, brings Salesforce formulas down from the cloud and into everyday life. ”The Everyday Function’ will first be published as part of Spark.Insights at https://www.sparkorange.net/insights/
The Everyday Function: Good grammar, My Mom, and Aunt Sally.
Like an English teacher, my mom, and algebra; Salesforce functions want everything to be just right.
Good Grammar
I know proper grammar, I swear. But as well as I know it, mine is often trash. I overuse ellipsis…not sure why.
I sometimes treat my sentences like a mini-term paper, with an intro, body, and conclusion separated by -. I talked to John – he said my grammar in blog posts is hot trash – I guess I need to work on that.
When typing, I often fire out a ‘their’ because my left hand apparently does not like all the back in forth in ‘there’.
If you follow the @AccidentalAdmin twitter feed, you’ll see how clumsy I can get.
The point is, despite my shortcomings, grammar is important in life and in Salesforce functions. For those that disagree, see below:
As it applies to Salesforce:
Knowing where to use ‘ ‘, not mixing “ “ and ‘ ‘ in the same formula, where to insert commas, &, +, ||, and a number of other values will help solve hours of pain when writing formulas, validation rules, automation criteria, and other elements inside of Salesforce.
Some general Salesforce function grammatical rules:
Use single ‘ not “, it takes up less space, its easier to type, and it just looks better. If you mix up ‘ and “ in the same formula or leave one out, you can get this and other errors that will send you looking in all the wrong directions.
Don’t eat grandpa: put your commas in the right place. In a formula, a comma tells the current function there is something else coming, leave it out and you’ll see this error:
These error messages are tricky, you will look at your ( )’s, see those are correct and wonder why you are still getting this annoying error message. Take another look at your grammar and your formula errors should clear right up.
Now onto my mother…
When I was a kid I’d run outside, my mom shouting after me, ‘SHUT THE DOOR!’ When words weren’t enough, she wielded La Chancla like a pro…making me go fetch the Latina mother cure-all for bad behavior and forgetfulness.
Hell, even today, if I’m just running to grab my kid from daycare, a door or two may be left ajar, and my wife is always happy to let me know about that, too.
Formulas are quite similar (minus the shoe)… if you open the function ( you have to shut the function ). If you leave it open, just like my mom, Salesforce lets you know:
When helping people with formulas, the Missing ‘(‘ or Missing ‘)’ error messages are probably the two most common. These errors mean one of two things:
Most Common: You simply have more open ( doors than closed ) doors OR closed ) a door you never opened (. This one is easy to solve, look at your functions, match up each open door with a closed door until you find the one that is missing.
Less Common: You have the right number of open ( doors and closed ) doors, but are still getting the Missing ‘)’ error message, this typically means something is missing in your formula and Salesforce is attempting to close the current function. See my comma notes in the grammar section and/or make sure you are following the correct syntax for your function.
About Aunt Sally…
Please Excuse My Dear Aunt Sally (PEMDAS) is the pneumonic device many of us remember from algebra class to learn the order of operations.
Parenthesis
Exponents
Multiplication
Division
Addition
Subtraction
While this video will tell you why PEMDAS is ‘morally wrong,’ the 1:16 mark illustrates a helpful way to approach your Salesforce functions.
Let’s kick the excuses and your dear aunt to the curb and keep the politeness, please.
I like to focus on Parenthesis, throwing an open ( door and closed ) door around each component to make sure my formula calculates just how I want.
If you have all the elements in your formula, there are no syntax errors, but your calculations don’t seem to make sense, go check on Aunt Sally, throw some new doors () in the right places, and you’ll be just fine.