Blog Categories

Posted by on Apr 21, 2016 in Blog Series, Featured Blogs, Salesforce Formulas Examples, Tutorials | 8 comments

Learn Salesforce Formulas with Examples – Part 8

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

Example 46

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
	
)

Explanation

  • 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.

Example 47

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")
   )
)

Explanation

  • 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.

Example 48

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

Explanation

If Salutation is empty then I will show only FirstName + MiddleName + LastName.If the FirstName is empty it will should Salutation + MiddleName + LastName etc.

Example 49

 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
)
  

Explanation

  • 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.

Example 50

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
)
  

Explanation:

  • Above is simple example of CASE function.

Example 51

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
  

Explanation:

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.

Example 52

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, "*" ))
)
  

Explanation:

  • 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

Example 53

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
Stage -1
Stage -2
Stage -3
Stage -4
Stage -5
Stage -6

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
	) 
)
  

Explanation:

  • 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).

 

Example 54

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))
  

Explanation:

  • Above example illustrates how to combine different text fields.

 

Example 55

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")
			)
	) 
)
  

Explanation:

  • IMAGE Inserts an image with alternate text and height/width specifications.
  • DATEVALUE Returns a date value for a date/time or text expression.

8 Comments

  1. This is amazing! these formulas have really helped me at times. Thank you for your input Vinay !! please keep up the good work.
    Sometimes i think these are better than the ones SF help section since the use cases are so much more relevant.

    Thanks AWESOME!

    • These words really mean to me a lot !! Thank you so much for your feedback!

      • You’ve got it in one. Co’nldut have put it better.

  2. Awesome work Vinay

    Some Ideas for Explanation:

    1. May you can Highlight in the Code the same colour in the text for exaplanation. That an Beginer still know where you are with your explanation in the code.

    2. Use for any Formula an Example Value in your Explanation

    Just suggestions from myself

    May you’ll think that one of it is a good one

    regards john s.

    • Thanks John for your feedback!
      Will keep the same for my next blog 🙂

  3. Dear Vinay
    Iam recently certified in Developer and Administrator,Iam wondering while going through your farmulas and examples.These KT is great lessons and useful to us.Amazing your thoughts.
    Iam really thankful to you and appreciate on your efforts and sharing your knowledge.
    Gopala krishna G
    Certified SAP and Salesforce Admin and developer
    Cell : +1 484-483-2664.

  4. Very good Post Vinay…:)

Post a Reply

Your email address will not be published. Required fields are marked *