Ah, IF statements.

They mean well. They really do. They step up to help us admins during more challenging times, formulaic-ly speaking. And we’re thankful, we really are. But sometimes you just need a simpler approach to your already busy administrator life. And an IF statement, while vital in many situations, can just be downright ugly, disorganized, and let’s face it: makes my eyes bleed when trying to de-bug, modify, or otherwise make some sort of sense out of.

So what’s an admin to do?

Well, this is where I’d like to introduce my friend the CASE function.

Ah CASE statements. Such a breath of fresh are.

So simple, innocent, quick to type up, troubleshoot, modify, and de-bug. It’s like a cool drink of ice water on a hot balmy day, and yet I never seem to come across them in many of my clients environments. Instead the IF statements seem to prevail. I’ve come across tangled, gnarled, convoluted messes – one after another – and the headaches! the hair pulling! the eye bleeding!

Ever open an IF statement like this and want to just crack open a beer and call it a day?:

IF( OR( ISBLANK( Patient__c ) , ISBLANK(Patient__r.DOB__c ) , ISBLANK(Patient__r.Street__c ) , ISBLANK(Patient__r.City__c ) , ISBLANK(Patient__r.State__c ) , ISBLANK(TEXT( Payer_Type__c )) , ISBLANK( Patient_Order_Form_Signature_Date__c) , ISBLANK( Patient_Order_Form_Rcv_d__c), ISBLANK( Referring_MD__c ) , ISBLANK( Audiologist__c) , ISBLANK(TEXT( BTE_color__c)) , ISBLANK(TEXT( BTE_to_be_worn_on__c )) , ISBLANK(TEXT( ITM_to_be_worn_on__c )) , ISBLANK(TEXT( Mic_Tube_Size__c )) , ISBLANK(TEXT( Patient_Dx_Primary__c )) , ISBLANK(TEXT( Patient_Condition_Preventing_Use_of_AC__c )) , ISBLANK( Rx_Date__c ) ,ISBLANK( Patient_Rights_Form_Rcv_d__c)&&ISPICKVAL(POP_Version__c,’Old POP’),ISBLANK( Assignment_of_Benefits_Form_Rcv_d__c), IF( ISPICKVAL(POP_Version__c,’Jan 2013 POP’), OR( ISBLANK( CMN_Signature_Date__c), ISBLANK( Current_Audiogram_Received__c), ISBLANK( Patient_Chart_Note_Received__c), ISBLANK( Rx_Form_Received_Date__c), IF( ISPICKVAL(Payer_Type__c ,”3rd Party Payer”), OR(ISBLANK(Copy_of_Insurance_Cards_Received__c),ISBLANK( Insurance_Benefit_Investigation_Form__c)), FALSE ) ),FALSE) ), ‘false’, ‘true’) ,’International_Order’, IF( OR( ISBLANK( Patient__c ) , ISBLANK( Patient_Order_Form_Rcv_d__c), ISBLANK( Referring_MD__c ) , ISBLANK( Audiologist__c) , ISBLANK(TEXT( BTE_color__c)) , ISBLANK(TEXT( BTE_to_be_worn_on__c )) , ISBLANK(TEXT( Mic_Tube_Size__c )) , ISBLANK(TEXT( IFU_Language__c ))) , ‘false’, ‘true’) ,’true’

No bueno.

To illustrate how a CASE statement can be SO much EASIER to write than an IF, let’s take a situation where we have a custom field (Grade__c) that is a picklist for users to select a student grade level from (with values to choose from like “Kindergarten”, “1st Grade”, etc). And we want to create a custom formula field to translate that value to a simple single digit value (i.e. “K”, “1″, etc). With an IF statement we would have to draw up something like this:

IF(TEXT(Grade__c) = “K”, “Kindergarten”, IF(TEXT(Grade__c) = “1″, “1st Grade”, IF(TEXT(Grade__c) = “2″, “2nd Grade”, IF(TEXT(Grade__c) = “3″, “3rd Grade”, IF(TEXT(Grade__c) = “4″, “4th Grade”, IF(TEXT(Grade__c) = “5″, “5th Grade”, IF(TEXT(Grade__c) = “6″, “6th Grade”, IF(TEXT(Grade__c) = “7″, “7th Grade”, IF(TEXT(Grade__c) = “8″, “8th Grade”, “No value found”)))))))))

Ok, ok, not the end of the world. BUT, it was annoying to write. Especially getting all those parenthesis in there. I can’t tell you how many times I got that annoying  ” Error: Syntax error. Missing ‘)’  ”  error message until I got the thing right.

Let’s take this formula and clean it up. Behold the simplicity, and happiness that is the CASE function:

It is just the tip of the iceberg. tadalafil 20mg from india First, you need to follow your niksautosalon.com buy generic viagra doctor’s advice and do a little research on your own to make an informed decision. For other women, the symptoms can be debilitating, greatly affecting their relation with their partners. viagra india online The Web is filled cialis prescription with con artists trying to supply low quality products and also swipe your finances.

And yes! Our new CASE formula will yield the same results. With the handy dandy CASE function we now have an admin-friendly formula that is much easier to modify or troubleshoot. Hooray for us! In fact, this formula is now so simple that you can easily train a fairly junior admin how to add or remove from it as business processes change. Need to change what value returns when the value chosen is “7th Grade”? No problem! Want to add “9th Grade” to the list? Knock yourself out! It’s kuh-RAZY!

And if the ease of creating a formula like that wasn’t enough to sell you, how about this: CASE functions help you avoid creating a formula that is too big for execution. IF statements tend to grow exponentially on the backend where all the calculating takes place, and before you know it you’ve got a formula that seems simple enough and yet it won’t save because you get that pesky “exceeded the maximum number of characters” error alerts. Swap that formula out with a CASE statement and you’ll most likely be back in business.

Now that I’ve sung all sorts of praises about our friend the CASE statement, I wouldn’t be doing my job if I wasn’t clear that CASE isn’t meant to replace IF statements. Yes, CASE statements are awesome sauce. But they can’t perform evaluations nested within them. So if you need to determine if the Grade__c field value chosen begins with “K” for example (by nesting the BEGINS function in there), you can’t do that. Too fancy for CASE. Gotta throw an IF statement together for that.

But for Best Practices sake, I whip out a CASE statement whenever possible to save myself the hair pulling, to keep my formulas nice and small, and avoid getting too big for execution.