100 Ways to Use the New Custom Formula Fields
May 27, 2005The buzz around custom formula fields is just beginning to take off as people realize the potential for this exciting new feature. Available to all salesforce.com customers with the Summer ’05 Release, custom formula fields will allow you to dynamically compute excel-like functions inside of salesforce.com.
Some of the more exciting applications include commission calculations, date calculations, lead scoring, and time tracking. Download the PDF to see all 100 sample formulas.
100 Ways to Use Formula Fields
Quick Reference Guide
Detailed Feature Description
Bookmark
Can a formula field refer to fields of other objects (standard or custom)?
We would like to create a formula that calculates "tier-ed" pricing based on few key elements: subscription length, number of users and type of client.
Thanks
Hitesh
Posted by: Hitesh Patel | June 14, 2005 at 06:42 AM
Hi Hitesh,
In this release, custom formulas can only reference other fields within the same object.
We are planning to enhance this in the future.
Have you seen the Custom Formulas blog? This question and many others are answered there, along with tips & techniques and examples.
http://crmsuccess.blogs.com/custom_formula_fields/2005/06/can_a_custom_fo.html
Cheers,
Eric Bezar
Customforce Product Management
Posted by: Eric B | June 18, 2005 at 07:55 AM
I need to produce a report by State. I currently do not capture address but do capture Phone #. How can I convert area code to applicable state.
Posted by: Danielle Rafuse | August 30, 2005 at 01:58 PM
Danielle my custom formula expert has his head down working on things for Dreamforce, but this logic might help you get closer to your answer. This formula is designed to get time zones from phone numbers. It’d be very similar getting states from phone numbers.
Area Code and Time Zone Fields
The vertical lines or semi colons are delimiters. They don’t have any special powers -- they just have to be characters that are not going to be in the phone number.
One problem with Michael’s code below is that the CONTAINS parameters are backwards. The first parameter should be the string that you are searching within. CONTAINS will only return True if the string in the second parameter is found within the first string.
So the formula below will probably never return true because the first 5 characters of Phone will never be exactly equal to a 3-character area code.
CASE(1,
IF( CONTAINS("212:718", LEFT({!Phone},5),),1,0),"East",
"Unknown")
If you have a good idea of all the combinations of phone number formatting that your data contains, you could put all combinations of formatting in the first parameter.
Let’s say your phone numbers are always going to be one of these 3 formats:
(999) 999-9999
999-999-9999
999.999.9999
999 999-9999
Your formula could be
CASE(1,
IF( CONTAINS("(415:415-:415.:415 :(510:510-:510.:510 ", LEFT({!Phone},4)),1,0), "West",
IF( CONTAINS("(212:212-:212.:212 :(718:718-:718.:718 ", LEFT({!Phone},4)),1,0), "East",
"Unknown")
You end up with a very long string for the Contains parameter, but this is still probably more efficient than doing all the phone number parsing in an IF statement.
Posted by: Gforce | September 01, 2005 at 09:43 AM
I need to calculate the day of the week that a case was opened, but Created Date or Open Date aren't available fields to use in the calculated field. When I try to validate the syntax, I get an error message - "Error: Incorrect parameter for function. Expected number/date time received date.
CASE(MOD( {!CreatedDate} - DATE(1900, 1, 5), 7), 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday","")
How can I do this?
Also, Is there a formula for determining the time of day the case was opened without the date?
Thanks,
Dave
Posted by: David Corken | September 26, 2005 at 10:39 AM
Regarding the Day of the Week calculation, another customer discovered a backdoor way to do this and posted it on the CRM Success message boards:
http://forums.crmsuccess.com/sforce/
board/message?
board.id=discuss&message.id=1566
Please be aware that this method has some significant limitations related to timezone conversion. Here is my response to the above post:
http://forums.crmsuccess.com/sforce/
board/message?
board.id=discuss&message.id=1788
Note: I had to put carriage returns in these URLs to get them to display. You may have to re-join them or directly type into browser.
Posted by: Eric Bezar | September 26, 2005 at 05:54 PM
I strongly suggest make the custom formula to be able to reference a field from another tab or a related list!!! If not, we have to either create too many fields in one tab, or create duplicate data/fields in separate tabs. I don't think this solution is competitive in either case.
Please, make this happen ASAP!!!
Thanks.
Posted by: Chuankai | March 28, 2006 at 11:01 AM
Chuankai: Thanks for the feedback, I'll pass it on.
Posted by: Kingsley | March 29, 2006 at 08:56 AM
I am trying to calculate length of stay by months for my customers. Does anyone have a formula and instructions that show how I can get this accomplished?
Posted by: Justin | June 21, 2007 at 02:53 PM
The "100 Ways to Use Formula Fields" link is not working. Please update.
Thanks.
Posted by: cwhiffen | May 12, 2009 at 10:43 AM
Ive gone round and round trying to build a simple formula, but I cant seem to make it so.
Essentially, I have a custom object that I am trying to calculate rates with the exception of 1 account.
Simply put, I am trying to say: If my picklist equals YES, then multiply hours field by rate field, UNLESS the account number is 11 then multiply hours field by 390.
Seems simple, but somehow I can seem to make it work.. thoughts?
Posted by: Sandy | November 09, 2009 at 11:07 PM