Get on my CASE
While a lot of my posts tend to be more broad in nature, every once and awhile, I have the urge to write about something a bit more tactical in nature, that I hope people can take value from. This is one of those weeks.
Being someone who doesn’t have a technical background, and also gets frustrated working in Excel, I had never really used the CASE function. About two years ago, I was working with a consultant on a project & he created a formula field in our salesforce.com org using CASE, and my mind was blown. Now, I had some experience with formulas, so I understood IF-THEN, but the IF-THEN-IF-THEN-IF-THEN-ELSE of a case was something new entirely. Love at first use.
As I say frequently in this blog, I can’t be the only one who was missing the power of the CASE, so I wanted to share some of my favorite uses.
One classic example is categorizing aging. If you have a custom field on an object (say the Case object) that calculates the days the case has been open, you could use the following formula in a field to categorize the status.
CASE(Days_Open__c, 4, "Overdue", 3, "Due",2, "Due", "New")
Another great example given by Salesforce is a standard discount rate, based on the user creating an opportunity.
CASE($User.Department, "IT", 0.25, "Field", 0.15, "Inside", 0.1, 0)
Finally, one that I’ve created that I find quite clever. This was looking at child objects on an opportunity. There was a child object of references, of which there were several types. We needed to report on which types were attached to any opportunity. First, I created four roll-up summary fields on the opportunity (left them off the page layout) to calculate which types of references where attached to the opportunity. Then I used the formula below to do all the work.
CASE(Reference_A__c,0,"", "A Reference") & " " &CASE(Reference_B__c,0,"", "Reference B")& " " &CASE( Reference_C__c ,0,"", "Reference C")& " " &CASE(Reference_D__c,0,"", "Reference D")
No, the CASE function isn’t sexy, but its powerful & handy. Do you have any cool CASE usages that you’d like to share? Post in the comments!