+ Start a Discussion
szesze20szesze20 

Error: Compiled formula is too big to execute

Hi, when I save the below formula, it gave 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,735 characters). Maximum size is 5,000 characters

 

I'm looking for help to rewrite this formula to stay below the limit. Thanks in advance!

 

(
IF  (ISPICKVAL (Carrier__c, "Main"), 
	(
	IF ( LEN( Plan_Name__c ) = 0, 0 , 1 ) +
	IF ( ISPICKVAL (Carrier__c, ""), 0, 1 +
	IF ( ISPICKVAL(Product_Cat__c, ""), 0, 1 +
	IF ( ISPICKVAL(Product__c, ""), 0, 1) +
	IF ( ISPICKVAL(Account_Type__c, ""), 0, 1) +
	IF ( ISPICKVAL( This_Plan_Covers__c ,"") , 0 , 1 ) +
	IF ( ISPICKVAL( Type__c ,"") , 0 , 1 ) + 
	IF ( ISPICKVAL(Status__c, ""), 0, 1) +
	IF ( ISBLANK(Effective__c), 0, 1)) + 
	IF ( ISBLANK(Effective_Through__c), 0, 1)) +
	IF ( ISPICKVAL(Payment__c, ""), 0, 1) +
	IF ( ISPICKVAL(Rate_Step_T1__c, "") , 0 , 1 ) + 
	IF ( ISNULL(Rate__c), 0 , 1 )
	),
(
IF ( LEN( Plan_Name__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( This_Plan_Covers__c ,"") , 0 , 1 ) + 
IF ( ISPICKVAL( Type__c ,"") , 0 , 1 ) + 
IF ( ISPICKVAL( Network__c ,"") , 0 , 1 ) + 
IF ( ISPICKVAL( Rate_Type__c ,"") , 0 , 1 ) + 
IF ( ISPICKVAL( Rate_Position__c ,"") , 0 , 1 ) + 
IF ( ISPICKVAL(Rate_Step_T1__c, "") , 0 , 1 ) + 
IF ( ISNULL(Enrolled_T1__c), 0 , 1 ) + 
IF ( ISNULL(Rate__c), 0 , 1 ) + 
IF ( ISNULL(EE_Contribution_T1__c), 0 , 1 ) + 
IF ( ISNULL(ER_Contribution_T1__c), 0 , 1 ) + 
IF ( ISNULL(Ratio_T1__c), 0 , 1 ) + 
IF ( ISPICKVAL(Rate_Step_T2__c, "") , 0 , 1 ) + 
IF ( ISNULL(Enrolled_T2__c), 0 , 1 ) + 
IF ( ISNULL(Rate_T2__c), 0 , 1 ) + 
IF ( ISNULL(Contribution_T1__c), 0 , 1 ) + 
IF ( ISNULL(Contribution_T2__c), 0 , 1 ) + 
IF ( ISNULL(Ratio_T2__c), 0 , 1 ) + 
IF ( ISPICKVAL( Primary_Care_Co_pay_Co_ins_PC__c ,"") , 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_PC__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( Specialist_Care_Co_pay_Co_ins_SC__c ,""), 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_SC__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( Generic_Drugs_Co_pay_Co_ins_RxG__c ,""), 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_RxG__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( Outpatient_Surgery_Co_pay_Co_ins_OS__c ,""), 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_OS__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( Emergency_Room_Co_pay_Co_ins_ER__c ,""), 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_ER__c ) = 0, 0 , 1 ) + 
IF ( ISPICKVAL( Inpatient_Hospital_Co_pay_Co_ins_IH__c ,""), 0 , 1 ) + 
IF ( LEN( Co_pay_Co_ins_IH__c ) = 0, 0 , 1 )
)
/(IF (ISPICKVAL (Carrier__c, "Main"), 13, 30))))

 

sfdcfoxsfdcfox

Consider "ISBLANK" instead of LEN(FIELD)=0; that should reduce the code size somewhat.

 

A better solution is to use a Workflow Field Update. Those formulas have greater leniency than a normal formula, since they are calculated only upon saving. Since your data appears to be solely dependent upon that record's data (i.e. does not cross object boundaries), this would be a better solution. Formula fields are better at "moving targets", such as when the outcome depends on the current day/time.

 

You'll have to create a normal field (one that can store data), and make it read-only (to avoid confusing users). Then, set a workflow rule to the field, and update all records with the data loader to force a recalculation. Note that when you (or any admin with Edit Read-Only Fields) views a record, it will still appear editable, but if your workflow is set up correctly, it will still overwrite the value in the field with the correctly calculated value.

szesze20szesze20

Thanks sfdcfox. I tried what you suggested, it seems to get me closer to what I wanted to achieve, at least now it doesn't give me the error. Just to make sure I understood you correctly - should I put the criterion: Carrier = Main as the Workflow criterion? and then set the Field Update (as a formula to set the value) based on this Carrier criterion? I initially had the following in my Formula field:

IF  (ISPICKVAL (Carrier__c, "Main")

 Thanks again, I feel I'm getting closer!