Written by Eoin on 16 Feb 2007 — Tagged with: Formulas, Validation Rules

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.

Written by Eoin on 10 Jan 2007 — Tagged with: Formulas, Validation Rules

Leading on from my last post I’m going to go into further detail of building your validation rules.

Step 1: Understanding formulas

First off you need to understand the basics of building formulas in salesforce. To do this I’d start looking at all the help and training information on formulas which salesforce already provide, this can easily be found by going to the Help & Training Link in salesforce and searching for the term Building Formulas. There is a wealth of information available here which can be extremely useful to the new salesforce administrator.

Also find below two useful links for getting used to building formula fields.

• Breeze Presentation on building formulas
• Resources on successforce.com for building formulas

Once you’re comfortable building normal formulas the next step is to have a look at the resources below. These useful resources are all about validation rules specifically.

• Breeze presentation on building Validation Rules
• Resources on successforce.com

At this point you should have the basics.

Once you have the basics in building these things you need to try it out. I don’t believe it’s a good idea to try things out in your production salesforce account as some of the fields you’re adding may confuse your users.

I suggest either getting a sandbox; you can get one by contacting your salesforce support rep. They’re an extra charge but I believe they’re worth it. As you can copy all your customization to it and then work with the fields you have in your production instance. Check it out here.

If you don’t have the money for this you can get a free developer account by joining the developer network.

Well that’s it for today but this should get you on the road to being a formula master

Written by Eoin on 8 Jan 2007 — Tagged with: Formulas, Validation Rules

With the upcoming Winter’07 release of salesforce there is one of my favourite new features being added. However it comes at a price, that price being the fact that you have to write formulas. The feature I’m talking about is Validation Rules these allow admins to specify formulas which limit what a user can enter into a field in salesforce.

This useful feature also allows admins to specify a custom error message and also where they would like the error to appear. At the top of the screen or at the field itself.
The final useful thing to know about this feature is the order in which it is applied in salesforce along with workflow because this can be exploited to give your users messages. The order is as follows:

1. Salesforce Standard Validation (email address in correct format)
2. Custom Validation Rules
3. Workflow is fired (emails sent, fields updated, etc..)

The formulas you have to write for validation rules are quite specific. I’ll be writing a short guide over the next while on writing formulas for Validation rules.

While writing validation rule formula you are writing for the error meaning if your formula returns a true value the error will appear. Its best to use bolean formulas to do this. Don’t use the IF function as it returns a value not a true or false value.

Here is an example of a formula written to force users to enter a valid state code for the US.

AND ( OR(BillingCountry = "US", BillingCountry="USA", LEN(BillingCountry)=0), OR( LEN(BillingState) < 2, NOT( CONTAINS("AL:AK:AZ:AR:CA:CO:CT:DE:DC:FL:GA:HI:ID:" & "IL:IN:IA:KS:KY:LA:ME:MD:MA:MI:MN:MS:MO:MT:NE:NV:NH:" & "NJ:NM:NY:NC:ND:OH:OK:OR:PA:RI:SC:SD:TN:TX:UT:VT:VA:" & "WA:WV:WI:WY", BillingState) )))

NOTE: I've added in the concatenations to make this more readable these are not essential but won't effect the formula itself. I'll leave it at that for today and I'll keep adding more and more as I go along.