Sunday, September 28, 2014

Salesforce: Long Text Area formula field

As of Winter '15 release, when you use function LEN() with Long Text Area field, you will get error message Error: You referenced an unsupported field type called "Long Text Area" using the following field:. The same error when you use it with Rich Text Area field, it also happen when you use those fields with other formula such as: MID(), FIND(), BEGINS(), CASE() and etc.

This limitation has been voted in AppExchange with 3470 points currently, please vote this idea.

Workaround: create Workflow Rule with Field Update action

Example: we would like to count length of Description field with Case

1. Create Custom Number Field
  • Setup | Customize | Cases | Fields 
  • Click New button in Case Custom Fields & Relationships
  • Select Number and Next
  • Enter Label, Length and Description
  • Click Next to continue and follow the wizard to save

2. Create Field Update for Workflow Rule
  • Setup | Create | Workflow & Approvals | Field Updates
  • Click New Field Update button
  • Enter Field Update Name, Description
  • Select Case object, and above field created
  • Choose Use a formula to set the new value 
  • Enter the formula into Formula Editor: LEN(Description)

3. Create Workflow Rule
  • Setup | Create | Workflow & Approvals | Workflow Rule
  • Click New Rule button
  • Select Case object
  • Enter Workflow Name, Evaluate the rule when a record is = created, and every time it’s edited
  • Rule Criteria = True

Click Next to continue then choose Select Existing Action in Immediate Workflow Actions, select field update action created in step 2 above. Save and remember to Activate to Workflow.

Cons: as workflow will only fired when there is update occur to the object, otherwise the value for that custom number field will be blank. To make it all populated, you need to mass update the object for all records.