Tuesday, October 15, 2013

Salesforce formula with CONTAINS

Recently, I learned an interesting detail about the CONTAINS method that can be used in formula fields.  According to the inline formula help, CONTAINS is defined as follows:

CONTAINS(text, compare_text)
Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE

Let's say you needed to check for multiple values and for each, set your formula to some other value. The obvious path would be to nest your CONTAINS in a case statement, right?  Something like this, maybe:

CASE(My_Field__c
  CONTAINS(My_Field__c, 'Some Value'), 'New Value'
  CONTAINS(My_Field__c, 'Some Other Value'), 'New Value', etc...)

WRONG!  Turns out, you can't use CONTAINS with CASE, as confirmed here.  Ugh.  So, plan B, might be to use CONTAINS with a nested IF, right?  Yes, it works, but the problem you may run into is that if you are checking for many values, you may hit the maximum size for the formula field, which at the time of writing, is 3900 characters.  

So, when I was researching this, I came across this obscure knowledge article.  What caught my eye was the following:

Example 2:
a. CONTAINS("CA:NV:FL:NY",BillingState)
Will return TRUE if BillingState is CA,NV,V,L,FL:NY or any exact match of "CA:NV:FL:NY".
NOTE: when using contains with the multiple operator (:) contains then becomes equals.

The colon operator allows you to inspect many values, without the overhead of nesting IF-statements. This seems to be very well suited for checking the standard BillingState field, where values will be relatively uniform.  The key difference is the highlighted note indicating the change of function when using the operator.  In the provided example, if you had C, A, N, V, L, F, or Y, it would return true.  But, if you had California, or even CALIFORNIA, it would return false.  By contrast, if you had used a nested-if, you could have introduced some additional flexibility in finding California, CA, Cali, NoCal, SoCal, etc, sacrificing some of your character limit.  So, the takeaway for me is this: if your data is pretty uniform and structured, use the : operator, otherwise use the nested-if. 

No comments:

Post a Comment