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!

Advertisements

6 Comments to “Get on my CASE”

  1. Awesome Post, I hope to get a case of the “CASES”

  2. Great post! I’ve played around with Case formulas but don’t have any active right now.

  3. Great post! CASE never gets enough credit! Since you asked, I thought I would share another use of CASE.

    I combine the CASE and IMAGE formulas to display flags/traffic lights/stars on Opportunity pages based on stage. It adds a nice look and feel and I have found it helps with adoption – People like to “see” how their actions change the record.

    I also use the same idea with Data Quality Scores – seeing an image is much easier to see in a report then all the fields you could be scoring.

    Jared

  4. I use this formula often when I need to use the month of a date field in a report – typically when grouping by month. An oldie but goodie.

    CASE(
    MONTH( Project_Date__c ),
    1, “01 January”,
    2, “02 February”,
    3, “03 March”,
    4, “04 April”,
    5, “05 May”,
    6, “06 June”,
    7, “07July”,
    8, “08 August”,
    9, “09 September”,
    10, “10 October”,
    11, “11 November”,
    12, “12 December”,
    NULL)

    • It’s funny, a week after posting this, someone asked me how to do this exact month formula. I gave them a tsk-tsk for not reading my blog, but gave them a very similar formula. Thanks for sharing on here, I’m guessing a lot of people will borrow this one!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: