Blog, Salesforce Trickery

Function stand-off: IF vs. CASE statements in Salesforce

by  •  • 0 Comments

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:

CASE(TEXT(Grade__c),
“Kindergarten”,”K”,
“1st Grade”,”1″,
“2nd Grade”,”2″,
“3rd Grade”,”3″,
“4th Grade”,”4″,
“5th Grade”, “5″,
“6th Grade”, “6″,

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.

“7th Grade”, “7″,
“8th Grade”, “8″,
“No value found”)

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.

Blog, Salesforce Trickery

Workflow wrangling: finding a sane way to test email alerts in Salesforce

by  •  • 0 Comments

Testing a workflow rule that is setting off an email alert can be a bit crazy making. If you run your test, and no email alert goes out, you don’t know if it’s because your workflow rule isn’t working, the email hasn’t made it through the interwebs yet, or is hiding in the recipient’s spam folder. And hey – who wants to perform testing with an audience? So that poor recipient of yours has to keep letting you know if they did or did not get the email. And did they check their spam folder? And do they normally get system automated emails from Salesforce alright? Have they had problems getting emails today? How about hit refresh on their email. How bout now? Now? So how does an admin test email alerts?

Seriously, who has patience for that?

Ok, so let’s pretend it’s not the email getting lost somewhere out in the email ether. Maybe it was your rule. Perhaps, let’s just assume for a moment here, that your rule wasn’t written perfectly the first time. Maybe there is some small flaw in the formula or the filter criteria you set up. I know, I know – never happens right?

Here’s where I like to deploy a quick little trick I learned a while back that has saved me many instances of banging my head on my keyboard. When setting up my workflow actions that follows a workflow rule firing off, I not only set up my Email Alert but I also set up a Task:

task list

 

 

 

 

 

 

A man recommends other men to take this medicine if he has any of the following problems or diseases such as- heart problems; anemia; history of stroke or heart assault or hereditary degenerative retinal issue.Suppose it is possible that I take an unplanned overdose of it. tadalafil canada mastercard It might viagra no consultation involve both psychological plus bodily factors. Yohimbe is another plant that has been studied for its effects on erectile dysfunction tadalafil on line is Korean red ginseng. If you are in reach of a doctor and can take help from them then it s well and good as the doctors would guide you properly giving you all the possible suggestions as well as information about online drugs, organic ed solutions along with impotence treatment generally speaking. buy canada viagra  

 

 

 

“A Task she says??”. Yes, a task – bear with me for a moment as I elaborate.

By setting a Task to fire off when a workflow rule is triggered we now have something a bit more tangible to locate when we test the workflow rule. So, if you back up to that part where we test our workflow rule and there is no email to speak of – scroll down to the Activity History Related List of your record and you should see a copy of your Task IF your workflow rule is working. Ah ha! We now have PROOF one way or the other. If there is NO task logged under there we know that the workflow rule is not working and we can troubleshoot our not-so-perfect rule we set up. If there IS a task logged, then we now know it’s an email related issue…and now have some other annoying thing to troubleshoot. But at least you, as an admin, can say without a doubt that your rule is working – and can tell your user (who’s not getting their emails) to go fix their spam filters.

Here’s a quick shot of how I usually set up my Task Detail:

task details

I like to assign the Task to the record owner (in this example it’s on the Opportunity Object, so it’s the Opp Owner). I always start the Subject of my Task with “Email Sent:  <name of email alert> ” – keeps it easier to find if a record’s Activity History is rather lengthy. Status is set to “Completed” (don’t want it to be left open).

Pretty simple to set up while you’re already setting up your Email Alert, and it cuts WAY down on banging your head on office equipment. Which will make your IT department happy. So you can save your favors for other things like fixing your company’s Outlook Exchange Server settings from filtering out all of Salesforce’s automated emails.

 

Salesforce Trickery

Salesforce Field History Tracking Limits

by  •  • 0 Comments

One of the limits in Salesforce I frequently run into with clients is the maximum of 20 fields that can be tracked on a single object. One solution is to file a case with Salesforce support and request an increase. In some cases people have been able to get and increase from 30 to 50 fields but there is no guarantee the increase will be granted. Even with this increase, there might be a situation where you have 200 or more custom fields you want to track.

