A big WELCOME to today’s guest blogger
Randi Thompson

A couple of weeks ago, the Director of Marketing (who also just happens to be my boss) asked me to implement a new Account scoring system we’ve been putting together over the last few months. I knew the scoring system would be mildly complicated but I wasn’t prepared for the intricate nested Case and IF formula(s) that I began playing around with. I knew I didn’t have enough experience with Case formulas to be able to get this scoring system in place within the week, so I happily threw away my Case experiments and moseyed right on over to Process Builder.

While this was also a bit complicated, it only took me a few hours and 11 versions to get it working! Once again, Process Builder showcased its enormous super powers . If you have any nested CASE or IF statements, or have been struggling with one, may I recommend scrapping them and moving over to Process Builder…*

*caveat: Process Builder is considered a Flow, so if you are nearing your Active Flow limits, then it’s probably best to keep your nested IF and Case formulas where they are.

Business Case:

Here’s the IF/THEN set up.

Randi - PB1
– So, Estimated Volume is the primary variable. IF an Account has an estimated volume of 50,000+, they get a score of 40, despite either of the other 2 variables (Bed, Doctors).

– If an Account has an estimated volume of 100, they get a score of 10, despite the fact they may have 500 plus beds or 60 doctors. Estimated Volume comes first.

– If Estimated Volume is empty(or is zero), then # of Beds is the secondary variable, despite how many doctors they may have.

– Lastly, if both Estimated Volume and # of Beds are zero or empty, then # of Doctors is the tertiary variable.

At first, this requirement seemed pretty straightforward; until I had to consider all these: If <= OR >= and that’s how nested CASE and IF statements work.  As I started writing out my CASE statements, and inhaling every SteveMo thread on CASE statements, I realized something very quickly: I was wayyyyy over my head.

That’s when I wondered if my little friend, Process Builder, could solve my problem.


Because this is a score for Account records, I start at the Account object.
Randi - PB2

I start the Process when a record is created or edited, because a user may gather more information about Estimated Volume, # of Bed, or # of Doctors over the lifetime of the record.

Initially, I also chose to ‘Allow process to evaluate a record multiple times in a single transaction’. However, that caused an influx of Unhandled Fault errors. Dozens of them. I was INUNDATED! It freaked me out so bad I deactivated the Process. Then I cried inside a little. Then I took to the Community and I was SAVED!

Essentially, the Process was going in a loop. It says “recursion” now, but prior to Summer ‘15, it didn’t have that “recursion” there and the explanation in the floaty question mark is a little obscure. So, I unchecked it. The MAGIC. It SPARKLES!

Randi - PB3

Here’s how Process Builder replaces a nested CASE/IF formula

I start with the lowest score first. Remember: The process looks for the FIRST TRUE criteria and then STOPS.  So, the order in which your criteria nodes are set up are very important (and yet, having to move criteria nodes around means cloning your Process and entering your nodes manually again. What a pain!).  My first version started with the 40 score – I thought I was being clever. I wasn’t.
Randi - PB4
//Printing a Process is not yet an embedded functionality and you aren’t allowed to make any changes to an Active process (or a process that’s been inactivated – you have to clone it again to make changes). So, the entire logic statement is really:

1 AND ((2 AND 3) OR (4 AND 5 AND 6) OR (7 AND 8 AND 9 AND 10))

You’ll notice I chose, ‘Do you want to execute the actions only when specified changes are made to the record?’. My first 9 versions did NOT have that checked, so when one of the criteria variables were changed, it wasn’t updating the score. Again, those Advanced options are super tricky and VERY IMPORTANT!  What that really asks is: If changes are made ONLY to one or more of the variables used in the criteria node, do you want to run the Process? In my case: Yes. Yes, I do.

So now I enter the 20 Score requirements:

Randi - PB5
Again, that full logic statement reads:

1 AND ((2 AND 3) OR (4 AND 5 AND 6) OR (7 AND 8 AND 9 AND 10))

Then the 30 score:

Randi - PB6
Again, that full logic statement reads:

1 AND ((2 AND 3) OR (4 AND 5 AND 6) OR (7 AND 8 AND 9 AND 10))

And finally, the 40 Score:

Randi - PB7

Because this is a more straightforward IF/THEN statement than the others, the logic statement is shorter:

1 AND ((2) OR (3 AND 4) OR (5 AND 6 AND 7))

That’s it!

4 Criteria Nodes and 4 Immediate Actions with very easy IF/THEN logic statements. No messing around with all those nested IF and Case formulas, where most of your time is tracking down an extra or missing parentheses.

NOT to say that nested IF/Case formulas don’t have their place in the Admin toolkit.  Like Formula fields, Workflows, Custom URL Buttons, Publisher Actions, Visual Workflow (Flow), and Apex – they all have their moment in the sun. All I’m sayin’ is…nested IF/Case formulas are HARD! Process Builder is easier.

Have you used Process Builder in lieu of a nested IF/Case formula? Share your use case and how you set it up!