«   |   »

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!

Posted on October 21, 2011 at 9:50 am in Admin Ramble   |  RSS feed |   Reply   |   Trackback URL

6 Comments to “Get on my CASE”

    
  1. jj
    October 21, 2011 at 10:17 am

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

    Reply
  2. Cindy Montgomery (@CindyMonty)
    October 24, 2011 at 11:54 am

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

    Reply
  3. Jared Miller (@jaredemiller)
    October 24, 2011 at 5:08 pm

    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

    Reply
  4. Fraser Suyetsugu
    November 1, 2011 at 3:13 am

    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)

    Reply
    • Becka
      November 1, 2011 at 8:58 am

      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!

      Reply
    

Leave a comment