Learn Salesforce Formulas with Examples – Part 8
Learn Salesforce Formulas with Examples – Part 8 is a part of the Blog Series inspired by a lot of problems related to formulas posted in Answers Community .Main motive for this blog is not to teach you how to begin with formula but how to keep a grip and make a better understanding on it..So stay tuned and I will be providing a lot of examples with explanations in this series which would help you understand more about formulas
User knows how to make one field required if another is filled in, but he would like to make all of the fields in a section required if ANY field in that section is entered. Let’s assume there are Five Fields Field_1,Field_2 and so on
AND ( IF(ISBLANK(Field_1__c), 0, 1) + IF(ISBLANK(Field_2__c), 0, 1) + IF(ISBLANK(Field_3__c), 0, 1) + IF(ISBLANK(Field_4__c), 0, 1) + IF(ISBLANK(Field_5__c), 0, 1) > 0, IF(ISBLANK(Field_1__c), 0, 1) + IF(ISBLANK(Field_2__c), 0, 1) + IF(ISBLANK(Field_3__c), 0, 1) + IF(ISBLANK(Field_4__c), 0, 1) + IF(ISBLANK(Field_5__c), 0, 1) < 5 )
- In this formula,IF Else returns value 0 and 1.
- AND is used here to check both condition simultaneaously that if any of the field is filled that means any of the field returns 1 i.e. the sum of all five fields’ IF condition evaluates to more than 0,then all the IF’s should return that means the total all IF’s should not be less than 5.
User has a date field Expiry Date field on Account Object,User wants to create a formula field on Opportunity such that if the Expiry Date field has a value:
- between 1-60 days in the past from today, this formula field should be Recently Expired.
- over 61 days old from today, it should be Inactive
- 0-90 days new from today, it should be Renewal Pending
- over 90 days new from today, it should be Active
IF( ISBLANK(Account.Expiry_Date__c), NULL, IF( Account.Expiry_Date__c < TODAY(), IF(TODAY() - Account.Expiry_Date__c <= 60, "Recently Expired", "Inactive"), IF(Account.Expiry_Date__c - TODAY() <= 90, "Renewal Pending", "Active") ) )
- Above Example is a nested IF .
- Determines if expressions are true or false. Returns a given value if true and another value if false.
IF(logical_test, value_if_true, value_if_false) and replace logical_test with the expression you want evaluated; replace value_if_true with the value you want returned if the expression is true; replace value_if_false with the value you want returned if the expression is false.
User is trying to set up a “Full Name” custom field that combines Salutation, First Name, Middle Name, and Last Name, and leaves off either the Salutation, First Name, or Middle Name if any of these fields are blank.
TEXT(Salutation) + " "+ FirstName + " "+MiddleName +" "+ LastName
If Salutation is empty then I will show only FirstName + MiddleName + LastName.If the FirstName is empty it will should Salutation + MiddleName + LastName etc.
User wants to add a text formula field that returns a dynamic URL based on the country selected.
If the country selected is Australia with country code as AU then
result should be – ‘http://mywebsite.com.au/’ + Custom_Profile_Page_Field
If the country selected is New Zealand with country code as NZ then-
‘http://mywebsite.com.nz/’ + Custom_Profile_Page_Field.
User wants to make sure that this text field is not populated if Custom_Profile_Page_Field is blank and record type is anything other than ‘ABC’
IF ( AND ( NOT(ISBLANK(Custom_Profile_Page_Field)), RecordType.Name='ABC', ), CASE (Country__c, 'AU','http://mywebsite.com.au/' + Custom_Profile_Page_Field, 'NZ','http://mywebsite.co.nz/' + Custom_Profile_Page_Field, NULL ), NULL )
- Here CASE is used to compare country__c field with multiple values.
- AND is used to combine multiple conditions to check if all the conditions are true.
- RecordType.Name will check the name of the record type of the record.
User need to construct a formula to assign a Tier (A, B or C) that gets the value from a picklist, and then validates with the options from two other pick lists .
There are three picklists with below values:
1. Type: Contractor, OEM , End User
2. Voltage: >600, <600
3. Engineers: <3, >3
User wants to achieve the formula such that :
if the type equals OEM and the voltage is >600, and engineers >3, assign the tier an A.
if the type equals OEM and the voltage is >600, and engineers <3, assign the tier an B.
if the type equals OEM and the voltage is <600, assign the tier an C.
CASE ( CASE(Type, "OEM","1", "EndUser","0", NULL) + CASE(Voltage, ">600","1", "<600","0", NULL) + CASE(Engineers, ">3","1", "<3","0", NULL ), "111", "A", "110", "B", "101", "C", NULL )
- Above is simple example of CASE function.
User need a formula to setup a validation rule such that this validation rule should allow only one check box either Box A or Box B should be filled .However User also needs to save even though there were no checkboxes selected.
(IF(Box_A__c= TRUE , 1 , 0) + IF(Box_B__c= TRUE, 1 , 0 )) >1
Above formula means that if both the condition are set to true,that means the total will be 2 i.e >1 then it will throw error and this wont throw when no value will be slected as the sum will be 0.
User wants a formula field for a validation rule that states users must begin the text field with an asterisk.
AND ( NOT(ISBLANK(Text_Field__c)), NOT(BEGINS(Text_Field__c, "*" )) )
- BEGINS checks if the word starts with letter,so NOT(BEGINS) will check if the word doesn’t start with “*”.
- ISBLANK checks if the field is BLANK
User wants to have a validation Rule on Opportunity such that when the user is in one stage (stage – 1) and goes to the next stage (stage – 2) they should not be able to put back to the pervious stage (Stage-1).
Let’s below are 6 stages in the picklist field
AND( CASE ( StageName , "Stage -1", 1, "Stage -2", 2, "Stage -3", 3, "Stage -4", 4, "Stage -5", 5, "Stage -6", 6, 0 ) < CASE ( PRIORVALUE(StageName), "Stage -1", 1, "Stage -2", 2, "Stage -3", 3, "Stage -4", 4, "Stage -5", 5, "Stage -6", 6, 0 ) )
- Above example is an awesome use case for CASE with two CASE combination which checks when user is in one stage (stage – 1) and goes to the next stage (stage – 2) they should not be able to put back to the pervious stage (Stage-1).
User needs a formula field that returns text to display the combination of first name , last name and Date of Birth all together such that a period “.” should be inserted between the three concatenated Fields
Assuming Last_Name__c,First_Name__c,DOB__c are the custom API names of the fields.
Last_Name__c & "." & First_Name__c & "." & (text(DOB__c))
- Above example illustrates how to combine different text fields.
User wants to set up a traffic light system in a custom object.He would like to base it on a picklist field “Status” which has the values, Open and Closed.
User wants to be able to track the length of time the record is open. User has an internal process whereby he requires everything to be completed within this stage of the process within 8 days.
Based on this He would like to have a traffic light in place that could show this visually.
Green = 3 – 8 days
Amber = 1 – 3 days
Red = Anything under 1 day.
User has already uploaded the images into One of the Document Folders where all Users have access to it ,
He also had captured the 15 digit ID of the Image File from the browser.
IF ( TEXT(Status__c) = "Closed", IMAGE("/servlet/servlet.FileDownload?file=15DigitIdofTheFile", "Closed"), IF ( (DATEVALUE(CreatedDate) +8 - TODAY()) <=1, IMAGE("/img/samples/flag_red.gif", "Red"), IF( (DATEVALUE(CreatedDate) +8 - TODAY()) <=3, IMAGE("/img/samples/flag_yellow.gif", "Yellow"), IMAGE("/img/samples/flag_green.gif", "Green") ) ) )
- IMAGE Inserts an image with alternate text and height/width specifications.
- DATEVALUE Returns a date value for a date/time or text expression.