1

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
| improve this question | |
  • 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
2

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

| improve this answer | |
  • 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
1

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.

| improve this answer | |
  • Nice idea with LEN! – Wookie88 Oct 7 '16 at 10:07
0

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.

| improve this answer | |
0

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)
| improve this answer | |

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.