# Compiled formula is too big to execute

When saving "Total_Point_Percentage__c" I get the error "Compiled formula is too big to execute (6,243 characters). Maximum size is 5,000 characters (Related field: Formula)"

Is there an alternative way of calculating this or a way of reducing the size to under the max of 5,000?

• Filed1 - Picklist - Yes,No
• Filed2-10 - Picklist - 1-10
• Filed11-13 - Picklist - 1-5
• Total Points - Formula
• Percentage Total - Formula
• Total Point Percentage - Formula

Total_Point__c

``````IF(ISPICKVAL(Field1__c,'Yes'),
VALUE(TEXT(Field2__c)) +
VALUE(TEXT(Field3__c)) +
VALUE(TEXT(Field4__c)) +
VALUE(TEXT(Field5__c)) +
VALUE(TEXT(Field6__c)) +
VALUE(TEXT(Field7__c)) +
VALUE(TEXT(Field8__c)) +
VALUE(TEXT(Field9__c)) +
VALUE(TEXT(Field10__c)) +
VALUE(TEXT(Field11__c)) +
VALUE(TEXT(Field12__c)) +
VALUE(TEXT(Field13__c)), 0 ) +
IF(ISPICKVAL(Field1__c,'No'),VALUE(TEXT(0)), 0)
``````

Percentage_Total__c

``````IF (ISBLANK(TEXT(Field2__c)),0, 10) +
IF (ISBLANK(TEXT(Field3__c)),0, 10) +
IF (ISBLANK(TEXT(Field4__c)),0, 10) +
IF (ISBLANK(TEXT(Field5__c)),0, 10) +
IF (ISBLANK(TEXT(Field6__c)),0, 10) +
IF (ISBLANK(TEXT(Field7__c)),0, 10) +
IF (ISBLANK(TEXT(Field8__c)),0, 10) +
IF (ISBLANK(TEXT(Field9__c)),0, 10) +
IF (ISBLANK(TEXT(Field10__c)),0, 10) +
IF (ISBLANK(TEXT(Field11__c)),0, 5) +
IF (ISBLANK(TEXT(Field12__c)),0, 5) +
IF (ISBLANK(TEXT(Field13__c)),0, 5)
``````

Total_Point_Percentage__c

``````Total_Point__c / Percentage_Total__c * 100
``````
• It might make sense to make whatever you are storing in fields 1-13, child records (in a MD relationship) of this object. Not only for future expansion but then you could do roll-up summaries and calculations much easier. Anytime you find yourself adding a counter to a field name you should be asking if these fields should be children of the parent. – Eric Oct 7 '16 at 6:27

Make use of a workflow rule updating a total points field every time one of the points fields changes. Or use process builder with the same approach

• I think this may be the only only option, I will give it a go and see what happens. Thanks – Brendan Murphy Oct 8 '16 at 15:42

Try the following formulas:

Total_Point__c

``````IF(ISPICKVAL(Field1__c,'Yes'),
VALUE(TEXT(Field2__c)) +
VALUE(TEXT(Field3__c)) +
VALUE(TEXT(Field4__c)) +
VALUE(TEXT(Field5__c)) +
VALUE(TEXT(Field6__c)) +
VALUE(TEXT(Field7__c)) +
VALUE(TEXT(Field8__c)) +
VALUE(TEXT(Field9__c)) +
VALUE(TEXT(Field10__c)) +
VALUE(TEXT(Field11__c)) +
VALUE(TEXT(Field12__c)) +
VALUE(TEXT(Field13__c)),
0 )
``````

Percentage_Total__c

``````LEN(
LEFT(TEXT(Field2__c), 1) &
LEFT(TEXT(Field11__c), 1) &
LEFT(TEXT(Field13__c), 1)
) * 5
+
LEN(
LEFT(TEXT(Field3__c), 1) &
LEFT(TEXT(Field4__c), 1) &
LEFT(TEXT(Field5__c), 1) &
LEFT(TEXT(Field6__c), 1) &
LEFT(TEXT(Field7__c), 1) &
LEFT(TEXT(Field8__c), 1) &
LEFT(TEXT(Field9__c), 1) &
LEFT(TEXT(Field10__c), 1) &
LEFT(TEXT(Field12__c), 1)
) * 10
``````

There is a discrepancy in your formula - when you provided in explanation that `Filed11-13 - Picklist - 1-5` but `Filed2`, `Filed11`, and `Filed13` is being assigned with max value of 5.

• Nice idea with `LEN`! – Wookie88 Oct 7 '16 at 10:07

There are some optimizations you can do. However, I am not sure that they will help - you exceeded the formula limit for quite big number.

• `VALUE(TEXT(0))` can be just 0 in first formula
• You can remove `IF(ISPICKVAL(Field1__c,'No'),VALUE(TEXT(0))` entirely and just leave 0 - if Picklist is not set to 'Yes' then it has to be 'No'. If you are worried about blank value, make the picklist required and optionally set default value

To optimize further you could for example change Fields 2 - 13 to number fields. Set min and max value, best with VR (use one VR for all fields - it will be easier to maintain). Then you can just use their values in formula (instead of repeating `VALUE(TEXT(FieldName))` ) - this optimization will be surely enough to go under the limit.

I made the following changes and managed to get complied version down to 4,032.

Added '0' to the options in the picklist fields 2-13 and made the below change to percentage total. Thanks all for help.

Percentage_Total__c

``````IF(ISPICKVAL(Field2__c,'0'),0, 5) +
IF(ISPICKVAL(Field3__c,'0'),0, 10) +
IF(ISPICKVAL(Field4__c,'0'),0, 10) +
IF(ISPICKVAL(Field5__c,'0'),0, 10) +
IF(ISPICKVAL(Field6__c,'0'),0, 10) +
IF(ISPICKVAL(Field7__c,'0'),0, 10) +
IF(ISPICKVAL(Field8__c,'0'),0, 10) +
IF(ISPICKVAL(Field9__c,'0'),0, 10) +
IF(ISPICKVAL(Field10__c,'0'),0, 10) +
IF(ISPICKVAL(Field11__c,'0'),0, 5) +
IF(ISPICKVAL(Field12__c,'0'),0, 10) +
IF(ISPICKVAL(Field13__c,'0'),0, 5)
``````