Tuesday, August 30, 2011

Salesforce Long Text Area and Formulas

If you try to create a formula on a "Long Text Area" field, salesforce does not support this.

One workaround is to create a new field and another workflow to perform a "field update" on this:
Step 1) Create a new field with data type of "Text Area (Long)"

Step 2) Create a workflow with the following settings:

Evaluation Criteria -> select "Every time a record is created or edited"
Rule Criteria -> select "Formula evaluates to true" and type "true"


Step 3) Select "Add Workflow Action" -> "New Field Update"

Step 4) Select the newly created field as the "Field To Update" and select "Use a formula to set the new value" under "Specify New Field Value"

Step 5) You can now enter in a formula that reference other "Long Text Area" fields.  The results will be posted to the new field of type "Long Text Area".

Reasons Behind This Solution
Basically this allows complex logics, ie perform formula calculations on a long text field, to be offloaded to a  workflow which runs on the background.  As a normal formula needs to be calculated very often and can slow down the page on each load/save, shifting the calculations to workflow helps salesforce lower the CPU usage.
Posted by Link Tang at 10:27 PM
Labels:

21 comments:

  1. RachelDecember 3, 2011 at 1:10 PM

    Thanks for this tip - I was stuck on this for a while until I came across your post and helpful workaround.

    ReplyDelete
  2. RachelDecember 4, 2011 at 1:31 AM

    But now I am having trouble because I am getting a workflow error whenever the original field is more than 255 characters (which is the length of my new "mirror" field). Any suggestions?

    ReplyDelete
  3. Link TangDecember 4, 2011 at 4:21 AM

    Hey Rachel, can you give the updated instructions above another go please?

    The new step 1 shouldn't have that 255 characters limit.

    ReplyDelete
  4. RachelDecember 4, 2011 at 4:43 AM

    Hi Link Tang,
    Thanks for your quick reply, but the original problem is because a formula field won't work off of a text area (long) field, so how would making a new text area (long) field help?

    ReplyDelete
  5. Link TangDecember 4, 2011 at 4:55 AM

    Salesforce basically does not allow workflow on long text area because it will slow down the page too much.

    In this case, the logic is now on the workflow, which is done on a background thread. The new field just stores the result of the formula.

    I've test this and it works 100%.

    ReplyDelete
  6. RachelDecember 4, 2011 at 10:58 AM

    but now, my formula field doesn't even give me that new long text field as an option to insert into the formula? This is exactly the problem that I had in the first place. Am I missing something? I followed your steps above exactly. I am trying to have a formula field on Object A read off of a long text field on Object B. The formula field on Object A won't allow a long text field, so I tried your workaround, but it is still a long text field and still doesn't show as an option for the formula field. The workflow won't allow me to update a field on a different object other than itself. Thanks.

    ReplyDelete
  7. Link TangDecember 4, 2011 at 1:23 PM

    Hi Rachel,

    "The workflow won't allow me to update a field on a different object other than itself."

    The solution works for:
    1) new field is on the same object as the old
    2) new field is on the child object, the old field is on the parent object.

    For example:
    Object A (child) - target field (field update created against child)
    Object B (parent) - source field

    Let me know what sort of relationship are your objects in.


    Link

    ReplyDelete
  8. monojussiAugust 9, 2012 at 1:21 AM

    Link,

    thanks for the clever workaround. Works like a charm for me and solved an acute problem.

    Cheers,
    Jussi

    ReplyDelete
  9. miklrFebruary 3, 2014 at 11:16 AM

    This comment has been removed by the author.

    ReplyDelete
  10. miklrFebruary 3, 2014 at 12:22 PM

    Tang, this seems to be the solution for me but I can't seem to get it to work.
    I have two objects:
    1. Price List Item
    2. Summary Output
    I have a long text field on the ‘Price List Item’.
    1. Then I have a formula on the ‘Summary Output’ that needs to pull in the long text from the ‘Price List Item’ to output to a proposal output from the summary output.
    I’ve tried several configurations based on what you’ve outlined to no avail. Could you clarify considering the scenario?

    Thanks for any help.

    ReplyDelete
    Replies
    1. UnknownFebruary 28, 2014 at 9:08 AM

      I'm assuming you have some kind of relationship between these two objects - either a Lookup field or a Master-Detail relationship. That way you have access to the all the fields on both objects when you create your Field Update.

      You will want to use a regular text field (which I'm guessing is called "Proposal Output"?) on your "Summary Output" object, not a formula field. You can make this field Read Only so users can't edit the data.

      The Field Update Workflow (where you will input your formula that references a field in the "Price List Item") will then be able to update this text field.

      Hope this helps!
      -Evan

      Delete
  11. UnknownDecember 3, 2014 at 8:18 AM

    Did anyone get this to work. Long Text fields do not show up in the formula field list.

    ReplyDelete
    Replies
    1. UnknownSeptember 5, 2018 at 10:38 AM

      I did! For the step 5 formula, I used TRIM(LEFT( Description , 255)) and that worked like a charm

      Delete
  12. UnknownSeptember 3, 2015 at 10:42 AM

    In step 4, what formula do we enter ? I am stuck here....

    ReplyDelete
  13. AnonymousOctober 18, 2015 at 2:11 PM

    Rule Criteria - Type - TRUE. You create a new field, type should be a Number field, and then use Value(LongFieldName -Original field) as instructed above. It will then work. Otherwise, you will get same error as you were getting on original Long Text field.

    ReplyDelete
  14. AnonymousAugust 5, 2016 at 3:47 PM

    It works, but I have a problem with the format of the text in the new field. It seems the format of the source field is not preserved and everything goes into the same line.

    Can we preserve HTML or at least the text format of the source field?

    ReplyDelete
  15. UnknownNovember 19, 2017 at 11:35 PM

    Not sure if I understood it correctly, but if the value in original Long Text field is changed, it will not propagate to child object, unless the child object is edited to fire the workflow. In that case, a Trigger on Parent object is the only solution.

    ReplyDelete
  16. DeLunatic FashionJune 20, 2018 at 1:06 PM

    Can I do this from Contact to Opportunity? If so, how? I can't seem to figure this one out!

    ReplyDelete
  17. UnknownMarch 13, 2019 at 2:02 PM

    Thank you for the instruction but I receive a Syntax error Extra Text_Area_Long__c Any ideas?

    ReplyDelete
  18. UnknownApril 15, 2020 at 8:26 AM

    Thank you for this - it worked for me copying a long text field from our quote object to the opportunity object for mapping in 3rd party software.

    ReplyDelete
  19. UnknownOctober 13, 2020 at 11:06 AM

    This was AMAZING. I have been looking for a workaround and didn't even think about this. GREAT fix!

    ReplyDelete

Subscribe to: Post Comments (Atom)