0

I am trying to write a formula field which uses another referenced formula inside it, hence the compilation size is longer than 5,000 characters. Does anybody know how to reduce the size of it?

This is the Project_Difficulty_Score__c field

if(A__c , 1 ,0) + 
if(A1__c , 2, 0)+ 
if(A2__c, 0, 1) + 
if(A3__c, 2, 0) + 
if(A4__c, 1,0 ) + 
if(A5__c, 2, 0) + 
if(A6__c,2,0) + 
if(isPickVal(A7__c,"Easy"),2, 
if(IsPickVal(A7__c,"Medium"), 1, 0) ) + 
if(A8__c>10000,3,if(AND(A8__c<10000,A8__c>=2000),2,1)) + 
if(A9__c=="Repeat",3,1)

and this is the Project_Difficulty__c field

if(Project_Difficulty_Score__c>11, "1 High", 
if(AND(Project_Difficulty_Score__c>5, Project_Difficulty_Score__c<12),"2 Medium" 
,if(Project_Difficulty_Score__c>1 ,"3 Low","4 No Priority")))
  • lines 8 and 9 in the difficulty_score formula seems to be incorrect syntax? – Guy Clairbois Jun 7 '16 at 8:26
  • the Project_difficulty_score__c works perfectly fine – Marc Zaharescu Jun 7 '16 at 8:28
  • OK. I'd expect 3 parameters in the IF functions: IF(logical_test, value_if_true, value_if_false) There seem to be only 2 in those lines. – Guy Clairbois Jun 7 '16 at 8:32
  • 1
    So maybe it's a copy-paste error, but this part of the formula has a closing parenthesis right after value_if_true: if(A7__c,"Easy"),2, – Guy Clairbois Jun 7 '16 at 10:48
  • 1
    yes you were right, I forgot to add the isPickVal above, now should be fixed – Marc Zaharescu Jun 7 '16 at 11:00
4

If it can be assumed Project_Difficulty_Score__c would be positve number all the time below formula can be used:

CASE(Project_Difficulty_Score__c,
0, "4 No Priority",
1, "4 No Priority",
2, "3 Low",
3, "3 Low", 
4, "3 Low", 
5, "3 Low", 
6, "2 Medium",
7, "2 Medium",
8, "2 Medium",
9, "2 Medium",
10, "2 Medium",
11, "2 Medium",

"1 High")

Advantage would be that this will reduce number of Project_Difficulty_Score__c references to 1 and thereby decreasing formula length considerably

  • +1 from me. To make it even more clear, every reference (to another formula field) in a formula field adds the compiled size of that referenced formula to the compiled size of the formula you're working on. Most of the formula field optimization that I've done involves either (and sometimes, both) 1) reducing the number of references to other formulae. 2) Using math to simplify/transform formulae. – Derek F Jun 7 '16 at 13:38
3

You could make use of the fact that in IF functions the conditions are checked one by one and once 1 is met, it doesn't need to evaluate the rest. So once you conclude that score is not >11, you don't have to check that it's lower than 12. That will save you 25% of formula size (I hope that is enough).

So turn Project_Difficulty__c into:

if(Project_Difficulty_Score__c>11, "1 High", 
if(Project_Difficulty_Score__c>5, "2 Medium",
if(Project_Difficulty_Score__c>1 ,"3 Low",
"4 No Priority")))
  • thank,s for your answer. So apart of this isn't there anything else I can do to reduce it? without relying to workflows or triggers – Marc Zaharescu Jun 7 '16 at 8:46
  • you could apply the same principle to the A8 part of the upper formula. Or revert to the Case solution mentioned in the other answer. It's more efficient in formula usage but might be a bit more work to maintain. – Guy Clairbois Jun 7 '16 at 10:45
0

A workflow rule can be used to make it work. Consider there is a new field Project_Difficulty_Score_Number__c of Number data type. Now it can be assigned a workflow field update with formula value :

if(A__c , 1 ,0) + 
if(A1__c , 2, 0)+ 
if(A2__c, 0, 1) + 
if(A3__c, 2, 0) + 
if(A4__c, 1,0 ) + 
if(A5__c, 2, 0) + 
if(A6__c,2,0) + 
if(A7__c,"Easy"),2, 
if(A8__c,"Medium"), 1, 0) ) + 
if(A9__c>10000,3,if(AND(A9__c<10000,A9__c>=2000),2,1)) + 
if(A10__c=="Repeat",3,1)

(Same as your Project_Difficulty_Score__c formula)

Now you can replace your Project_Difficulty_Score__c formula to use Project_Difficulty_Score_Number__c in place of Project_Difficulty_Score__c .

  • yeah I know I can do this with a workflow but I'd prefer to only reduce the formula size if possible – Marc Zaharescu Jun 7 '16 at 8:44

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.