Salesforce Lesson 5 - More Best Practices with IFs in Formulas

Today I want to write about best practice uses of IF within formulas. Sometimes there are misuses of IFs that add extra complexity without gaining functionality.

Understanding formulas can be a science of its own.  They can become as complex as necessary but adding extra unnecessary complexity should always be avoided to make formulas error-free and more maintainable.

Lets take the case of a Validation Rule.  When embodied inside an AND( ) the following form of IF is unnecessary and NOT, in this technical engineer's humble opinion, a best practice.
Here is a simple formula example with an unnecessary IF:
AND (
   X = Y,
   IF(A = B, TRUE, FALSE)
)
You see the lack of necessity in the IF?
The only part necessary here is "A=B" because each line within the AND is a Boolean evaluation anyway returning TRUE when the condition is TRUE and FALSE when it is not.  Its just that simple.

The above formula should therefore be rewritten as follows:
AND (
   X = Y,
   A = B
)

As alluded to above an important consideration is the purpose/place of the formula?  Is it a Formula Field or is it Validation Rule or a Workflow Rule (criteria)?   The latter two always return a Boolean value whereas the former can be a Checkbox (Boolean) but can also return various other data types.  IF statements can therefore apply to returning something other than TRUE or FALSE.
For example a formula field might be written the following way:
IF (X > Y, X, Y)

Finally there can be plenty of uses of IF statements in Validation Rules to use two different Boolean evaluations.  Lets say you want an error to be evaluated two different ways - one way when field X is greater than field Y and another way when X is less than or equal to Y.  This hypothetical might be coded the following way:
IF (X > Y, 
    AND( Amount1 < Amount2,
               FieldA = FieldB
    ),
    A > Z
)
An alternative to this would be:
OR(
    AND( X > Y, 
             Amount1 < Amount2, 
             FieldA = FieldB
    ),
   AND( X <= Y, 
              A > Z 
   )
)

It is less complex to use the first form with the IF.  In this instance we are not assigning a static TRUE or FALSE return value which, as a general rule, is pointless.

Happy formulating!

Salesforce Lesson 4 - Notable Formula Logic Mistakes

Greetings aspiring Force.com developers,

Something like this does NOT mean anything if included with other AND conditions:
OR  (FieldX <> 'A', 
        FieldX <> 'B'
)

Why?  Because its always going to return true regardless of what FieldX value is.  If the value of FieldX happens to be 'A' its true because then FieldX <> 'B'.  If the value of FieldX happens to be 'B' its true because then FieldX <> 'A'.  If the value of FieldX happens to be 'Z' (or anything else) its true because then FieldX <> 'A'.

OK so don't do that - Im beggin.

Maybe you really want NEITHER value A or B here?  (Thats what I think when I see code like this anyway.)  In that case you want to say:
AND  (FieldX <> 'A', 
        FieldX <> 'B'
)

Although if you had this condition inside another AND condition group, as supposed in my first sentence, then just use:
FieldX <> 'A', 

FieldX <> 'B'

Generally speaking this construct is excessive and more complicated for no reason:
AND(AND(condition1, condition2), condition3)

Simplify to:  AND(condition1, condition2, condition3)

For the developer who needs to expand on a rule it can continue to get more complex so keep it as tight and clean as possible to begin with.  

Salesforce Lesson 3 - Cleaner SFDC Formulas

Have you ever needed to maintain a formula and you look at it and it seems confusing/unmanageable?  Salesforce is a fantastic platform for the non-technical to get into building business rules but it can quickly become a tangle of excess code if this is handled by non-engineer types.  Not all admins are created equal either.  One can learn all the ins and outs of administering an org but really still not be too skilled at the simplicity of clean formulas.

Here's a case of what NOT to do and what TO do...

Lets say, for example, you have the following that Fred the-brilliant-sales-guy-and-aspiring-engineer built as a formula:

OR(
    AND(
        fieldXYZ > 10,
        CONTAINS(fieldABC, 'example1'),
        field123 = 'just another case'
    ),
    AND(
        fieldXYZ = 5,
        CONTAINS(fieldABC, 'example1'),
        field123 = 'just another case'
    ),
    AND(
        fieldXYZ < 3,
        CONTAINS(fieldABC, 'example1'),
        field123 = 'just another case'
    )
)

Ever see something like that?  Its probably cleaner than the average salesperson would develop after all check out that indentation - classic good geek practice for readability.  However thats not much of the crux here. Note the only difference between the multiple nested AND groups is the criteria on fieldXYZ otherwise each is the same test.  This is redundant and can quickly become more so as new criteria might need to be added.

The simplification that works is the following:

AND(
      OR(
        fieldXYZ > 10,

        fieldXYZ = 5,

        fieldXYZ < 3
      ),
      CONTAINS(fieldABC, 'example1'),
      field123 = 'just another case'
)

Thats it!  Isnt that better?  Essentially we need to think of the overarching AND instead of an overarching OR and reverse those then add the OR inside that expresses what was different about each original AND group.

