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
Sheena QueridoSheena Querido 

Formula referencing another formula - too many characters?

I tried creating a formula, but I got an error message that said there were too many characters in my formula.  Any ideas on how to shorten the formula is much appreciated!
 
Error: Invalid Data. 
Review all error messages below to correct your data.
Compiled formula is too big to execute (9,526 characters). Maximum size is 5,000 characters (Related field: Formula)
  
IF(ISPICKVAL(Frequency_of_Service__c, "One-time"),
ABS((Cost__c * Probability) / 1),

IF(ISPICKVAL(Frequency_of_Service__c, "Yearly"),
ABS((Cost__c * Probability) / 1),

IF(ISPICKVAL(Frequency_of_Service__c, "Twice Yearly"),
ABS((Cost__c * Probability) / 2),

IF(ISPICKVAL(Frequency_of_Service__c, "Quarterly"),
ABS((Cost__c * Probability) / 4),

IF(ISPICKVAL(Frequency_of_Service__c, "Monthly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Frequency_of_Service__c, "Twice Monthly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Frequency_of_Service__c, "Weekly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Frequency_of_Service__c, "Twice Weekly"),
ABS((Cost__c * Probability) / 12),

IF(ISPICKVAL(Frequency_of_Service__c, "Daily"),
ABS((Cost__c * Probability) / 12),

0
)))))))))

Where the field Cost__c is another formula:
  
IF(ISPICKVAL(Margin__c , "60"), 
ABS((Amount -(0.60 * Amount))), 

IF(ISPICKVAL(Margin__c , "50"), 
ABS((Amount -(0.50 * Amount))), 

IF(ISPICKVAL(Margin__c , "40"), 
ABS(( Amount -(0.40 * Amount))), 

IF(ISPICKVAL(Margin__c , "45"), 
ABS((Amount -(0.45 * Amount))), 

IF(ISPICKVAL(Margin__c , "35"), 
ABS((Amount -(0.35 * Amount))), 

IF(ISPICKVAL(Margin__c , "30"), 
ABS((Amount -(0.30 * Amount))), 

0 
))))))
Best Answer chosen by Sheena Querido
Alain CabonAlain Cabon

IF (CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,0) > 0, ABS((Cost__c * Probability) / CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,1)),0) 

Notepad++ for the open/closed parenthesis :

User-added image

Regards

Alain
 

All Answers

Akhil ReddyAkhil Reddy
Create a Proxy text field, then update that field with a Field Update and then reference the proxy field in other formula field. It should minimize the compile size
Alain CabonAlain Cabon
Cost__c: 
IF(VALUE(TEXT(Margin__c)) > 0,  ABS(Amount -(VALUE(TEXT(Margin__c))/100 * Amount)),0) 
Alain CabonAlain Cabon
Main formula combining IF and CASE:
IF (CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,...,0) > 0,
ABS((Cost__c * Probability) / CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,...,1),0)
Sheena QueridoSheena Querido
Hi Alain, Thanks for the feedback. I tried your formula like so: IF (CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,0) > 0, ABS((Cost__c * Probability) / CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,1),0)) But I keep getting this error message: Error: Incorrect number of parameters for function 'ABS()'. Expected 1, received 2 Am I doing something wrong? Sheena Querido Systems Project Manager Ingenium Phone: 760-745-8780 Mobile: 760-484-1345 Fax: 760-745-8786 www.pureingenium.com Linked-IN Leading innovators of hazardous waste management with a focus on sustainability. This message contains confidential information and is intended only for the intended recipients. If you are not an intended recipient you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.
Alain CabonAlain Cabon

IF (CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,0) > 0, ABS((Cost__c * Probability) / CASE(TEXT(Frequency_of_Service__c), "One-time",1,"Yearly",1,"Twice Yearly",2,"Quarterly",4,"Monthly",12,"Twice Monthly",12,"Weekly",12,"Twice Weekly",12,"Daily",12,1)),0) 

Notepad++ for the open/closed parenthesis :

User-added image

Regards

Alain
 
This was selected as the best answer
Sheena QueridoSheena Querido
That did the trick, thank you so much!! :] Sheena Querido Systems Project Manager Ingenium Phone: 760-745-8780 Mobile: 760-484-1345 Fax: 760-745-8786 www.pureingenium.com Linked-IN Leading innovators of hazardous waste management with a focus on sustainability. This message contains confidential information and is intended only for the intended recipients. If you are not an intended recipient you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.