Written by Eoin on Friday 16 February 2007, tagged with: Formulas, Validation Rules — 3 comments so far.

UPDATE: As you can see we received a comment to this post from Eric Bezar from Salesforce.com; they said that this is an exploitation of a bug in the application and it can not be guaranteed to work in the long term. In other words don’t use this method. Daniel gave a very good workaround if you run into this problem: you can use a workflow field update to update the value in a field with a formula value then use that field in your formula. This gets around the compile size limit and it’s something Salesforce.com can support. Thanks Eric for the info

Everyone who creates formulas in salesforce knows how annoying their 4,000 character compile size limit is.

Well, good news! There is a way around it.

Its a simple enough thing to do and according to their support its not something that is going to change in the future but I’m not going to guarantee you they won’t. First of all you need to be aware of the current work around to get past their 1,300 character limit for formulas.

The basic process is to reference one formula off another. For example, if you’re using a case statement and you run out of room you create another another formula and continue your case statement, then in the first formula you put the merge field for the second formula in the else_result position.

E.g.:
Formula 1:
Case(expression,value1,result1,value2,result2,…,value100,result100,formula2)

Formula 2:
Case(expression,value101,result101,value102,result102,else_result)

In my case I have a picklist field which lists all of the countries in which we do installs and when a sales rep closes a sale they need to fill in the country where the install will be.

Once that is done I wanted to do two things:

1. Create a formula to show the region for the installation.
2. Create another formula based on the region and the amount to calculate who the lead on the install will be.

I built the following formula to calculate the region. Its called installation_region:

CASE( Installation_Country__c ,"Albania","EMEA",
"Algeria","EMEA",
"Andorra","EMEA",
"Angola","EMEA",
"ANtigua and Barbuda","US",
"Argentina","US",
"Australia","APAC",
"Austria","EMEA",
"Azerbarjan","APAC",
"Bahamas","US",
"Bahrain","APAC",
"Bangladesh","APAC",
"Barbados","US",
"Belgium","EMEA",
"Brazil","US",
"Bulgaria","EMEA",
"Cambodia","APAC",
"Canada","US",
"Chile","US",
"China","APAC",
"Denmark","EMEA",
"Egypt","EMEA",
"Estonia","EMEA",
"Finland","EMEA",
"France","EMEA",
"Germany","EMEA",
"Greece","EMEA",
"Hungary","EMEA",
"Iceland","EMEA",
"Ireland","EMEA",
"Israel","EMEA",
"Italy","EMEA",
"Japan","APAC",
"Latvia","EMEA",
"Luxembourg","EMEA",
"Macedonia","EMEA",
"Malta","EMEA",
"Mexico","US",
"Monaco","EMEA",
"Mozambique","EMEA",
"Nepal","APAC",
"Netherlands","EMEA",
"New Zealand","APAC",
"Norway","EMEA",
"Poland","EMEA",
"Portugal","EMEA",
"Romania","EMEA",
"Russia","EMEA",
"Singapore","APAC",
"Slovakia","EMEA",
"South Africa","EMEA",
"Spain","EMEA",
"Sweden","EMEA",
"Switzerland","EMEA",
"Thailand","APAC",
"Turkey","EMEA",
"Ukraine","EMEA",
"UAE","APAC",
"UK","EMEA",
"US","US","UPDATE FORMULA")

This compiled to 2,179 characters. Then I built the following formula to calculate the lead and called it installation_lead:

IF(Amount > 100000, CASE( Installation_Region__c , "EMEA","Mark","APAC","Dave","US","James","UPDATE FORMULA") , "Mike")

But this compiled to 6,763 characters so I couldn’t save it.

Then after reading up on something I had a brainwave. The reason the installation_lead wouldn’t save is because it references installation_region which is a very large formula. I copied the original installation_region formula and pasted it into a text editor and I modified the installation_region to be:

CASE( Installation_Region__c ,"Albania", "EMEA", "US", "US", "UPDATE FORMULA" )

This compiled to be 104 Characters long. I saved this simplified version of the formula and tried the second formula again. It then saved and compiled as 538 characters. I saved it and went back and pasted back in my original installation_region formula and it saved without any problem.

Just out of curiosity I checked installation_lead and if I edited it I couldn’t save it because it was over the compile size but on the page layout both work perfectly.

So all you have to do to get around the compile size limit is:

1. Create a simple version of a formula and save it.
2. Create a master formula refrenecing the formula created in step one and make sure its as complicated and as long as it needs to be and save it.
3. Go back and edit the formula created in step one and extend it to contain all of the information you need and save it.

The formula in step 2 may go over the compile size but as it is already saved it won’t stop you using it.

I can’t guarantee you this will work forever but its better then nothing.