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!

Last week we dove right in with a formula from SteveMo. This week, I want to take a small step back and rather than just transform a formula into Apex, I’d like to talk on a more general level. Often times our formulas are checking for equality between two fields, or one field and some expected value. There are numerous ways to do this in formulas, but I wanted to show you some ways that you can check for equality in Apex as well.

Lets start off with with something simple, like checking if the value of a picklist is equal to some known value. In a formula one would have:

ISPICKVAL(MyField__c, 'Some Value')

In apex however, we don’t need the “ISPICKVAL” and we use the double equals to check for equality as such:

MyObject.MyField__c == 'Some Value'

The above is usually put within an if statement or its result assigned to a variable like this:

if(MyObject.MyField__c == 'Some Value') {
    //do geek things here
}

–or–

myBooleanVariable = (MyObject.MyField__c == 'Some Value')

Now lets talk about checking for inequality. Normally this is wrapped in a NOT function within a formula like so:

NOT(ISPICKVAL(MyField__c, 'Some Value'))

In apex we check for not equals usually using the not equal operator (!=):

MyObject.MyField__c != 'Some Value'

Again we put this in if statements or assign to variables as shown above. There are also the >,<, and <> operators but these function identically in apex as they do in formulas:

Some_Value > 0
Some_Value < 0
Some_Value <> 'some other value'

For some the ‘<>‘ operator may be new, its another way of checking for inequality and I prefer to use that method in formulas but use the != notation in apex. I’m not sure why I prefer it that way other than that’s just what I’ve been using in other languages since I started programming and that’s a 15 year habit that I don’t see me breaking anytime soon.

I apologize if this week’s lesson feels a bit light, but you can help with that. Send me your formulas! :)

Until next time

:wq!

Last week I mentioned that I would be starting a new series of sorts. Today will be the first entry in that series. My hope is to bridge the gap between point and click development, and slinging code for those admins/developers that are hesitant to make “the leap of faith” into the world of of development. As mentioned, fellow MVP @SteveMoForce had mentioned that perhaps seeing a formula of sorts (be it from a validation rule, workflow entry criteria, etc) written out to its equivalent in Apex would be of use to begin understanding it.

While writing this stuff likely isn’t going to be useful to you directly, the intent is that since — as an admin — you are already familiar with how formulas work and how to write them that it isn’t as far a stretch to go from the logic of formulas to the logic of slinging code. After all, that is the biggest hurdle when learning to code, the logic. The rest is just syntax, like learning a new language, and for you the admin, more like learning the nuances of a given dialect of a language.

So for our first example, SteveMo was kind enough to donate the following formula that simply looks at four fields, determines if there is a value in any of them and if there is a value in more than one, return true. I’m told this was part of a validation rule the expects that “any” of the fields can be filled out, but only one of them can contain data at any given time:

IF(ISBLANK(Field_A), 0, 1) +
IF(ISBLANK(Field_B), 0, 1) +
IF(ISBLANK(Field_C), 0, 1) +
IF(ISBLANK(Field_D), 0, 1) > 1 

What we have in this formula is a few “IF” functions, some math operations, and a comparison. (Comparisons always return true or false, since a Validation rule fires when its criteria is true, we need a comparison at the end of the day). Before jumping into the coding portion, lets ensure we all understand this formula.

IF(ISBLANK(Field_X), 0, 1):
When you use an IF function in a formula, it checks to find out if a given condition is true. Upon finding it is true it returns the next value in the “IF box”, if its false it returns the last value in the IF. So in this case we are checking to see if the value of the given field (Field_A) is blank. If it is indeed blank (condition is true) then the “value” in our IF box is 0, if it is not blank, the value of our IF box is 1.

We Do Math:
We take the results of these IFs and add them together, if they are all blank we get 0 + 0 + 0 + 0 = 0. If only one of the fields is filled out (it doesn’t matter which), we could get 0 + 0 + 1 + 0 = 1. Lastly if more than one field is filled out (again any combination) we could end up with 1 + 0 + 1 + 1 = 3.

Now we compare:
Finally we will look at the result of our math and check to see if it is greater than 1. If it is greater than one, our Validation Rule becomes TRUE and will fire a message. If it equals or is less than one, our Validation Rule is FALSE and all is well.

So let’s write some Apex:
I know your excited, but I want to clear a few things up. Firstly, we have to imagine for now that we have done some other coding to get access to this object’s fields. Following Steve’s example lets say we have a Widget object with the following four fields: Field_A__c, Field_B__c, Field_C__c and Field_D__c. Furthermore, as it is out of scope for this series, lets assume we’ve written the necessary code to allow us to refer these fields as “MyObject.Field_A__c” etc. Secondly, this code will be very verbose. There will likely be more concise/better ways to write this, but we have to walk before we run. Lastly, I may use words like variable or other “coding words” — I will try to explain those as I go, but remember the concept here is to map what you know of formulas to something you are unfamiliar with so that your mind can begin to marry the two together. This is largely an experiment so whether this helps remains to be seen. Stay with me.