This is a simple case but it applies to any similar situation where you have repeating AND group criteria inside an outer OR group.

Carry on and keep it clean and manageable folks!

Salesforce Lesson 2 - Formula IF Statement

What is the specific syntax of IF in Salesforce.com formulae?

First, to reiterate something (not so emphasized) in my last post (Lesson 1), Salesforce formula syntax is very much like Excel formula syntax. So if you have mastered an IF statement in Excel you will have little trouble understanding Salesforce IF in formulas.
(**Do note however that in Salesforce Apex language the IF statement is a considerably different animal. There it is just like it is in Java.)

The standard format of the formula IF is:
IF(condition, result if condition is TRUE, result if condition is FALSE)

Unlike the AND and OR statements which return only a Boolean, the IF stantement will return anything you like, including a Boolean True or False if you wish.

And you can nest IF statements inside the True or False result sections to create more complex conditions.
For example with custom fields on Lead TotalQuantity__c and TotalDollars__c you want to create a text formula field called Lead Size the formula could be something like:
IF(TotalQuantity__c > 1000, IF(TotalDollars__c > 10000, 'Huge', 'Large'), IF(TotalQuantity__c > 500, 'Medium', 'Small') )

This example works in the following way:
a. if TotalQuatity is greater than 1000 and TotalDollars is greater than 10,000 then the Lead Size is "Huge";
b. if TotalQuatity is greater than 1000 and TotalDollars is NOT greater than 10,000 then the Lead Size is "Large";
c. if TotalQuatity is NOT greater than 1000 but is greater than 500 then the Lead Size is "Medium";
d. finally, if TotalQuatity is NOT greater than 1000 NOR is it greater than 500 then the Lead Size is "Small".

If you are a Java or Apex coder interested in a comparison here is what this construct would look like in one of those laguages as a nested If statement:
If (TotalQuantity__c > 1000) {
    If (TotalDollars__c > 10000) {LeadSize__c = 'Huge';}
    Else {LeadSize__c = 'Large';}
}
Else {
    If (TotalQuantity__c > 500) {LeadSize__c = 'Medium';}
    Else {LeadSize__c = 'Small';}
}

If you simply need Lead Sizes of Large, Medium, and Small conditioning only with TotalQuantity criteria the formula would be a little simpler:
IF(TotalQuantity__c > 1000, 'Large', IF(TotalQuantity__c > 500, 'Medium', 'Small') )

Note in the above examples the lack of AND/OR statements. One could combine IF statements with AND/OR combinations but those function in formulas exactly as described in my previous post, not as one who is classically trained with conditional logic might expect.

Good luck with reinventing your brain around these and other Salesforce constructs and feel free to comment with questions.

Salesforce Lesson 1 - Formula Constructs AND / OR

What is the specific syntax of ANDs and ORs in Salesforce.com formulae?

These constructs look very different from what conventional If statements** with ands or ors in many of the usual languages look like. (Though Excel et.al. are similar in their formula formulations to how Salesforce applies them.)
** IF statements will be the topic of my next post.

For starters the result of a compound conditional statement using either an AND or an OR is going to be a Boolean True or False. In Salesforce validation rules a true result is going to be a positive error condition. In essence though it is the same when using a formula for other purposes such as workflow conditions. That is - if the condition is True something will take place, otherwise whatever something is won't.
You set an outcome that will occur as the result of a formula evaluating to true. But if it evaluates false, again, the outcome does not "come out".

Anyway - back to ANDs and ORs. When a decision is based on multiple - and possibly nested - conditions this is where ANDs and ORs come into play.

The standard format of AND is:
AND(condition 1, condition 2,..., condition x)
and when ALL conditions 1 - x are true then the total result is true. If ONE OR MORE of them are false then the result is false.

The standard format of OR is:
OR(condition 1, condition 2,..., condition x)
and when ONE OR MORE conditions 1 - x are true then total result is true. If ALL of them are false then the result is false.

More complex constructs may easily be built:
AND(condition 1, condition 2, OR(condition a, condition b))
is a simple nested example. This means that if condition 1 is true and condition 2 is true and either or both condition a or condition b is true then the total result is true.

A few specifics to pay attention to:

• Note your punctuation - its important.
  • Parentheses enclose every OR or AND block.
  • Commas follow every condition except the last in a given OR or AND block.
• Picklist fields need to be wrapped by the TEXT() function for comparing with a text literal.
• Use other formula functions when necessary or appropriate - such as ISBLANK to determine if a field has no value (null) in it.

Once you get the hang of this it becomes second nature to see how to solve a given problem. Mind you it helps to understand logic and set theory.

I kicked off my Salesforce.com work in what might be thought of as reverse order - at least by the powers that be @ "[International No Symbol] SOFTWARE". I was coding some advanced solutions with Visualforce and Apex right out of the gate yet I really did not get the formula syntax until later when a client asked me to start building solutions with it.
Now its just like a walk in the park.