Blog Categories

Posted by on Jan 7, 2016 in Featured Blogs, Salesforce Formulas Examples, Tutorials | 9 comments

Learn Salesforce Formulas with Examples – Part 7

Learn Salesforce Formulas with Examples – Part 7

Learn Salesforce Formulas with Examples – Part 7 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 36

 

User needs  a formula for validation rule such that whenever the StageName is changed to Closed Won,Status Field cannot be ‘Approved’ or ‘Accepted’.

AND 
(
	ISCHANGED(StageName),
	TEXT(StageName) = "Closed Won",
	CASE(Status,
		'Approved', 1,
		'Accepted', 1, 
		0)
		<> 0
)

Explanation

In this formula,AND is combing three conditions:

  • ISCHANGED Checks if the Stage is changed
  • TEXT(StageName) evaulates the TEXT  part of the picklist Value

  • CASE will return value on basis of status field which is compared with 1 means,checks if the Status is approved or accepted.

Example 37

 

User needs  a formula for to avoid instertion on a look up field product on an object when the product are not active

AND
(
    ISNEW(),
    NOT(Product.Active__c)
)

Explanation

In this Example,ISNEW() Checks if the formula is running during the creation of a new record and returns TRUE if it is. If an existing record is being updated, this function returns FALSE.

Example 38

 

User has an account record type and when the a custom checkbox field ‘IsMandate’ is checked there should be one text field address type should be filled.

AND(
   RecordType.DeveloperName ="Account_Record_Type_Developer_Name",
   IsMandate__c = TRUE,
   ISBLANK(Address_Type_c)
)

Explanation

  • We have used RecordType.DeveloperName which will give the Name of the Record Type,If We would have used RecordType.Name then it will be the label for that Record Type.For eg:If Record Type Name is say -“Vinay Chaturvedi” then its DeveloperName will be something like “Vinay_Chaturvedi”.
  • ISBLANK will check if the field has some value.It is preferred over a NULL Check.

Example 39

 

 User is trying to create a formula that adds a severity code field for a given target completion date.Here is scenarios:
If Target Date is <= 7 Days from Today then it should be priority -“1-Red”.
If Target Date is <= 14 Days from Today then it should be priority -“2-Yellow”.
If Target Date is any other date ,then it should be priority -“3-Green”.
If Target Date is Blank,then it should be ‘null’.

IF(
    NOT(ISBLANK(Target_Date__c)),
    IF(
        Target_Date__c <= (TODAY() + 7), 
        "1-Red",
        IF(
            Target_Date__c <= (TODAY() + 14), 
            "2-Yellow", 
            "3-Green"
        )
    ),
    NULL
)
  

Explanation

Please notice that in this example We have used nested IF which means,We have used IF Else such that we have two inner IF loops in the ELSE part.We could have done it using CASE alternatively

Example 40

 

User wants a validation rule for any opportunity record  such that when the Override field  is not checked, User cannot have a blank Agreed Rate if the Opportunity stage is Closed Won and the Opportunity Name Contains ‘Vinay’.

IF(Override__c,FALSE,
AND(
	IsPickval(StageName, "Closed Won"),
	Isblank ( Agreed_Rate__c ),
    Contains( Name , "Vinay")
   )
)
  

Explanation:

  • ISPICKVAL will compare the picklist value of that field and Contains will check if the name contains Vinay
  • AND part will execute only when Override__c will be FALSE .

Example 41

 

User needs to create a formula using the created by first name +”+ created by last name to identify one grouping, and Lead Source to identify another, then the 3rd grouping would be everything else.Here is the scenario:
If the Lead is Created by Vinay Chaturvedi then the value should be “Vinay Sourced”.
else
if the Lead Source is Inbound email,then “Marketing Email Sourced”
if the Lead Source is Live Chat,then “Marketing Live Chat Sourced”
if the Lead Source is Webinar,then “Marketing Webinar Sourced”
else,the Lead Source is “Other”.

