I’ve had to reach into my own bag of formulas to find an example for this week’s entry. Last week I was focused on our local user group so we didn’t have an entry, this week I’d like to make up for that a little bit. This week’s formula is a bit more involved but nothing crazy:

Let’s walk through what the formula does:
First we have an AND function wrapped around everything. If we think of the AND function as this box that we just put things into and shake up and look inside our AND box will contain a value of either TRUE or FALSE. If everything we threw inside our AND box is TRUE, then our overall value should be TRUE. If even one item in our AND box is FALSE, the whole thing is FALSE. The trick here is that our AND box contains another BOX that can have its own items inside. This inner box is an OR box and in this case only one of the items inside needs to be TRUE in order for the whole BOX to be TRUE.

So the first item we put in our AND box is checking a Boolean field on our object. This is a checkbox that is basically telling us that this “item” is external. (Let’s ignore the business case for now as its irrelevant, just know that this is a checkbox that will either be check or unchecked. Since a Boolean value is by default either TRUE or FALSE we don’t need to do any sort of comparisons, here. However we did wrap this in a NOT function. So, if our External Item checkbox is indeed checked, (therefore TRUE) our NOT box is reading NOT(TRUE). So basically if our checkbox is checked, this would return FALSE thereby making our entire AND box false. Clear as mud? We’ll try to clear that up when we have an actual example, but for now lets check the next part.

Lines 3 through 7 contain our OR box. We are checking to see if our Opportunity is either in a stage of VALUE1, VALUE2, or VALUE3. (The stages have been changed to something generic as this is an actual formula in an org and I’m not comfortable giving away too much information). So if our Opportunity is set to any of those three stages, our entire OR box will be TRUE. Incidentally if we only had four stages, this OR box could have been removed and we simply could have said inside our AND box: “NOT(ISPICKVAL(StageName, ‘VALUE4’))” meaning if it isn’t VALUE4 than it must be one of the other values, but I digress.

Lastly we are checking a multi-picklist field (ssshhhh don’t tell @SteveMoForce) to see if one of the selected values is ‘APPLICATION’ and if so, then that part will also be TRUE.

Lets walk through an example before we get into code. Lets say we have the following Opportunity information:

External Item: unchecked
StageName: VALUE2
Item Key: SERVICE and APPLICATION are selected

This formula would return TRUE on line 2: NOT(External_Item__c) which is saying NOT(TRUE). If something isn’t TRUE than it must be FALSE. So we are checking to see if External_Item__c is FALSE (unchecked) which it is indeed unchecked, so the result of our NOT box is TRUE, since External Item is not TRUE (checked). (I know its still mud…but you’ll get it).

For our OR box, we are NOT in StageName ‘VALUE1’ but we are in StageName ‘VALUE2’ — so since one of our items in our OR box is true, the whole OR box is TRUE.

Lastly, our Item Key indeed contains the text ‘APPLICATION’ so that too is TRUE. So basically our big AND box that wraps this whole thing contains:

AND(
    TRUE,
    TRUE,
    TRUE
)

So everything in our AND box is TRUE and therefore our whole box is TRUE and this rule would fire. Its reminiscent of “solving for X” using substitution back in our Algebra days, (and you thought you’d never USE that stuff).

Now let’s code this puppy. Again let’s assume that I’ve done the proper querying of my Opportunity and included the fields that I am interested in. As we did back in lesson 1, we are going with a very verbose solution. This could be written in so many different ways and the idea here is to help you simply tie the logic together. We’ll refactor it, but lets start with using perhaps some nested IF statements (again, this code won’t compile on it own):

Line 1:
This is just our function declaration. Let’s not worry too much here at the moment as the logic is within the braces.

Line2: We are checking to see if the External Item is NOT True (unchecked). This matches our formula’s use of the NOT() function. We could have also written it like this:

if(myOpportunity.External_Item__c == False) {...}

…but that wouldn’t have really matched what our formula was saying. It would have been equal, but not “equivalent” ;)

Line 3:
Using our Opportunity example from above, that would pass and we would fall into our next IF statement on line 3. Here we are checking the Opportunity’s StageName to see if its either ‘VALUE1’, ‘VALUE2’, or ‘VALUE3’. The || symbol in our if statement means “OR” and follows the same rules as the OR box in our formulas. If any of those comparisons are True, then the whole thing passes through to the next line.

Line 4:
Here we are checking to see if our multi-picklist field as APPLICATION for one of its selected values. In Apex, you can use the “contains” function to look into some text to see if a given sequence of letters or numbers are present. Also, a multi-picklist field’s selected values in apex appear as one long string with each selected item separated by a semi colon. So our example would have: ‘SERVICE;APPLICATION’ as the value for that field. PLEASE NOTE: this code as written could wind up in a bug because if for some reason your multi-picklist had both ‘APPLICATION’ and ‘APPLICATION LICENSE’ as viable selections, and someone chose the latter, our code on line 4 would still find the word “APPLICATION” in our selection even though it was part of the “APPLICATION LICENSE” selection. So there is a better way to do that by splitting up the selections into a list and looking for the value that way, but that lies beyond the scope and I don’t want to dive too deeply too soon.

Upon passing line 4 successfully, we return a value of True in our function and we are done. If at any point we don’t pass the if statements, we fall out and wind up on line 9 where we return false.

As stated there are many other ways to write this:

or

but my point is to get you to focus on the similarities of the logic between Apex and Formulas. We can worry about writing more concise code once you get more comfortable with the concepts. Your homework? Yes there is homework. Given the following Opportunity, will it pass our tests or fail them? If it fails, where will it fail?

External Item: checked
StageName: VALUE3
Item Key: 'SERVICE' and 'APPLICATION' are selected

or

External Item: unchecked
StageName: VALUE4
Item Key: 'SERVICE'

I hope this helps you along your path. As always, I am open to suggestion and send me those formulas!

:wq!