/
Formulas

Formulas

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

Articles about Formulas

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: