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
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 )
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.
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) )
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.
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) )
- 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.
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 )
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
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") ) )
- 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 .
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”.
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”))))
- 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
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)) )
Simple one,comparing picklist value if its blank using ISBLANK(TEXT(PickList_Field)).
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" ) )
- $Profile is the global variable used to check the Profile Name
- PRIORVALUE will give the previous value of
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" )
CASE will check for State__c values and will return 0 if nothing is matched.
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 )
- 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