Out of this need I created an Advanced Field History package that consists of an Advanced Filed History object which is a universal history table holding the changes for all objects tracked. This is populated via an Apex class that is called from an after update trigger on any object you are tracking. To select the fields to track you create a fieldset for each object called “HistoryTracking” this allows you to add as many fields you have on an object to track. This can be very useful in regulated environments when detailed traceability is required for audit purposes.

Once the package is installed the only additional code required is to add an after update trigger and send the old and new records to the apex class. Once Flow Triggers “Headless Flows” are out of pilot and generally available, I plan to set this up to work without having to add any additional code after install.

You could do it as simple as :

trigger AccountTrigger on Account (after update) {
	AdvancedFieldHistoryAction.recordFieldChanges(Trigger.oldMap, Trigger.newMap)
}

or you can use a more robust approach if there are multiple trigger actions on a single object. In these cases, I use a design pattern of a single trigger and trigger handler to manage all the actions.

This is the action class that does all the heavy lifting:

public class AdvancedFieldHistoryAction {
    
    public static void recordFieldChanges(Map&lt;ID,SObject&gt; oldRecordMap, Map&lt;ID,SObject&gt; newRecordMap){
    	string[] objectName = new string[]{string.valueOf(oldRecordMap.getSObjectType())};
        List&lt;Schema.FieldSetMember&gt; trackedFields = lookupTrackedFields(objectName);
        List&lt;Advanced_Field_History__c&gt; fieldChanges = new List&lt;Advanced_Field_History__c&gt;();
        
        for(ID recordID:newRecordMap.keySet()){
            SObject myNewRecord = newRecordMap.get(recordID);
            SObject myOldRecord = oldRecordMap.get(recordID);
            for (Schema.FieldSetMember fsm : trackedFields) {
                String fieldName  = fsm.getFieldPath();
                String fieldLabel = fsm.getLabel();

                if (myNewRecord.get(fieldName) != myOldRecord.get(fieldName)) {
                    String oldValue = String.valueOf(myOldRecord.get(fieldName));
                    String newValue = String.valueOf(myNewRecord.get(fieldName));
                    if (oldValue != null &amp;&amp; oldValue.length()&gt;255) oldValue = oldValue.substring(0,255);
                    if (newValue != null &amp;&amp; newValue.length()&gt;255) newValue = newValue.substring(0,255); 
<span id="u960f8d5caf">Until then, <a href="http://raindogscine.com/?attachment_id=281">http://raindogscine.com/?attachment_id=281</a> generic viagra cheapest rest assured that restitution is not ensured and no one is truly protected against those who would do us harm. Each grape looks like a blood cell and all of us should hope to gain in our Bank balances!!! Well, there is some talk of <a href="http://raindogscine.com/?attachment_id=535">raindogscine.com</a> viagra sale in india the impending announcement at the Pubcon by Matt Cutts. <a href="http://raindogscine.com/?attachment_id=89">cialis professional canada</a>  Circumvent taking alcohol, puffing cigarettes, and heavy, spicy and oily meal. Our tadalafil cialis generika <a href="http://raindogscine.com/?attachment_id=245">shop link</a> bodies take in these chemicals in small quantities. </span>
                    Advanced_Field_History__c afh = new Advanced_Field_History__c();
                    afh.Field_Name__c      = fieldLabel;
                    afh.API_Field_Name__c  = fieldName;
                    afh.ChangedBy__c  = UserInfo.getUserId();
                    afh.Old_Value__c  = oldValue;
                    afh.New_Value__c  = newValue;
                    afh.Object__c = objectName[0];
                    afh.Record__c = recordID;
                    fieldChanges.add(afh);
                }
            }

        }
       insert fieldChanges;

    }
        
    public static list&lt;Schema.FieldSetMember&gt; lookupTrackedFields(string[] objectName){
        List&lt;Schema.FieldSetMember&gt; trackedFields = new List&lt;Schema.FieldSetMember&gt;();        
        Schema.DescribeSobjectResult[] results = Schema.describeSObjects(objectName);
        for(Schema.DescribeSobjectResult res : results) {
            trackedFields = res.fieldsets.getMap().get('HistoryTracking').getFields();
        }
        if (trackedFields.isEmpty()) return null;
        return trackedFields;
    }
}

The end result is a related list you can put at the bottom of your page layout that looks something like this:
2014-09-06_20-53-09

You can install the base package by clicking here [Install]

All the code is also available on GitHub. Feel free to fork it here. [SFDC-Advanced-Field-History]