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’.
[codeblocks name=’Formula_Example_ 36′]
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
[codeblocks name=’Formula_Example_ 37′]
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.
[codeblocks name=’Formula_Example_ 38′]
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’.
[codeblocks name=’Formula_Example_ 39′]
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’.
[codeblocks name=’Formula_Example_ 40′]
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”.
[codeblocks name=’Formula_Example_ 41′]
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.
[codeblocks name=’Formula_Example_ 42′]
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.
[codeblocks name=’Formula_Example_ 43′]
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
[codeblocks name=’Formula_Example_ 44′]
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
[codeblocks name=’Formula_Example_ 45′]
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