+ Start a Discussion
KathiKathi 

Need help with shortening a formula - optional products on quotes

Hi everyone,

 

we are trying to find a way on adding optional products on quotes. Since you cannot change the quantity to "Zero" we've tried to find a way in displaying "not offered" in the total price for those products. I've created a text field formula, that would work perfectly, if we weren't using 3 different currencies in our company EUR, GBP, USD).


Unfortunately the formula is too long (Error message: Compiled formula is too big to execute (5,341 characters). Maximum size is 5,000 characters).

 

Therefore we need to find a way to shorten it. Unfortunately we've couldn't find a way internally, that's why I'm asking the community.

 

The formula, that needs to be shortened is the following. SP_Q__c is a currency formula field totaling the price of a product incl. any discounts. The formula should convert the number into a text, while keeping the decimals.

 

CASE(Description,

"Optional", "not offered",

 "optional", "not offered",

 CASE( Quote.Opportunity.CurrencyIsoCode ,

"EUR",  ( "EUR"  & " " &

 

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2)))),

 

"GBP",  ( "GBP"  & " " &

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2)))),

 

 ( "USD"  & " " &

IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",

IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",

     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2))))))

 

Your help is really appreciated.

 

Thanks

 

Kathi

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

Hi Kathi,

 

It looks like for your CASE() statement for the ISO code, you're repeating the same code 3 times -- the only thing that seems to change is the ISO code itself. Because SP_Q__c is referenced in that repeated code 7 times, you're basically multipying the compile size of SP_Q__C x 7 x 3, and adding it to your current formula.

 

I think you can simplify this to be something like the following:

 

IF(
  LOWER(Description) = "optional",
  "not offered",
  TEXT( Quote.Opportunity.CurrencyIsoCode ) & " " &
    IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",
    IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",
     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2))))
)

 

(I didn't get to paste that into a formula editor to validate it, but hopefully you get the idea.)

 

If you use additional currencies beyond EUR/GBP/USD, you might need to replace the last part with another IF() to check for OR(Quote.Opportunity.CurrencyISOCODE = "EUR", Quote.Opportunity.CurrencyISOCODE = "GBP", Quote.Opportunity.CurrencyISOCODE = "USD") but the next line would be the same.

All Answers

Shannon HaleShannon Hale

Hi Kathi,

 

It looks like for your CASE() statement for the ISO code, you're repeating the same code 3 times -- the only thing that seems to change is the ISO code itself. Because SP_Q__c is referenced in that repeated code 7 times, you're basically multipying the compile size of SP_Q__C x 7 x 3, and adding it to your current formula.

 

I think you can simplify this to be something like the following:

 

IF(
  LOWER(Description) = "optional",
  "not offered",
  TEXT( Quote.Opportunity.CurrencyIsoCode ) & " " &
    IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00",
    IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0",
     LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2))))
)

 

(I didn't get to paste that into a formula editor to validate it, but hopefully you get the idea.)

 

If you use additional currencies beyond EUR/GBP/USD, you might need to replace the last part with another IF() to check for OR(Quote.Opportunity.CurrencyISOCODE = "EUR", Quote.Opportunity.CurrencyISOCODE = "GBP", Quote.Opportunity.CurrencyISOCODE = "USD") but the next line would be the same.

This was selected as the best answer
KathiKathi

Hi Shannon,

 

Thank you very much for your help with the formula. You're a star!!!

 

With some adjustment (the currency field is a picklist field) the formula now looks like that:

 

IF( LOWER(Description) = "optional", "not offered", TEXT(Quote.Opportunity.CurrencyIsoCode) & " " & IF(FIND(".",TEXT(SP_Q__c))=0, TEXT(SP_Q__c)+".00", IF (LEN(TEXT(SP_Q__c))-FIND(".",TEXT(SP_Q__c))=1, TEXT(SP_Q__c)+"0", LEFT(TEXT(SP_Q__c),FIND(".",TEXT(ROUND(SP_Q__c,2)))+2))))

 

Have a great day!

 

Kathi

Shannon HaleShannon Hale

Excellent, glad to help.

 

Please mark your answer (or my answer) as the solution so that the post shows up solved on the boards, and others may benefit.