function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
LGSOChairLGSOChair 

Pulling results from formula into other fields: my formulae are too long

Hello
I have a very complex step of calculations that refer to each other in sequence. This is causing problems as the total length of the formula has to be less than 5000 characters and Salesforce support are saying that even though there's no way 5000 characters in all the formulae, it all adds up in some complicated way.

I'm trying to calculate an organisation's membership fee which is based on:

Base fee =
IF(ISPICKVAL( Membership_Category__c,"A"),(110+Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"B"),(230+Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"C"),(500 +Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"D"),(875+Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"E"), (2175+Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"F"),(3800+Unrestricted_funds__c*0.0002),
IF(ISPICKVAL( Membership_Category__c,"G"), (4500+Unrestricted_funds__c*0.0002),0)))))))

Distance from London adjustment =
IF(ISPICKVAL(Distance_From_London__c,"M25-150m"),( base_fee__c *0.95),
IF(ISPICKVAL(Distance_From_London__c,"150-300m"),(base_fee__c *0.90),
IF(ISPICKVAL(Distance_From_London__c,">300m"),( base_fee__c *0.85),base_fee__c )))

Application period adjustment =
IF(ISPICKVAL(  Application_Period__c , "April-June"), ( Distance_from_london_amount__c -  Agreed_Additional_Discount_Amount__c ),
IF(ISPICKVAL(  Application_Period__c , "July-Sept"), ( Distance_from_london_amount__c * 0.75 -  Agreed_Additional_Discount_Amount__c ),
IF(ISPICKVAL(  Application_Period__c , "Oct-Dec"), ( Distance_from_london_amount__c * 0.5 - Agreed_Additional_Discount_Amount__c ),
IF(ISPICKVAL(  Application_Period__c , "Jan-March"), ( Distance_from_london_amount__c * 0.25 - Agreed_Additional_Discount_Amount__c ), Distance_from_london_amount__c ))))

As you can see, the three formulae are no where near 5000 characters long, but checking the syntax on the third formula says that there are over 22,000 characters....

So, the only thing I can think of is creating a field that isn't a formula, but simply copies the result from the Base Fee calculation fee as this would reset the character count. But I don't know how to do this.

Is there a bit of code I can use to pull the info from one field into another?

Thanks
Nicole
RickyGRickyG
Nicole -

Not sure if this would completely solve the problem, but you could use a CASE statement instead of all those ISPICKVALs.  It also looks like some of the calculations are 'regular', so you could may save some of the values in a custom object, do a VLOOKUP to retrieve them, and greatly simplify the formula that way.

Hope this helps.
J RoamJ Roam
About the count:
The reason why you're getting such a high number is that any formula you refer to in a formula will bring in that entire formula (and character count).
So let's say your Base Fee formula has 1000 characters
Each Line of your "Distance from London Adjustment" is length of the text (about 80 characters) + 1000 characters (every time you refer to "base_fee__c" formula).  So the "Distance from London" formula  = 4320 characters.
Your Application period adjustment has text and refers to the "Distance from London Adjustment" = (100 characters of text per line + 4320 characters for "Distance from London" formula) X 5 times referenced formula = 22100 characters
Ergo your error.

Case would save you some characters - but not enough for all 3 formulas.

I would pursue VLOOKUP to save the maximum number of characters
LGSOChairLGSOChair
Oh thanks, this is brilliant. VLOOKUPS sound ideal. One question about it though. I'm not sure what you mean when you say create a custom object and save the values in there?

And by that I mean, do I create a custom object called say "Values" and then create a field in that called "Base Fee"?

Because I don't quite understand how I would then record that the base fee for Category A is £110.

I'm familiar with VLOOKUPS from excel and I'm happy to create the formula, I'm not sure I understand the bit before that!

Thanks
Nicole
RickyGRickyG
You got it - except that you would have another field which identified which category the base fee was for.  The custom object is basically there to hold values for the lookups.

From the look of your formula, seems like there is a very orderly progression there, so keeping the values in a separate object would work - and have the side benefit of being able to change when the pricing structure changed without reaching into the application.
LGSOChairLGSOChair
Sorry, but I'm not getting it. So:

1) I create a custom object called Values.
2) I create a field within Values called Base fee

then what?

How do I record that if the membership category is A then the fee is £110?

I can't see a field type that allows a series of values to be entered. And if I create a record to actually record the information, does it just sit in salesforce along with all my contacts and accounts?

I'm sorry! Can you explain step by step?

Thanks so much for your help.

Nicole