Formulas

Owned by Jodie Miners
Last updated: Jan 18, 2018

See Also Rules for Fields

Getting Started

Trailhead has a good overview of Formulas https://developer.salesforce.com/trailhead/force_com_introduction/point_click_business_logic/formula_fields and rollup summary fields https://developer.salesforce.com/trailhead/force_com_introduction/point_click_business_logic/roll_up_summary_fields and Validation Rules https://developer.salesforce.com/trailhead/force_com_introduction/point_click_business_logic/validation_rules

This is a nice overview of formulas for Salesforce


Formula Pages

Other pages in this site about formulas

  • Page:
    Case Thread ID — Used in Cases to ensure that an email sent to your support email is attached to an existing Case.

Articles about Formulas

Generating page properties report...

Example Formulas

The Salesforce help has some nice examples https://help.salesforce.com/HTViewHelpDoc?id=useful_advanced_formulas.htm and date formulas https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm

Date Expired on Active Contract Example

Requirement for a field for an expired contract. Users requested a report they can run to show if the contracts are expired (note, this is using a custom contracts object so this can be used on any type of record. 

Checklist Formula field. 

IF(AND((ContractExpiry__c) < TODAY(),ISPICKVAL(ContractStatus__c,"Active")),True,False)
So, there are a few parts to this formula:
  • If Statement - straightforward, just as you would have used in Excel.
  • And Statement - exactly like Excel
  • A field value > Today()
  • ISPICKVAL formula - you need to use this wherever dealing with the picklists. 
  • And returning True or False because the formula is a 
If you get stuck, try it in excel, the formula structure is quite similar, especially around IF AND OR statements.

Date Expired check field 2 weeks before Active Contract Example

Requirement for a field to check an expired contract or create a warning. Workflows can be created to task a record owner to check a contract

As above but include  "-14" for 14 days before expiry

IF(AND((ContractExpiry__c) > TODAY()-14,ISPICKVAL(Contractor_Status__c,"Active")),True,False)

State and Country for use with State and Country Picklists

IF(NOT(ISBLANK(BLANKVALUE(TEXT(BillingStateCode) ,TEXT(ShippingStateCode)))),
BLANKVALUE( TEXT(ShippingStateCode) ,TEXT(BillingStateCode)) & IF(BLANKVALUE(TEXT(ShippingCountryCode), TEXT(BillingCountryCode)) = "AU","", ", " & BLANKVALUE(TEXT(ShippingCountryCode), TEXT(BillingCountryCode))),"")

/*IF(NOT(ISBLANK(BLANKVALUE(TEXT(BillingStateCode) ,TEXT(ShippingStateCode)))),
BLANKVALUE( TEXT(BillingStateCode) ,TEXT(ShippingStateCode)) & IF(BLANKVALUE(TEXT(BillingCountryCode), TEXT(ShippingCountryCode)) = "AU","", ", " & BLANKVALUE(TEXT(BillingCountryCode), TEXT(ShippingCountryCode))),"")*/

Warning fields

Requirement for a field to create a warning that a contract if about to expire

IF (ContractExpiry__c = True, "Contract about to Expire","")

If adding more than one warning add   + '; ' +   between IF statements


Add a Year to a Date Example

As described here

https://success.salesforce.com/answers?id=90630000000gmVpAAI

DATE(YEAR(Join_Date__c) + 1,MONTH(Join_Date__c),DAY(Join_Date__c))
Another solution
AND(DATEVALUE(CreatedDate)<DATE(YEAR(DATEVALUE(CreatedDate)),MONTH(TODAY()),1),DATEVALUE(CreatedDate)>=DATE(YEAR(DATEVALUE(CreatedDate)),1,1))

Things you can't do

Rules

Rules for Formulas: