Thursday, February 20, 2014

Salesforce: Formula with CONTAINS() function

You can use CONTAINS() function in Salesforce from formula field, validation rule, workflow rule and etc. But it commonly used in validation and workflow rules to search for a character or string in a text field.

If you are reading Salesforce documentation, it said the functions is to compare two arguments of text and returns TRUE if the first argument contains the second argument. If not, returns FALSE. Be aware that comparison using Contains() is case sensitive.

This function is only for Text field. For Picklist, add TEXT() function to convert the picklist value to text. For Picklist (Multi-Select), use INCLUDES() to see if a multi-select picklist has a specific value.

UsageCONTAINS(text, compare_text)
CONTAINS will return TRUE if "compare_text" is found in "text" and FALSE if not.

 CONTAINS("abc", "abcd") --> FALSE  
 CONTAINS("abcd", "abc") --> TRUE  

ExampleIF(CONTAINS(Product_Type__c, "part"), "Parts", "Service")
In this example, we compare if part is exist in a custom field Product_Type__c

Some Use Case:
1. Searching for Text
Example: CONTAINS(Comments__c, "BadWord")
Returns TRUE if "BadWord" is found anywhere in Comments__c

2. Check if an unknown string or character matches a defined set of strings or characters.
Example: CONTAINS("0123456789", Address)
Return TRUE if Address values such as 1, 2, 9, 01, 789, or any other substring of "0123456789"

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

You can enhance above formula to be
this formula will ensure that BillingState is 2 characters in length. But, it still return TRUE for :N or V: and other any 2 character EXACT match of each state "CA:NV:FL:NY"

Of course you can still enhance the formula above to check if the 2 characters do not contain :

For a picklist field, use TEXT(picklist_field_name) to convert a picklist value into a text value so that you can work with the value in functions that support text value.