An Introduction to Formulas

Abstract

Objects persisted in the database tend to have field types that store data, for example numbers or text. They can also have a formula field type, which is calculated at run-time much like formulas in a spreadsheet. Besides their use as a field type, formulas are used to determine when certain actions should occur on the platform, for example when a workflow rule should fire, and in validation rules that determine when data is bona fide.

This article introduces formulas on Force.com. It provides an overview of the syntax for formulas and the operations that are available in formulas. Finally, it provides examples of where formulas can be used on the platform, ranging from default values to validation rules.

What are Formulas?

A formula is similar to an equation that is executed at run time. Depending on the context of the formula, it can make use of various data and operations to perform the calculation. A useful way to think about formulas is that they are similar to formulas on a spreadsheet. These formulas can make use of data and operations to calculate a new value of some type.

Here's an example of a formula that can be used in a custom formula field on a database object. It calculates the number of days the record has remained open:

 IF( ISNULL(Close_Date__c), 
     TODAY() - Open_Date__c, 
     Close_Date__c - Open_Date__c)

This example demonstrates the use of a number of features of the formula field including conditional statements (IF, ISBLANK), functions (TODAY()), operators (-) and the use of custom fields (Close_Date__c, Open_Date__c). We will discuss each of these features further below.

Formulas are widely used in the Force.com platform and should be considered first before jumping into Apex Code. The same formula syntax can be used to create default values, data validation rules, custom field formulas, conditional logic on Visualforce pages, and rules for determining when certain actions should occur, such as workflow actions.

Formula Syntax

Let's look a little at the syntax and features available. A formula can contain references to the values of standard or custom fields, operators, functions, literal values, or even other formulas. They're generally composed of the following components:

  • Literal Values - A text string or number you enter that is not calculated or changed. For example, 2.0 or 'Hello World'
  • Field References - Reference the value of another field using a merge field. The syntax for a merge field is field_name for a standard field or field_name__c for a custom field. For example, My_Field__c.
  • Global Variables – Reference the value of a system or environment variable, such as information about the logged in user or the organization settings. For example, $User.Department.
  • Functions - A collection of operations and conditional operations that when used within a formula field allow you to perform such things as math calculations, if/and/or and string manipulation. Functions are grouped into four primary categories: Date & Time, Logical, Math and Text. In addition, there are a number of advanced functions including regular expressions, and vlookup ability. These are currently only available in some formula contexts, not all.
  • Operator - Operators fall into two categories; math operators that specify the type of calculation to perform or the order in which to do it, and logical operators that evaluate if a value is greater than, less than, equivalent or true/false. Example math operators include +,-,* while example logical operators include >, <, &&, == and so on.

Check out the documentation in the References for a complete description of all the available functions.

Formulas in the Wild

The following sections describe the various contexts in which formulas can be used in Force.com.

Creating a Formula Field

Formulas fields can be created in a number of ways, the most common is by creating a new custom field on an object and selecting Formula in the field type list, then choosing a return type. Return types provide a convenient way to automatically format results such as currency or number (including decimal precision) or perform typical operations including percentage calculations and date manipulation.

Crl formulafieldtype.jpg

These fields are read only, and are also accessible via the Force.com SOAP API. The values are calculated every time they are needed; they are not stored.

Dynamic Default Values

Formula fields also let you set dynamic default values for new records. Say that your company has established a sales process, which requires that all new opportunities must be followed up within 5 days of being created in the system. The first thing to do is to create a new custom Date field on the Opportunity object called FollowUpDate. On the same step where you define the field name and label, you will have the option to define a Default Value. In this case a default value of TODAY() + 5 will give the desired effect - a value that is dynamic (it varies depending on the day that the record is created!).

To add the dynamic default value, simply click the Show Formula Editor button above the default value textbox. This displays a Formula Editor, which helps you compose and verify formulas. Note that in the Select Field Type picklist you only see the Global Variables – these start with $ (e.g., $Profile, $User). When the new record is created, you only have access to these global variables – you do not have access to any other records in the system, so you cannot select any merge fields.

Formulas can be typed in directly, or you can use the editor to help composition. For example, you can navigate to the Date Functions and select the 'Date & Time' function category from the picklist, exposing the function called TODAY. Double click it to add the function into your formula field then click the 'Insert Operator' picklist and select + Add. Now type 5 into the formula field, click Check Syntax for instant syntax gratification, and save. If you receive a syntax error check your formula against the one below:

Crl formula followupdate.jpg

That's it, you just created a very useful dynamic default value which could be used to kick off any number of workflow rules and alerts. On the next step of the custom field wizard, you can decide if any users will be able to edit the FollowUpDate field, or if this business rule is rigid and this field should be read-only. Complete the custom field wizard and save this new field.

Validation Rules

Formulas are also used in validation rules to ensure business requirements are met and data quality is maintained. A simple example may be that minimum pay is greater than maximum pay or end date is less than start date. To create a validation rule select the object you want to associate the rule with and, within the Validation Rule list, click New.