Lets look at the code (I put the code in an image for two reasons. First, so that I get nice line numbers with my code and its easier to do a walkthrough. Second, so that if you wanted to play with this, you’d have to type it in manually. I know, I’m a jerk but cutting and pasting is one thing, but I believe it helps you absorb it all if you type it in as you go. That being said, this won’t compile directly in eclipse or sublime — remember that we are assuming I’ve done some other stuff, but perhaps some of the more enthusiastic amongst you might read up on that “other stuff” and I don’t want you mad at me when it doesn’t compile):

Now lemme ‘splain:
Line 1: Don’t get hung-up here. This is how we write a chunk of code that we can refer to later from some other place. Its called a function and we write code that “calls” it, which basically means, do the stuff in between my curly braces ({…}). For now just consider line 1 and line 33 as the walls of the container in which we are holding our apex version of the validation rule. The “black box” of magic so to speak.

Lines 2-4: Here we are telling the compiler (the bit that checks to make sure our code is valid and will run) that we are going to use some variables to hold integer values (whole numbers, not decimals, not text). Variables are placeholders for items we want to refer back to later, or do something with. In our case, we will be putting a value of either 0 or 1 in each of these based on whether or not our fields have a value in them and adding them up later.

Line 5: Here we are telling the compiler about another variable, but instead of holding a number it will hold a Boolean value which simply put, means its going to hold a value of TRUE or FALSE.

Line 7-11:
This might look familiar, its an IF statement quite similar to the IF function used in our formula. There is a certain syntax wrapped around us here so now would be a good time to point that out. An IF statement in Apex has a condition that you are checking for in parenthesis (in this scenario, the code in between the parenthesis is similar to the ISBLANK portions of our Validation Rule criteria), and then some curly braces. The curly braces separate the logic for us a bit. The code in between the first set of curly braces are evaluated when the condition in parenthesis is true. An if statement may also have more sets of curly braces. In our case we have an “else {…}” block. THIS block gets evaluated when our condition is not true (and therefore false, get it?). At some point in the future we can talk about the “else if {…}” block which allows us to check for numerous conditions by stringing them together, until one of the conditions finally winds up true or we run into an else block, or just run out of conditions to check all together.

So continuing with line 7-11, on line 7 we are checking our condition: if(myObject.Field_A__c == null). What is null you ask? Null is essentially “nothing” — so we are checking to see if there is any value there or not. The check for equality in Apex is the double equal sign “==” so this bit is equivalent to our ISBLANK function in our formula. If there is no value in myObject.Field_A__c then our condition is true and line 8 will get evaluated. On line 8 we store a value of 0 in our Integer variable called “a” — if there was indeed a value in myObject.Field_A__c then our condition would be false and therefore our “else {…}” block would get evaluated and the code on line 10 would tell our Integer variable “a” to hold onto the value of 1.

Lines 13-32: We repeat this logic for all four fields either storing 0 or 1 in each of our variables. When we are done we should have four variables that we can then add the values they hold together like we do on line 31. But that line looks just a bit different doesn’t it? Here we are saying “Hey compiler, remember that Boolean variable I told you about? Well take the value of adding a + b + c + d (my picture left out the + d — sorry) and compare that to see if its greater than 1 and store that result in my isGreater variable.” That may be a bit much to grasp, and I get it. Here’s what is happening. We are adding (a + b + c + d) but much like the order of operations from elementary school, we want that math to happen before we compare it to 1. We could have written it like so:

Integer i = a + b + c + d;
isGreater = i > 1;

But we can put this all on one line. Remember whenever we do a comparison, we either get a TRUE or FALSE value and Boolean variables know how to hold on TRUE or FALSE values.

Lastly, we “return” the result of our comparison to whatever called it (again elsewhere in code). Our validation rule doesn’t have the concept of a “return value” per se — it just IS that result. (Essentially, technically it does “return a value” but you as the admin are shielded from all of that).

Now some of you out there are saying, “WOW that’s quite a bit of code when my formula was only 4 lines long!” and you’d be right, but formulas have some built in magic that hide some of that extra “stuff” from you. Also, remember I said there may be better ways of doing things? Not all code would have to be that long. This next snippet — don’t worry if you don’t get it, its only here for an example and to show you that not all code is so drawn out — would do essentially the same thing:

That above snippet has some more advanced things in them like looping over a list of items, etc. Perhaps we will eventually get there with this series. I’m not sure if we will have Part 2 next week — hopefully so, but it depends on an submissions I may get from readers, or perhaps SteveMo has some more gems hiding somewhere. So feel free to send me your ideas and keep this going. Let me know if you have questions or if I can help clarify anything else in this lesson.

:wq!