How to give formula field dynamically in Visual force, I am using PE in salesforce

Question

when I save the below formula, it shows this error:

Error: Compiled formula is too big to execute (19,959 characters). Maximum size is 5,000 character

if the sum of Value_1__c to Value_13__c <= 10, then display level 1 if the sum of Value_1__c to Value_13__c >10 and Value_1__c to Value_13__c <= 18, then display level 2 if the sum of Value_1__c to Value_13__c >18 then level 3

formula value:

if((Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c) <= 10
&&
(Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c ) <> 0 , "Level 1",
if( AND((Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c + Value_10__c + Value_11__c + Value_12__c + Value_13__c) > 10
&&
(Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c) <= 18), "Level 2",
if((Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c) > 18,"Level 3",
"Level Not Calculated Yet" )))

All field Value_1__c to Value_13__c also formula field which get s the value from the picklist selected.

we tried this also:

total_risk_cal__c = Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c

if((total_risk_cal__c) <= 10
&&
(total_risk_cal__c ) <> 0 , "Level 1",
if( AND((total_risk_cal__c) > 10
&&
(total_risk_cal__c) <= 18), "Level 2",
if((total_risk_cal__c) > 18,"Level 3",
"Level Not Calculated Yet" )))

but getting same error. We are trying an alternate method of creating a whole page using VF. We didn't get any ideal for how to give fomula field dynmaically in Visual force we are using professional edition. Kindly any one tell how to dao this and please send me any sample code for this also some links. Thanks in advance, waiting for you reply.

Answer 1

Try to create a new formula field for the Value_1__c + Value_2__c +Value_3__c + Value_4__c + Value_5__c + Value_6__c + Value_7__c + Value_8__c + Value_9__c+ Value_10__c + Value_11__c + Value_12__c + Value_13__c and use it in your main formula.

Well, now i understand your problem :) Because it is realy hard to optimize the nested formula i would try to generalize the conditions you have:

1. total_risk_cal__c: >0  && <=10  ---> "Level 1"
2. total_risk_cal__c: >10 && <= 18 ---> "Level 2"
3. total_risk_cal__c: >18          ---> "Level 3"
4. total_risk_cal__c: NULL or 0    ---> "Level Not Calculated Yet"

I've assumed that the value of total_risk_cal__c can not be a negative number. So now let's try to use the CASE instead of multiply IF:

CASE(total_risk_cal__c, 
     NULL, "Level Not Calculated Yet",
     0,    "Level Not Calculated Yet",
     1, "Level 1", 
     2, "Level 1", 
     3, "Level 1", 
     4, "Level 1", 
     5, "Level 1", 
     6, "Level 1", 
     7, "Level 1", 
     8, "Level 1", 
     9, "Level 1", 
     10, "Level 1", 
     11, "Level 2", 
     12, "Level 2", 
     13, "Level 2", 
     14, "Level 2", 
     15, "Level 2", 
     16, "Level 2", 
     17, "Level 2", 
     18, "Level 2", 
     "Level 3")