IF(CreatedBy.FirstName + " " + CreatedBy.LastName = “Vinay Chaturvedi”, “Vinay Sourced”,
IF(ISPICKVAL(LeadSource, “Inbound Email”),“Marketing Email Sourced”,
IF(ISPICKVAL(LeadSource, “Live Chat”), “MarketingLive Chat  Sourced”,
IF(ISPICKVAL(LeadSource, “Webinar”), “Marketing Webinar Sourced”, “Other”))))
  

Explanation:

  • CreatedBy.FirstName + ” ” + CreatedBy.LastNameis merged to compare the full name
  • Three level Nested IF is used where the seond will exceute when the first is false and third will execute when second is false

Example 42

 

User wants a formula for a workflow to evaluate true and trigger an email if the picklist field “Reason” is left blank and also if the Lookup(User) field created has a name populated. 

AND
(
	ISBLANK(TEXT(Reason__c)),
	NOT(ISBLANK(LookupUser__c))
)
  

Explanation:

Simple one,comparing picklist value if its blank using ISBLANK(TEXT(PickList_Field)).

Example 43

 

User needs a useful validation for the stage reaches to advance and if the user wants to close the opportunity with lost.
Users should not select the Opportunity Stage to “Closed Lost “or “Closed Cancelled” ,if the Prior Stage is “Negotiation/Review” or
“Selected by Customer”.
Only Administrator should close the opportunity with lost.

AND
(
	$Profile.Name <> "System Administrator",
	OR
	(
		TEXT( StageName ) = "Closed Lost",
		TEXT( StageName ) = "Closed Cancelled",
	),
	OR
	(
		TEXT(PRIORVALUE(StageName)) = "Negotiation/Review",
		TEXT(PRIORVALUE(StageName)) = "Selected by Customer"
	)
)
  

Explanation:

  • $Profile is the global variable used to check the Profile Name
  • PRIORVALUE will give the previous value of

Example 44

 

User has created a custom bject in that user has a picklist name “state”,there are more than 20 states if user selects one state respective code will be displayed in another field
For instance,If User select state as STATE 1,then value in the field should be 01

CASE
(
    TEXT(State__c),
    "STATE 1", "01",
    "STATE 2", "02",
    "STATE 3", "03",
    "STATE 4", "04",
    "0"
)
  

Explanation:

CASE will check for State__c values  and will return 0 if nothing is matched.

Example 45

 

User wants to show no of days left for escalation such that if the Lead Status is not open and is converted then the no of days should be the difference between the Date when Lead was
Responded (Lead Response Date Field) and Lead Escalation Start Date otherwise it should be difference between today and the Escaltion Start Date

IF
( 
	OR(NOT(ISPICKVAL(Status , "Open")), IsConverted = True), 
	Lead_Response__c - Lead_Escalation_Start_Date__c, 
	NOW() - Lead_Escalation_Start_Date__c 
)
  

Explanation:

  • If any one of the following is TRUE
    1. Status picklist does not equal Open OR
    2. Lead had been converted

Then the formula field should display
Lead_Response__c – Lead_Escalation_Start__c

  • If none of 1. or 2. are TRUE then the formula should instead display
    NOW() – Lead_Escalation_Start__c

9 Comments

  1. Vgood

  2. Thanks, Vinay. This compilation is extremely good. One clarification in example 1:

    AND
    (
    ISCHANGED(StageName),
    TEXT(StageName) = “Closed Won”,
    CASE(Status,
    ‘Approved’, 1,
    ‘Accepted’, 1,
    0)
    1
    )

    Won’t it should be = 1 in CASE comparison since we need to write error scenario.

    • You are right Deep,Thanks for pointing it out!! Really Appreciate it!!
      I have fixed it now 🙂

  3. Really a worth Post

  4. I want to have a validation rule, such that whenever a contact enters “11111111” or “3333333” such similar combinations in phone field, it shows validation error.

  5. This is one of the best blogs/sites. Keep publishing more!Thanks Vinay

Post a Reply

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