The Official Salesforce Blog - Custom Formulas

• Edit and Delete Records Directly from a Report with Custom Buttons

  Apr 26, 2006

  Today on the message boards gokubi posted a great best practice idea for initiating actions directly from a report.

  The way Salesforce's reporting tool works is that you click through to view the record but it's one more click if you want to edit it or delete it. Gokubi created a custom formula field for edit, delete, and add to campaign which can be added as a column to your custom reports.

  

  To see how he did it read the full post on his blog.

  Comments | Bookmark

• Converting Date/Time function output into HH:MM:SS

  Apr 25, 2006

  Spazure Silicon blogged this tip on his/her blog with the caveat that "So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful." So, mainly to prove him/her wrong, but also because it's very useful, here it is - a function to convert Date/Time into HH:MM:SS.

  This particular formula was designed for cases, calculating the elapsed time in HH:MM:SS between right now and the time the case was created. As you see, I had to use the TEXT() function to convert the numerically output Date/Time function results into text, which were then fed into the Concatenate function to make everything display nicely on one field. My original version of this formula actually used multiple fields, {!Hours} {!Seconds} {!Minutes}. This made the final formula look prettier, and helped with debugging, but ultimately was impractical for distribution.
  
  So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful. If you use my formula, please comment -- not because I want credit, but because I really do like to know where my code ends up. :-)

  Text(FLOOR((NOW() - {!CreatedDate}) * 24))

  &":"&Text(FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24))* 60))

  &":"&Text(FLOOR((( (NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 - FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 )) * 60 ))

  Comments | Bookmark

• Dynamically Required Picklists to Capture...

  Mar 23, 2006

  There have been some really interesting best practice ideas emerging from customer and partner blogs. This week Scott Hemmeter wrote about how you could use dependent picklists to create dynamically required picklists.  

  The example he used was a field to capture reason lost, a field that often appears on the opportunity page layout. Are you loosing deals based upon company viability, features, price, lack of references? This is valuable information. The challenge that most companies face is that they can’t make it a required field because the rep won’t know the reason for the loss until it’s closed. On the flip side they can’t ensure that it’s filled out consistently without it being a required.

  

  His simple solution makes the reason for a loss required only when the stage is equal to closed lost. To learn how it’s done, visit Perspectives on Salesforce. Scott great work!

  Comments | Bookmark

• Creating Fiscal Period Formula Fields in a Custom Fiscal Year Org

  Mar 7, 2006

  If your business’s fiscal year is not based on the Gregorian calendar but follows a different structure consisting of fiscal weeks, periods and quarters deviating from the regular calendar year, you can set up your fiscal year structure using the Custom Fiscal Year (CFY) feature in Salesforce.

  For example, your company may run on a “4-4-5” structure and a 13-week quarter may be represented by three periods of four, four, and five weeks rather than by three calendar months. Or may be your fiscal years consist of 13 fiscal periods consisting of 4 weeks each.

  As specified on the CFY enablement page in setup as well as in online help and release notes, a side effect of enabling the CFY feature for an organization is that you will not be able to use the fiscal period columns (Fiscal Year, Fiscal Quarter, and Fiscal Period) in opportunity, opportunity with product, opportunity with schedule reports or opportunity list views otherwise available in non-CFY organizations.

  Here is the tip: Using the formula field capability in Salesforce, you can create custom fields on the opportunity object that can calculate the fiscal year, fiscal quarter, fiscal period or a combination of these for you. These custom fields can then be exposed in all opportunity reports and list views and you can also filter by values in those fields. If you were using the fiscal period columns in opportunity reports prior to enabling CFY, you can recreate the columns via this formula field approach to retain reporting functionality that you may have been used to.

  

  
  

  
  

  
  

  Comments | Bookmark

• Formula Tip: Using Picklist fields with the CASE() function

  Feb 1, 2006

  Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.

  Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required.  This has a number of big advantages:

  1. The formula is easier to create and read
     
  2. Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.

  Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:

  Comments | Bookmark

• Pre-Built Image Fields Available on the AppExchange

  Jan 25, 2006

  To help people experience the power of image fields, I created a very simple AppExchange component which allows you to quickly install nine working examples ranging from priority flags, to product photos, to stock charts.

  This free component takes less than two minutes to install and it’s built on a stand-alone custom tab so you can add it to your account without affecting any of your users.  One thing to note, when you install this component, you'll have to fill in the blanks (score, ticker, image url) for some of the images to appear.

  The idea is that once you have these sample formulas in your account, you can reapply the them to any of your other objects. Give it a try!

  
  

  Comments | Bookmark

• Tips for Creating Your First Image Field

  Jan 11, 2006

  Over the past couple of days we’ve all been digesting the exciting new features in the Winter ’06 release. So far I’d have to say my favorite has been the image fields. It’s amazing how dramatically you can change your users experience with this simple trick.

  For those of you who have watched my Dreamforce Presentation you know that I use Salesforce.com in an unconventional manner but I think that this use case for image fields can be extended to all sorts of situations.

  With image fields I’m now able to get a quick visual representation of what's being published out to the website. I have a field on this page for “image url” and another for “asset url.” With these two data points I was able to create a formula field to displays the thumbnail image for each piece of content and it's even clickable so you can quickly launching the underlying presentation or document.

  HYPERLINK({!Asset_URL_c},
  IMAGE({!Image_URL_c},"Image Not Available"))

  Comments | Bookmark

• Spice Up Your Pages with Image Fields

  Jan 8, 2006

  With the Winter ’06 release, formula fields allow you to incorporate images into standard and custom tabs, list views, and reports. Because people recognize images more quickly than they can read words, adding images to Salesforce can enable users to find the data they’re looking for more quickly. For example,

  • Photos for product catalogs or employee directories
  • Graphical representations of status, priority, or rating
  • Clickable Buttons
  • Online Presence Indicator

  As an example, you can add green, yellow, or red flag images to the Cases tab to provide a quick visual indication of case priority.

  
  

  Formula Return Type:
  Text
  

  Formula:
  IMAGE(
  CASE( {!Priority},
  "Low", "/img/samples/flag_green.gif",
  "Medium", "/img/samples/flag_yellow.gif",
  "High", "/img/samples/flag_red.gif",
  "/s.gif"),
  "priority flag")

  For tips & techniques and many more samples, be sure to check out Getting Started with Image Fields on the Image Fields home page on successforce.com. 

  Comments | Bookmark

• Suggested Documents using Custom Formula Fields

  Oct 24, 2005

  “Do your reps ever complain that it’s hard to find documents in the document library? Do you want to cut down on training? Leverage custom formula fields to suggest resources based upon fielded information in the contact, opportunity, or account record.”

  Now you can deliver different content for your sales force based upon the different stages of their opportunity.

  

  Comments | Bookmark

• Year over Year Comparisons with Formula Fields

  Sep 19, 2005
  

  A common key performance indicator (KPI) in most companies is the "year-over-year" comparison, e.g., "How does sales performance in Q3 last year compare to Q3 this year?" Formula fields make it possible to provide this key metric in real-time, in salesforce.com. If you think your executives would like to see this metric, you can be a hero by reading on and spending about 10-20 minutes to set it up.

  Comments | Bookmark

• Calculating Day of the Week

  Sep 18, 2005

  Have you ever had a need to know the day of the week an opportunity closed? What about excluding Saturdays and Sundays from your reports so that your dashboards only show business days?

  Day of week calculations are easy with formula fields, using the technique described below.

  This example calculates the day of the week as a number (0 = Saturday, 1 = Sunday, 2 = Monday, 3 = Tuesday, etc.) for Opportunity Close Date.
  

  Formula Return Type:
  Number
  

  Formula:
  MOD( {!CloseDate} - DATE(1900, 1, 6), 7)

  Remarks: 

  • This formula works by picking a known Saturday (January 6, 1900 in the example), subtracting it from the provided date, and using MOD to divide the result by 7 and return the remainder. Thanks to Simon W. and Jesse C. for this example.
  • Exclude Saturday & Sunday from reports by adding a report selection criteria of "Day of Week greater than 2".
  • A text version of this formula could spell out the day of the week:

  CASE(MOD( {!CloseDate} - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday","")
  
  

  Comments | Bookmark

• Using checkbox fields in formulas

  Aug 20, 2005

  There are some important checkbox fields in salesforce.com that you may find useful to reference in formulas.  "Do not call" and "Email opt out" on the Contacts tab are but a couple examples.

  Is this possible, you ask?  Yes, but there's a trick to it: checkbox fields are treated as Booleans with a value of TRUE for checked and FALSE for unchecked.
  

   

  You can use a checkbox field directly in an IF function as follows:
  

  IF ({!HasOptedOutOfEmail} , "checkbox is checked", "checkbox is unchecked" )

  Note that if you compare a checkbox field to the literal TRUE or FALSE in an IF statement, you will get the following syntax error.

  IF ({!HasOptedOutOfEmail} = TRUE , "checkbox is checked", "checkbox is unchecked" )
  Error: Incorrect argument type for function =.

  Comments | Bookmark

• Using custom formulas for data validation

  Aug 19, 2005

  Custom formulas were not intended as a solution for data validation, and we have plans to provide robust field and cross-field validation capabilities in the future.

  However, if you are anxious for field validation logic today, it is possible to implement a workaround with a custom formula field, a custom report, and/or workflow.
  

  1. Create a custom formula field with a return type of Text
  2. Use an IF function to evaluate one or more validation conditions.  If an error condition is found, return a textual description of the error.  If there are no errors, return an empty string ("").
  3. Create a custom report that lists data records where the custom formula field is non-blank.
  4. Alternatively, you can create a workflow rule that creates a task or sends an email notification when the custom formula field is non-blank.

  For example, here is an Opportunity formula that checks if a custom field Renewal Expiration Date is after the Close Date. 

  IF ({!Expiration_Date__c} > {!CloseDate}, "Error: renewal expiration date cannot be after close date", "")

  >The major limitation with this approach is that it does not prevent the user from saving invalid data.  Rather, it enables you to detect invalid data after it has been saved, so that it can be corrected.

  Comments | Bookmark

• Using a Custom Formula to Calculate Account Region

  Jul 20, 2005

  

  "Territory management" means something different to every sales organization, and the requirements can get quite complex.

  However, sometimes a few simple rules are sufficient to get the job done. For example, if you just need to categorize your accounts into regions, based on state or province.

  The Region formula below returns a text value of North, South, East, West, or Central based on the Billing State of the account.
  Formula:
  IF(LEN( {!BillingState})=0, "None",
  IF(CONTAINS("AK:AZ:CA:HI:NV:NM:OR:UT:WA", {!BillingState}), "West",
  IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", {!BillingState}), "Central",
  IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", {!BillingState}), "East",
  IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV", {!BillingState}), "South",
  IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", {!BillingState}), "North", "Other"))))))

  Comments | Bookmark

• Lead Scoring for Increased Sales Efficiency

  Jul 15, 2005

  Lead Scoring associates a value to each lead based upon a weighted criteria. For example, a prospect that fills out a web form might have a higher score than purchased list. By giving a numerical value for each lead, reps can better prioritize their lead lists and focus on those opportunities with the greatest potential value.

  By creating a calculated field in salesforce.com you can design your own scoring plan. It might be as simple as scoring based upon lead source (as seen below), or it may include a variety of factors including product of interest, opportunity size, intent to purchase, and time frame. Some of this information might be captured at the time the lead is created, but calculated fields are dynamic so as the rep adds or verifies information the score will adjust accordingly.

  To add a lead scoring field, create a custom calculated field on the lead page layout. There is a sample below but you’ll want to adjust it to fit your business.

  Comments | Bookmark

• Integrate Skype's IP-based phone solution with Salesforce for one click dialing

  Jul 13, 2005

  Some companies are turning to IP-based phone solutions like Skype and Vonage to save money. Skype is a peer-to-peer telecommunications company that has attracted over 40 million users in the past few years. Their plug-in allows PC-based calls through a USB headset or a PC's microphone and speakers. They offer free calls to any other Skype user and very low cost calls to regular phone numbers (2 cents per minute for the US, Western Europe, and Australia.

  The example below shows how you might add pop-up dialing functionality to salesforce.com such that a user can click on a web link from a contact record and automatically launches the Skype client.

  

  Comments | Bookmark

• Creating a Custom Formula to Calculate Age

  Jun 27, 2005

  

  When working with date fields there’s often a need to calculate the number of days since a particular event took place. For example, if you run a support organization you might want to calculate the number of days it takes to resolve a case.

  This is a little bit tricky because if the case is still open you’ll want to calculate the difference between the create date and the current date. If the date is closed, it’s the difference between the create date and the close date.

  The formula field below accounts for both situations and even rounds down to the nearest integer. The end result is a simple standardized number which is easy to work with in reports and dashboards.

  Formula:
  IF( {!IsClosed} , ROUND({!ClosedDate} - {!CreatedDate}, 0) ,
  ROUND((NOW() - {!CreatedDate}),0))
  

  • This formula uses the special Boolean field {!IsClosed} to determine whether the case is open or closed.  Note that when you use a Boolean field in an IF function, you do not need to compare it to TRUE or FALSE.
  • {!CreatedDate} and {!ClosedDate} are both date/time fields, so you can use them together with the NOW() function in subtraction operations.
  • The ROUND function is used to round off the decimal portion of the resulting duration in days.

  Note that this is general approach and could be used for calculating age of any type of data -- Leads, Accounts, custom objects, and so on.

  Comments | Bookmark

• Calculate Accelerated Sales Commissions

  Jun 27, 2005

  One common application for formula fields is to track commission percentages. For example, in the formula to the left, the logic says that if the amount is less than $10,000, the commission should be set to 4%; but if it’s greater, then the accelerator should kick in, resulting in a 5% commission.
  

  Formula:
  IF({!Amount} < 100000, 0.04, 0.05)

  This number can be displayed on the opportunity page layout to motivate your sales reps and it can also be used in a report formula to calculate your commission payouts at the end of the month.
  
  It’s worth noting that when working with percent fields in formulas, 5% should be entered as 0.05. You have to lead with a zero or it will return an error.

  Comments | Bookmark

• Create Groupings Using Custom Formula Fields

  Jun 15, 2005

  The Summer ’05 release has already started to change the way I analyze data in salesforce.com, bringing forward trends that were once hard to distinguish.

  The first formula I’ve started using is a simple average calculation. For example, you might want to divide the number of employees by the number of users to calculate your penetration in an account.
  

  {!Number_Users} / {!Number_Employees}

  Note that that you'll have to substitute your custom fields when building this formula but it shows you the basic design pattern.
  

  This calculation is very similar to the way analysts do a Price/Earnings ratio so that they can get an apples to apples comparison.

  I’ve also found that I’m using custom formula fields helps with categorization. For example, on the account record you might have the number of employees but for reporting purposes you want to create buckets. To do so you can create a nested if statement that looks something like this.

  IF({!Employees} = 0, "",

  IF({!Employees} <50, "Tier 1",

      IF({!Employees} <= 200, "Tier 2",

       IF({!Employees} <=500, "Tier 3",

         IF( {!Employees} <= 1,000, "Tier 4", "Tier 5")))))

  With these two data points you can create a report to measure penetration at different tiers. I’ve left the stage, summary fields, and tier thresholds blank but it should be enough to spark some ideas for your organization.

  
  

  To learn more about how custom formula fields work, I highly recommend Getting Started with Custom Formula Fields presentation and the Custom Formula Field Blog.

  Comments | Bookmark

• What kinds of things can I do with custom formulas?

  Jun 10, 2005

  Custom formulas are very powerful and flexible, so the sky is the limit on what you can do. For some ideas, refer to the 100 Sample Formulas To Get You Started document or review examples in the Summer '05 Release Notes.

  Comments | Bookmark

• What salesforce.com editions include custom formulas?

  Jun 10, 2005

  All editions.

  Comments | Bookmark

• Is it possible for custom formulas to get out-of-date?

  Jun 10, 2005

  No. Custom formulas are recalculated with each request, so they are guaranteed to always be up-to-date, whether viewed on tabs, list views, reports, or through the sforce API.

  Comments | Bookmark

• How many custom formulas are allowed per tab?

  Jun 10, 2005

  The number of custom formulas is limited only by the number of custom fields allowed per tab in each edition. In general*, these limits are:
  
  * Enterprise Edition (500)
  * Professional Edition (100)
  * Team Edition (10)

  The Activity and User objects have significantly lower limits (20).

  Comments | Bookmark

• How big can a custom formula be?

  Jun 10, 2005

  There is a limit of 1000 characters per custom formula, including spaces and carriage returns. However, because you can reference other formulas within formulas, you can sometimes divide up big formulas into multiple fields to get around this limit.

  There are other limits that are related to the size of the database query generated by the system. You may run into these limits if you create custom formulas that reference other custom formulas. If this happens, you will get a syntax validation error stating, “formula too large to execute.”

  Comments | Bookmark

• Can a custom formula reference a field from another tab or a related list?

  Jun 10, 2005

  No. In Summer ‘05, custom formulas can only reference standard and custom fields (including other formulas!) within the same object. They cannot reference fields in their “parent” object, “child” objects (i.e., related lists), or other related objects.  This means, for example, that a custom formula on Opportunity cannot reference fields on Account (parent) or Opportunity Product (child).

  We plan to allow formulas to reference fields in other tabs and related lists in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case for the following feature:

  - Foreign field references in formulas – ability to reference fields in parent or other related objects

  Comments | Bookmark

• Can I do a sum or a count of fields / records in a related list?

  Jun 10, 2005

  No. In Summer ‘05, custom formulas can only reference standard and custom fields within the same object. They cannot reference fields in their “child” objects (i.e., related lists).   

  We plan to support sums & counts in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case f or the following feature:

  - Summary Fields – ability to count or sum fields in children objects (i.e., related lists)

  Comments | Bookmark

• My formula returns #Error! instead of an actual value. What am I doing wrong?

  Jun 10, 2005

  This is the error message that indicates a run-time error occurred during formula calculation. The most common cause of this error is division by zero. This often happens if your formula divides by a non-required field, and the field is blank for some records. See the online help or release notes for more information on troubleshooting formula error conditions.
  

  Comments | Bookmark

• What are other custom formula limitations I should be aware of?

  Jun 10, 2005

  Custom formulas are not indexed for searching because they are dynamically recalculated for each request.
  

  They are not available in Offline Edition or Outlook Edition (planned for the future).
  They are not included in data exports (planned for the future).
  Custom formulas on Lead cannot be mapped for lead conversion.

  The following field types cannot be referenced in a custom formula:
  - Description
  - Long Text Area
  - Multi-Select Picklists

  Comments | Bookmark

• Can I use custom formulas in workflow?

  Jun 10, 2005

  Yes, custom formula fields can be used in workflow rules.
  

  However, the rules for triggering workflow have not changed.  Workflow rules are only evaluated when a record is created or updated. 

  This means that you cannot use a custom formula as a way to trigger a workflow rule based on the passage of time.  For example, if you create a custom formula field on Case called "Days Open", you can't create a workflow rule that automatically triggers when Days Open > 10.  Well, to be precise, you you can create the workflow rule, but it will only be evaluated when the Case is updated by an end user or through the API.

  Time-based workflow is on the Customforce product roadmap.

  Comments | Bookmark

• 100 Ways to Use the New Custom Formula Fields

  May 27, 2005

  The 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 
  
  

  Comments | Bookmark