Another somewhat off-topic entry for a developer targeted blog, but in the spirit of developer and administrator collaboration, this update is aimed at the Salesforce Button Click Admins out there, with a couple of quick hints for those working with formula fields and workflow rules etc. specifically. This information will of course be of use to force.com developers as well, and the second tip is just as valid for apex code as it is for formula syntax.
Tip #1: Make logic easier to follow with && and ||
Something I've seen consistently in both formula fields and the advanced rule combinations for views etc. is the use of the
OR() logic functions which allow you to specify very particular criteria to get exactly what you want. For instance, if you want a formula field to generate "Yep" if A and B are both
TRUE or C and D are both
TRUE (where A, B, C and D are expressions equating to
'Colour__c == "Green"'), or "Nope" if not, then you may well write a formula similar to the one below.
IF(OR(AND(A, B), AND(C, D)), "Yep", "Nope")
I think you'll agree that this prefixed notation is not particularly easy on the eye or even very easy to follow, if you read it from left to right you get "if or and A B, and C D...", but there is an alternative; for a while now Salesforce has provided the use of the operators
&& in such circumstances, and although these will be familiar to anybody who has used one of a vast number of programming languages, they may not be so obvious to somebody from a different background. The
&& operator is the equivalent of using
|| is the equivalent of using
OR(), but the way in which they're written is what makes all the difference.
IF((A && B) || (C && D), "Yep", "Nope")
At first glance, you may well feel that this is no easier to understand, but if you try once more to read the expression from left to right, then you'll see the magic: "if A and B, or C and D...". The comma may well appear to be something I've inserted to improve the readability of the phrase, but note that I also included parentheses in the expression to ensure that it means what I want it to mean. If we were to remove them altogether then we'd be at the mercy of operator precedence (i.e. precedence dictates the order in which the operators are evaluated), and although knowing the precedence of the operators you use is a valuable thing, it always pays to be explicit, not least for those who need to follow your meaning later down the line.
Tip #2: How to make string comparisons a little more robust with LOWER()
Often in a formula field or other area that uses the same functions and syntax, you'll want to compare the value of a field with some string, taking one action if they match and a different one if they don't. For example, say you have a text field into which people type a colour (yes this is a somewhat contrived example, but it's the technique that I want to demonstrate!), and you want a formula field which produces the value "Fire!" for "Green" and "Hold!" for any other value, chances are you'll do something like the following.
IF(Colour__c == "Green", "Fire!", "Hold!")
And then you'll test it, and see that it works:
Better not fire I suppose...
Fire in the hole!
So we wrote our formula, we gave it a spin, and we're done, right? Sure, done. So what happens if the user types in "green"?
Fir.. wait, what?
Ah. Clearly this is not what we're aiming for here, so what can we do about it? Well something most good developers will do when testing a string against a constant (i.e. a fixed value), and they don't care about case sensitivity, is to ensure that case variations are removed from the input string. Typically this is easily achieved in the majority of environments using a function with a name along the lines of
toLower(), and lo and behold,
LOWER() is one of the functions available when constructing a formula. As can be guessed from it's name, this function converts a string to lower case, meaning that you can compare against a constant string written in lower case knowing that you'll cater for all case combinations of the input characters.
IF(LOWER(Colour__c) == "green", "Fire!", "Hold!")
There's all sorts of further enhancements you could make to your formulae, but exactly what you do will depend largely upon your circumstances and for what you want to allow for—should you want your check to be case sensitive then the above would make no sense, but if you should want to ignore erroneous whitespace before or after a word, you'll probably want to investigate the
Hopefully this has at least given you some inspiration to consider the possibilities available to you when trying to decipher user input, making both your life and the user's life that little bit simpler.
One of my colleagues asked me about the use of
== in this post, and it's not something I'd considered before. Being a programmer used to languages where
= is assignment and
== is equality I use
== out of habit, interestingly enough both
= seem to work in formula syntax! Anybody know if there's a difference between the two in these expressions? Might investigate further myself but a little limited on time right now.