The Formula Editor will again be present, making it easy to create your rule. After a quick description of the rule we get to the important part - the error condition formula. With our newly obtained knowledge of Merge Fields and Functions we can create a rule that asserts our particular requirement. For a simple example, let's create a new Rule on a custom object, Position, called Min_Pay_Rule.

Assuming your Position object already contains fields called Min_Pay__c and Max_Pay__c, you can insert them into the formula field by selecting them from the Insert Field button, and choosing from the Insert Operator picklist to place the > (greater than) operator between them. At this stage the formula should look like the following: Min_Pay__c > Max_Pay_c

At first glance the logic may look reversed – don't you want to make sure that minimum pay is less than maximum pay, not greater? Yes you do but, for the validation rule to be executed you want to assert that the exception is true. If minimum pay is less than maximum pay you do not need to do anything and the system can continue processing. But if minimum pay is greater you want to know about it and inform the user of the error.

Crl formula minpayvalidation.jpg

Now if the user enters an invalid pay range when creating an instance of this object, they will see the error message displayed automatically. The same validation rule is also applied if records are inserted via the Force.com SOAP API or import wizard. In other words, validation rules are enforced no matter which mechanisms are used to modify the records.

Cross-Object Formulas

One powerful feature in the formula language is the ability to reference the value of a field in a parent object. In the examples we have walked through so far, the values we needed were on the object where we created the formula. But what if we needed a value from a related object, such as the Rating value of the Account that is related to an Opportunity, or the Email Address of the Hiring Manager for a Position? We can do this easily in formula fields and in validation rules and other business rule formulas.

The syntax for referencing a field on a related object is to prefix the field name with the relationship names of the objects to which you are spanning. Separate the relationship names of each object and the field with periods. For example, enter Contact.Account.Name to reference the Account Name for a contact associated with a case in a formula field on the Case object. Be sure to use the relationship names of the objects, not the labels. The relationship name is often the same as the object name, but it is technically the field name of the relationship field (e.g., to reference the Parent Account name from the Account object, the syntax is Parent.Name, not Account.Name). Also, when referencing a custom object, add two underscores and the letter r to its name.

For example, Position__r.title__c references the Job Title field (title__c) on a Position custom object.

Note that when we say "related object"" we are only talking about parent relationships (Master-Detail or Lookup), not child relationships. There is no syntax to reference a collection of child object records in the formula language.

Let's take the Hiring Manager example. What if we want to display the Email Address of the Hiring Manager on the Position record, and have it available in lists and reports, too? We can create a custom formula field named Hiring Manager Email with return type of Text on the Position object to "calculate" this value. The formula is very simple – we just need the merge field for the Email Address on the User object, via the Hiring Manager relationship. The easiest way to do this is to use the Insert Field button to open the "field picker". Then, click on Hiring Manager >, then click on E-mail, then click on Insert. The > after an item in the list indicates that it is a relationship, and clicking on the > opens a new list of fields and relationships for that object.

Formula insert field.jpg

This will generate the following formula:

  Hiring_Manager__r.Email

That's it.

Other Business Rules

You can use the formula language to enforce other business rules, including Workflow Rules, Approval Processes, Approval Steps, Assignment Rules, Escalation Rules, and Auto-Response Rules. In each of these rules, you can choose to define the rule criteria using the filter user interface or using a formula. Many simple rules can be defined with a filter, but for more complex rules, you can dive into a formula.

To find out more about setting up the other types of business rules, see the online help.

Visualforce

Formulas are also used for the expression language in Visualforce pages. Visualforce supports slightly different global variables, operators, and functions. For example, consider the following Visualforce page:

<apex:page renderAs="{!if($CurrentPage.parameters.p == null, null, 'pdf')}"
           controller="MyController"> 
  Hello World!
</apex:page>

This page will set the value of the renderAs attribute to 'pdf' if an incoming parameter p is present. It uses a variable made available by Visualforce, $CurrentPage, which lets you access the parameters passed to a page. As a result, the page will render as PDF instead of HTML. Many standard Visualforce components support formulas in this way.

One useful global variable that is supported in both formula fields and Visualforce pages is $Label. This global variable allows you to reference your own translatable text in a formula. For example:

 <apex:page>
   <apex:pageMessage severity="info" 
   strength="1" 
   summary="{!$Label.firstrun_helptext}" 
   />
 </apex:page>

Another useful global variable that is only supported in Visualforce is $Resource. This global variable allows you to reference your static resources. For example:

 <apex:image url="{!URLFOR($Resource.TestZip, 'images/Bluehills.jpg')}" width="50" height="50" />

In this example, the <apex:image> component references a .jpg file contained within a .zip file that has been uploaded as a static resource. For more on static resources, see Delivering Static Resources with Visualforce.

Rules and Limits

Throughout this article we have seen the power of formulas, but there are a number of rules and limits that can affect their use. Understanding what these rules and limits are, as well as best practices to work within them, will help you in maximizing the benefits of formulas.

