Salesforce Admins:Taking on Formula Functions – Jenny’s Admin Tips #27

FullSizeRender (2)Formulas, something that my brain finds difficult to understand. And yet as a Salesforce Admin I find myself coming back to them over and over again. Why? Not only do I often come across feature requests that require some sort of workflow or validation rule to be created, but I also believe that creating these rules should be something that we admins should be taking a crack at rather than leaving them to the developers. Easier said than done right?

If you are an Admin who has Excel knowledge, then understanding Formulas may come a little easier to you, but if you know nothing about Excel, like me, then creating Formulas can be quite tricky to say the least. (Unless you are one of those people who just ‘get things’ first time round, in which case I envy you!) The tricky part, at least it is for me anyway, is determining the meaning of all the different kinds of Functions – which are used when building Formulas. However, with the help of MVP Ryan Headley and my Director, Gary McGeorge, I think I might have actually cracked it. Or at least gained an understanding of a few of the functions anyway…Baby steps and all 😉

Before you read on, you may want to take a look at Salesforce documentation on Formula Functions as this may be all you need to understand what each function does.

However, if you want more, then keep on readin’.

Below is an example, courtesy of Trailhead, of a validation rule that I am going to attempt to define, in layman’s terms, what each Function means and how they works within the rule.

Looking at the Formula above, the best advice that I was given by Ryan Headly was to think of Functions as little black boxes that ‘do stuff’. It is also worth noting that many of the ‘black box’ Functions are going to return a value of TRUE or FALSE. So lets break down the Formula further.

First you will see that I have formatted the Formula so that it is a little easier to read. Secondly, you will see that I have placed a number of black boxes around the Functions. This should make it easier to see what the Formula is going to return.

AND – will only return TRUE responses if all values are true and returns FALSE if one or more values are false.

In this formula we are saying, if the Account Number is not blank i.e. it has a value, then NOT ISBLANK evaluates to TRUE.  – (To save confusion on the NOT functions, it would be great if there was a ISNOTBLANK function., right?)

We then go on to say if the Account Number is not a numeric value, then NOT ISNUMBER evaluates to TRUE. (Again, it would be great is there was a ISNOTNUMBER function)

Another way to look at the Functions, as explained by Gary McGeorge, is like this:-

Function

Field Value

Outcome

ISBLANK

(12345)

= FALSE

ISBLANK

(          )

= TRUE

ISNUMBER

(abcdef)

= FALSE

ISNUMBER

(12345)

= TRUE

So when you say NOT ISBLANK, you are saying ISBLANK is NOT FALSE which = TRUE.

So when you say NOT ISNUMBER, you are saying ISNUMBER is NOT FALSE, which = TRUE.

Furthermore, as AND needs to return TRUE, your validation will successfully evaluate and continue to trigger the error to the user.

Phew. I think i just about managed to explain the AND, NOT and ISBLANK Functions here? As tricky at they look and sound just keep going with them. You will eventually get your ‘ooh yeah’ moment.

If you would like any further explanations on each Function then why not leave a comment, tweet @jenny_bamber or send Ryan Headley a tweet.

See you next week.

Jenny

FullSizeRender (2)
By Jenny Bamber
15 May 2015
Jenny's Admin TipsThe Good Systems Blog

Share this post

Catt to action

Amet aliquam id diam maecenas ultricies mi eget mauris

Lorem ipsum dolor sit amet, consectetur elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.