function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
vandemanjwvandemanjw 

Character Limit on Formula Field

I would like to create a formula field that populates based off of another picklist option

 

 

case(Phone_Number__c, 
"800-444-4444","Marketing House Ad", 
"800-444-4445","Marketing Home Page", 
"800-444-4446","Trade Show Ad", 
"800-444-4447","Magazine Ad",...)

 

The phone number selected would automatically populate the program name where the lead originated.  However, there is a 5000 character limit when using a formula field, and that is not enough for the number of programs that our organization offers.  Is there a way to recreate this same functionality, but do so in a way that would allow a larger list of programs?

 

 

 

yudhvirmoryudhvirmor

I suggest you to create 2 Program categories and write 2 seperate formula fields.. I am really not sure if there is any othe workaround to increase the limit or you can use the phone number contains 800 goes to X program category and 888 goes to Y program category..

Pradeep_NavatarPradeep_Navatar

The code in a formula field can exceed maximum number of characters allowed in two ways:

  • Directly in the formula field's characters (3900), or
  • In the overall size of the formula after other included formula fields are factored in (5000)

The latter of those problems can result from the formulas (code) of other formulas being inserted where those other formula fields are included. For example:

  • Field A says "Total_Amount * .9"
  • Field B says "IF (Field_A__c = 0)..."
  • Then behind the scenes, B is expanded to say "IF ((Total_Amount * .9) = 0)..." because Formula_A's formula is put into Formula_B, not Formula_A's result

So while building or modifying a formula field if you run into either of these limits, what to do?

1. The smaller character limit (3900) can be remedied by splitting the formula into 2 fields. Create another formula field and break out some part of the code into that one. Then call Formula_A within Formula_B. Each can have up to 3900 characters.

2. The trickier case is when a formula field that refers to other formula fields (that may in turn refer to even more formula fields) results in compounded code which exceeds the 5000 character limit. In any edition, the whole combination of formula can be scrutinized to see if there is anything that can be reduced or eliminated from the string of code. Any small part might have a ripple effect and significantly reduce the overall size.

2b) In Enterprise or Unlimited Edition workflow can be used to help out. Create a new hidden field (not on any page layout). Create a new Workflow Rule for the object which triggers when record is Created or Edited and make the Criteria something that is always true, like Owner Name <not equals> NULL. Then make an Immediate Workflow Action which does a Field Update to the newly created (hidden) field, click on "Use a formula to set new value", then for the formula, just enter the name of the first formula field (Formula_A). Any time a record in this object is created or edited, Formula_A's results will be calculated and copied into this new regular field. Then refer to this field instead of Formula_A within Formula_B.

For best results on multi-level nested Formula field references, pick a spot near the middle of the chain of formula fields to split the overall code in half. This will reduce the size the quickest.

2c) Apex code could also be used in (EE, UE) to trigger an update to a field on the record. This provides even more powerful and flexible options, including the capability to assign values based on procedural algorithms (e.g., a double-declining balance) and to reference values beyond the record/object that is being evaluated in a formula or workflow, similar to the VLOOKUP() function--that can be employed on a validation rule but not in a custom formula field.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

ckryzanckryzan

Thanks, Pradeep, for a very thorough explanation of how to address this issue. It provided me the tutorial I needed to get this working in my implementation. The only suggest I might have is for Item 2b, that you might mention that not only do you need to create the hidden field (btw, it doesn't NEED to be hidden, I used it visible for debugging), but that it does need to be an input -- and not a formula -- field. That turned out to be an issue for me as I was attempting to repurpose my existing formula fields and couldn't determine why they weren't showing up in the field pick list for workflow.