Having the right data at your fingertips when working in Salesforce, especially from an end-user perspective, can dramatically increase productivity and enhance the overall user experience. Formulas are a great way to achieve this, making them a must-have in any Salesforce professional’s toolbox.
In this guide, we will dive into the Salesforce CASE function – how and why it is used, as well as a few examples of common scenarios in which this function will help you and your users.
What is the Salesforce CASE Function?
While the widely known IF function checks if a statement is true or false, and returns one of two results, the CASE function verifies if the actual value of a field, text, or number exactly matches one of the values you input, returning a corresponding result. The function can easily be used to return a formula based on a picklist value or create custom groupings for various use cases.
The syntax of the statement is as follows:
CASE(expression,value1, result1, value2, result2,..., else_result)
- Expression: This represents the field value, combination of field values, number or text that you are evaluating. For example, your expression could be StageName from the Opportunity.
- Value1, value2,..valueN: The number of values you input is based on your needs, and these will be the values that the expression will be checked against. Continuing on the StageName example, the values would be the Opportunity Stages, respectively Qualification, Prospecting, etc.
- Result1, result2…resultN: Each value should have a corresponding result, which will be returned in the formula field if the value matches the expression.
- Else_result: In the situation in which the expression doesn’t match any of the defined values or is blank, then this is what will be displayed.
Salesforce CASE Formula Examples
Now that you know the syntax of this particular function, we will dive into Salesforce case formula examples. These common scenarios represent a few different ways you can leverage the CASE() function across departments and requirements.
If you haven’t yet created your first formula field, feel free to take a look at our interactive tutorial post – learning how to get started may be useful before exploring the examples below.
Using Images in Formula Fields
One of the best things about formula fields is the ability to display images as an output. Salesforce has a bunch of images that can be used, and you can also download the Salesforce Labs AppExchange Graphics pack to add some more.
Using a CASE statement, we can look at our Lead Score field and assign an Image URL using the IMAGE function. If the score is 10, assign a red traffic light. If the score is 20, assign a yellow light. And if 30, assign the green. Easy!
CASE(Lead_Score__c , 10, IMAGE("/img/samples/light_red.gif", "redlight"),20, IMAGE("/img/samples/light_yellow.gif", "yellowlight") ,30, IMAGE("/img/samples/light_green.gif","greenlight"), "")READ MORE: Use Images in Salesforce Formula Fields to Spice up Your Org
Account Classification
Classifying an Account on various criteria is something a lot of companies do, and for simple use cases a formula field can easily be used. However, as an organization grows this may not be the case anymore, which means it’s worth making sure that you choose a solution which suits the particular needs of your company.
In this theoretical example, the Classification would be either Enterprise, Mid-Market, or SMB based on the custom Revenue Range.
CASE(Revenue_Range__c,"Over 1 Million", "Enterprise","Over 500K", "Mid-Market","Under 500K", "SMB","N/A")
Determine the Fiscal Quarter
Perhaps the first formula that comes to my mind when thinking about date fields, is the one helping us find out the day of the week. However, there is another which may prove extremely helpful as a formula field, but even more so when you’re out of grouping in a report.
To be more specific, you can use CASE to create a simple row-level report formula and remove the need to use a grouping in order to display a date by Fiscal or Calendar Quarter. The clear advantage of this approach is that you can have it as a column in the report without a field actually existing, while the row grouping can be used for the other three attributes.
CASE(MONTH(Date__c),1, "Q1",2, "Q1",3, "Q1",4, "Q2",5, "Q2",6, "Q2",7, "Q3",8, "Q3",9, "Q3",10, "Q4",11, "Q4",12, "Q4","N/A")
Just as I did in the example above, feel free to use the DATEVALUE() function to convert a Date/Time field into a Date if needed.
CASE Status and Response Time
When it comes to the support team and information available on the Case Object, chances are that you would want to let your customer, prospect or end user the Status of their case. Depending on your internal established Status values, they may not be as user-friendly for external users. In this scenario, you can leverage the CASE function to convert the Status to a more detailed explanation which can be sent out externally.
CASE(Status, "New", "Your case has been submitted!", "Working", "We are currently looking into your issue.", "Escalated", "A support manager will reach out shortly.", "Closed", "Your case has been resolved.", "More information needed.")
When using Salesforce Flow and the Send Email action to trigger the notification to the customer, you could also create the formula as a resource within the flow, rather than an actual formula field as internal end users wouldn’t need to see or use it. Once created, you could include it in a Text Template resource alongside a nice message to be used within the action, and you’re done!
Additionally, the response expectation related to the support tier is another way that Cases would be prioritized by the team. Depending on the criteria your organization uses to identify the type of support a customer is entitled to receive, using a CASE formula can help further translate these tiers into ETAs which the support agents can reference.
CASE(Account.Rating, "Platinum", "24 Hours Response", "Gold", "24 Hours Response", "Silver", "36 Hours Response", "Bronze", "36 Hours Response", "Standard service")
Combine IF and CASE Statements
With the IF function checking if a statement is true or false before returning one of two results, you can make use of it in combination with CASE to determine different values in one or both scenarios. This combo removed the need for repetition within the CASE function and could result in a lower compile size.
Returning to our example when going through the CASE syntax with StageName mentions, let’s calculate the formula field output based on IsClosed and Amount, then Opportunity Stage, to obtain a Tier.
IF(Amount > 50000 && NOT(IsClosed),CASE(StageName,"Prospecting", "Tier 3","Qualification", "Tier 3","Needs Analysis", "Tier 2","Value Proposition", "Tier 2","Tier 1"), "Not Eligible for Tier")
Additionally, let’s go back to the Account Classification example and include the Account Type in the mix. The Classification should be different for Customers in comparison to Partner or Reseller Accounts, which are all identified based on the Type picklist field.
IF(CONTAINS(TEXT(Type),"Partner"),"Partner Account",CASE(Revenue_Range__c,"Over 1 Million", "Enterprise","Over 500K", "Mid-Market","Under 500K", "SMB","N/A"))
READ MORE: 50+ Salesforce Formula Examples
Considerations When Building CASE Formulas
As you would expect, every functionality comes with a few caveats as well, and the CASE function is no different:
- All values have to be the same data type.
- Similar to the values, all results have to be the same data type.
- When the field used in the expression is blank, the formula will not error out, but instead, the else_result will be returned.
Make sure to check the full list of considerations and tips Salesforce provides as well before embarking on this journey.
Summary
While this is just one of the many available functions you can use in Salesforce formula fields or reports, it’s one that will set you up for success, regardless of whether you’re just starting your Salesforce journey or you’re an experienced admin.
What other situations have you used the CASE function in? Let us know in the comments below!