General Formula Rules

  • Formulas can reference standard, custom or other formula fields as required
  • Formula fields cannot reference themselves
  • Fields that are used in formulas cannot be deleted without an Administrator removing the formula first

There are also a number of special cases in formulas, mostly dealing with different data types and functions. These are described in the online documentation.

Formula Limits

  • Formula fields can contain up to 3900 characters, including spaces and line breaks.
  • Formula fields are calculated using generated SQL on the backend. This SQL must not exceed 5000 characters. This includes other referenced formula fields. A best practice to avoid receiving a formula too big to execute error is to avoid referencing large formula fields multiple times from another formula (each reference is expanded at run-time) and use CASE statements instead of nested IF statements, where possible.
  • For all of the formulas related to an object (formula fields, validation rules workflow rules, assignment rules, etc.), there is a limit of 10 unique relationships for cross-object merge fields. You can reference any number of merge fields across one relationship (i.e., from Contact you can reference as many Account fields as you want), but if you try to reference a merge field across a 6th relationship, then you will be blocked from saving that formula.

Examples

Now that you have a good understanding on how to build formulas and where formula fields can be used, let's jump into a few more complex examples that come up regularly as requirements. Examples are often the best way to learn how others have solved similar requirements and provide great resources to get you up and running quickly. Please be sure to check out the Formula Quick Reference in the References for details on the functions referenced in the examples below.

Useful Validation Rules

The samples below are a small subset of those included in the Useful Validation Rules document (also in References). This valuable document provides detailed explanations of the formulas, going far beyond this introductory article.

Validates the Billing Zip is in the Billing State

 VLOOKUP(
   $ObjectType.Zip_Code__c.Fields.City__c ,
   Formula:
   $ObjectType.Zip_Code__c.Fields.Name ,
   LEFT(BillingPostalCode,5)
 ) <> BillingCity

The VLOOKUP function is only available in Validation Rules.

Validate Account number is numeric

 AND(
  LEN ( AccountNumber) > 0,
  NOT (ISNUMBER( AccountNumber )
 )	

The ISNUMBER function is not available in Custom Formula Fields or Visualforce.

Date must be in the current month

 OR (
  YEAR( My_Date__c ) <> YEAR ( TODAY() ),
  MONTH( My_Date__c ) <> MONTH ( TODAY() )
 )

Regular Expressions

One of the most powerful aspects of formula fields is the REGEX function. A full discussion of regular expression syntax is beyond the scope of this article but if you are interested in more details check out this great tutorial.

The REGEX function is not available in Custom Formula Fields or Visualforce.

Ensures that a custom field, Driver's License, is from California

 AND(
  MailingState = "CA",
  NOT(REGEX(Drivers_License__c, "([A-Z]\\d{7})?"))
 )

Check for a valid credit card

  NOT( REGEX( Credit_Card_Number__c ,"(((\\d{4}-){3}\\d{4})|\\d{16})?"))

Validate Social Security Number format

 NOT(
    OR(
	LEN (Social_Security_Number__c) = 0,
	REGEX( Social_Security_Number__c , "[0-9]{3}-[0-9]{2}-[0-9]{4}")
    )
 )

Summary

Throughout this article we have introduced formulas as a powerful tool when you need to perform typical spreadsheet-like calculations and functions. By learning the basic constructs of a formula; literal values, field references, functions and operators, and combining them together with the ability to use them for calculations, enforcing data quality, or triggering business rules, you can quickly build out dynamic business processes.

The best way to become a formula guru is to start with the examples in this document and the great references listed below. Learning the provided functions and how you can combine these together will be invaluable. Examples become particularly useful in the more complex functions supported by Force.com.

Now there is nothing else to do but jump in and try it out yourself.

References

  • This huge collection of useful validation rules (PDF) has many great validation rules. Bookmark this document and reference regularly.
  • The Formula Quick Reference Guide (PDF) provides a cheat sheet of the formula syntax.
  • The Operators and Functions list from the online help is invaluable. Log in to your environment, select Help, and search for Operators and Functions.
  • The Tips for Building Formulas page, also available on the online help, is very usBold texteful. Log in to your environment, select Help, and search for Tips for Building Formulas.
  • Useful Validation Rules (PDF) provides a number of example validation rules.
  • Steps to Successful Validation Rules is a slightly old, but useful document on data validation.
  • Developer Force provides access to free developer edition accounts, which you can use to start programming immediately. It also provides links to documentation, forums, and more.
  • An Introduction to the Force.com Database provides a much more comprehensive introduction to the Force.com Database.
  • An Introduction to Visualforce provides a lot more detail on Visualforce, which also uses the formula language.
  • Building Smarter Apps Webinar is a recording of a September 2008 webinar focused on cool things to do with formulas, includes Workflow and Approvals.

==About the Author== Mary Scotton is the product manager for formulas at salesforce.com. She is passionate about converting both technical developers and non-technical administrators into formula evangelists. She keeps an eye on the Formula Discussion Board (on the CRM Community site) and really appreciates how the community responds to posts and takes such good care of each other.