Blog

When writing formulas in Salesforce, the objective should not only be to achieve the desired outcome but also to make the formula readable, maintainable and low in character/compile size. Thanks to formula ninjas like Deepak Anand, SteveMo, Sunil Sarilla and many others on the Answers Community that I have realized the importance of utilizing the versatile CASE() function for a multitude of my formula needs. Without blabbering on about how enamored I am with this powerful function , here are the 5 areas where I use it every now and then:

1. Outcome based on different Permutations/Combinations of field values

   This technique was developed by a close friend Deepak Anand and I swear by it when implementing scenarios which involve multiple permutations/combinations.

   Requirement: Imagine there are two checkboxes on the Lead to categorize the type of package-

   –Standard
   –Premium

   Depending on how these checkboxes are checked, a user needs to display intuitive text in a custom field on the Lead record:

   Standard	Premium	Text to Display
   Not Checked	Not Checked	Ignore
   Not Checked	Checked	Warm
   Checked	Not Checked	Cold
   Checked	Checked	Hot

   In terms of Boolean Algebra, this is what it translates to:

   A	B
   0	0
   0	1
   1	0
   1	1

   Where 0 is for unchecked and 1 is for checked. This is where the Binary Technique shines and you can write the formula as shown below:

    

   

2. Validating Stage/Status progression or regression on a record

   For this one, I am going to reference an Answer Community post from the past that was resolved by SteveMo and the technique continues to be utilized ever since then.

   Requirement:  I’m trying to write a validation rule that will prevent our sales people from moving an opportunity stage backward to “Prospecting” after it has advanced past that stage. We have many stages, so the rule is rather lengthy.

   SteveMo : Here’s one I wrote using a CASE() function and some basic match that you can hack up and use

   And now that stage can never be moved backwards once it has progressed. I can’t count the number of times I have used this formula for similar requirements!
   Reference: https://success.salesforce.com/answers?id=90630000000gj6uAAA

3. Wonderful replacement for nested IF’s

   For this one, I will use Trailhead’s challenge as an example.

   Requirement: The formula should be on the Contact object. The formula should be of return type Text. The formula should be named ‘Day of the Week’ with the resulting API name ‘Day_of_the_Week__c’. The formula should return the day of the week as a string—”Monday,” “Tuesday,” and so on.If you had to write this using the IF function, it would mean a lot of nested IF’s and a huge formula size so why not utilize the CASE() function like this:

   

   See how slick that was? One of the many reasons I love the CASE() function.

4. Wonderful replacement for nested OR’s

   I bet all of you run into scenarios where you need to create a validation rule that should only fire it for certain profiles/roles or exclude only particular profiles/role from it.

   Requirement: If the Department or Title field is blank on a new record and the Profile is either “Marketing” or “Sales” or “Support” or “IT” or “Read only”, “Standard user”, do not let the user save the record.There are two ways to write this:

   1. Use the OR function to accommodate the Profile logic

   
   2. Use the CASE() function

   

   See how clean #2 looks (less wordy)? Now #1 isn’t bad either but I personally prefer using #2 because if the number of Profiles gets large, the CASE function would lead to a much smaller formula size (note Character size, not compile size) compared to using an OR function. And on a separate note, you should definitely look into Custom Permissions if you come across scenarios similar to above, on a regular basis.

5. Displaying an Image with the CASE() function

   For this one again, I will use an example from Trailhead.

   Requirement: If the lead Rating is

   • Hot, the formula shows five stars
   • Warm, the formula shows three stars
   • Cold, the formula shows one star

   If the lead Rating is blank, the formula shows no stars. This is what the formula will look like:

   
   
   Isn’t that neat? If you are already utilizing the CASE() function to its fullest potential, great job! And if not, it isn’t too late to realize the worth of this useful function. As always, feel free to share your unique utilization of CASE() function in the comments below.