ShowAll Questionssorted byDate Posted
Kathi

# 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))))))

Thanks

Kathi

Shannon 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.

Shannon 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
Kathi

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 Hale