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 Alessandro on 31 Jan 2007 — Tagged with: Custom Links, Formulas, Script, URLs

In my last post I explained how to build a simple web custom link; more complex custom links can be used to pass data from your Salesforce records, user information, or company information to another website or application; it can also be used to pass variables from one Salesforce object to another.

Let’s see how to create a custom link to pass fields values from a Salesforce object to another.

Scenario

We give support to different companies and accordingly to what kind of support type a company has, their cases have to be handled differently. We’ve created a custom field for the Account object called Support Type, of type Picklist, containing the variables: “Silver, Gold, Platinum“; we created the same Support Type field for the Case object, same type, same picklist values. We now need to create a custom link in the Account object so when we click on it, we get redirected to a Case page where Account Name and Support Type are filled accordingly to the values present on the Account record.

How to proceed – a step by step guide

First of all we need to find out where in the Case record page our values are going to be “written” to; we basically need to find out the ID of the HTML INPUT fields: Support Type and Account Name.

UPDATE: In order to find a field’s ID please check this post

Let’s now move to: Setup > App Setup > Customize > Accounts > Buttons and Links, click on the New button, specify a Label (eg: “New Case”), the Name will be filled in automatically, select the Display Type, the Behavior, make sure the Content Source is URL.

We’re now about to start writing our custom link; the last thing we need is the Object’s code for the Case object: 500 (where did I get this from? Did you read all the old posts? ).

1. Our custom link will start with: /500/e? which points to the edit page of a new case record.
2. Now we can pass the variables: cas4, the ID of the Account Name INPUT field in the Case object, will have to contain the actual account name, so we can write cas4= and select Account Name from the Insert Field drop-down list. This will automatically insert {!Account.Name}.

   So far we have:

   /500/e?cas4={!Account.Name}

3. We have to add an & (ampersand) within the variables.
4. 00N20000000thpg, the ID of the Support Type INPUT field in the Case object, will need to contain the value of the selected account’s Support Type field, so we can write 00N20000000thpg= and select Support Type from the Insert Field drop-down list. This will automatically insert {!Account.SupportType__c} in the textarea. So far we have:

   /500/e?cas4={!Account.Name}&00N20000000thpg={!Account.SupportType__c}

5. Doesn’t it look good? And it’s almost complete. We can add a retURL to point the user back to the account’s page when the case is saved. Just add retURL=/{!Account.Id} and we’re done! All together!:

   /500/e?retURL=/{!Account.Id}&cas4={!Account.Name}&00N20000000thpg={!Account.SupportType__c}

This should give you an idea of how a custom link works. I hope I’ve been clear enough, if you have any question, just add a comment.

Written by Alessandro on 16 Jan 2007 — Tagged with: Custom Links, Formulas, Script

From Salesforce’s Help&Training: “Your organization’s Administrator can set up Custom Links for Home, Campaigns, Leads, Accounts, Contacts, Opportunities, Cases, Contracts, Solutions, Products, Assets and Users. These Custom Links allow your organization to link salesforce.com data with your back-end office systems. They can be directed to external URLs or to your company’s intranet. In addition, the administrator can specify fields to be included as tokens within the URL. For example, you could include an account name in a URL that searches your accounts-payable system“.

These “tokens” are normally called Merge fields; “Merge fields serve as placeholders for data that will be replaced with information from your records, user information, or company information“.

Suggested trainings

• Getting Started
• Sample Buttons & Links

A simple web custom link

We can create a simple custom link to search on google.com for an account name:

http://www.google.com/search?q={!Account_Name}

• http://www.google.com/search?q= is the URL we use to search on google;
• {!Account_Name} is the merge field for the account name.

We are ready now to create the custom link, so login to your Salesforce organization and:

1. Click on: Setup > App Setup > Customize > Accounts > Buttons and Links > New
2. Specify a Label (eg: “Google Account Name”)
3. The Name will be auto populated
4. Select the Display Type
5. Select the Behavior
6. Make sure the Content Source is URL
7. Copy&paste the custom link:
   http://www.google.com/search?q={!Account_Name}
8. Click Save

You can display all the available Merge fields by selecting the Object in the Select Field Type picklist, and the relative field in the Inster Field picklist.

The Link URL you enter can be up to 3000 bytes. When data is substituted for the tokens in the URL, the link may exceed 3000 bytes. Your browser may enforce additional limits for the maximum URL length. Maximum URL Length is 2083 Characters in Internet Explorer, not sure about Firefox, definitely more than 65536.

Update: the 2nd part of the tutorial is available here.

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.