3

I am receiving this error: Error: Invalid Data. Review all error messages below to correct your data. Formula(s) that reference this field are no longer valid: Compiled formula is too big to execute (5,573 characters). Maximum size is 5,000 characters (Related field: Formula)

I have a formula to calculate whether NZ day light savings is on for a date. This is the logic: If the dateis after may and it's after the last sunday of septemeber. Or If the date is before may and before the 1st sunday in April

if(
Month( Attitude_presentation_date__c )>5
&&
Attitude_presentation_date__c>=
(DATE(YEAR( Attitude_presentation_date__c ),9+1,01) - 1
-
MOD( DATE(YEAR( Attitude_presentation_date__c ),9+1,01) - 1 - DATE(1900, 1, 7), 7))
,1,0)


+

if(
Month( Attitude_presentation_date__c )<5
&&
Attitude_presentation_date__c<=
(DATE(YEAR(Attitude_presentation_date__c ),4,01)
+
CASE( MOD( DATE(YEAR(Attitude_presentation_date__c ),4,01) - DATE(1900, 1, 7), 7), 0, 0, 1, 6, 2, 5, 3,
4, 4, 3, 5, 2, 6, 1,0))

,1,0)

I Then have a second formula that uses it to create calendar entries:

DATETIMEVALUE( 
TEXT(YEAR(Attitude_presentation_date__c))+"-" + 
TEXT(MONTH(Attitude_presentation_date__c))+"-" + 
TEXT(DAY(Attitude_presentation_date__c))+" "+ 

Start_time__c
+":00") 

-
if( Date_NZ_Daylight_savings_on__c >0, 1/24*13,1/24*12)

How can I modify this to get it under the 5000 compile limit?

  • 1
    I just changed the second formula to DATETIMEVALUE( TEXT(Attitude_presentation_date__c)+" "+ Start_time__c +":00"). Another 200 characters to go – Daniel Howell May 29 '16 at 22:56
3

Assuming that it's the second formula that's throwing the compile size error, try moving the first formula into a workflow field update or process instead of a formula field. The steps for a workflow field update are:

  1. Create a custom field of the type your field update formula will return (I don't think you can create a WF field update for checkbox, so you might need to make it numeric -- e.g. 1 if true and 0 if false).
  2. Create a workflow rule on the object. Set it to execute whenever a record is created or updated.
  3. Create a Field Update workflow action. Choose the custom field you created in Step 1 as the target, and use your first formula as the content.
  4. In your formula field, replace the part of the formula you copied to the field update action with a reference to your custom field.

When you reference another formula field, it pulls the compile size of that formula into the new formula. When you use a workflow field update, the size of the field is much smaller because it's just a regular field (not compiled at the time it's referenced).

See the Tips for Reducing Formula Size tip sheet in the Salesforce Help (the last section, "If all else fails use a workflow field update").

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.