Inspired by a lot of problems related to formulas posted in Answers Community ,I decided to put blog series “Learn Salesforce Formulas with Examples“ and here I am with second post in the Series.
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 6
User would like to to show an Auto-Number field (Name) in two display formats based on the selection of a custom checkbox field say ABC__c,
If ABC__c is checked the display format should be ABC{0000} else it should be CBA{0000}
Let’s assume the Auto-Number Field (Name) is set the display format to be only numbers {0000},If not then User has to make sure this is done.
Now creating a formula returning a Text Type will do the job :
IF( ABC__c, “ABC”, “CBA” ) & Name
Explanation:
Here we have used return type TEXT and we have used “& ” which will concatenate result of “IF” which would result either ‘ABC’ or ‘CBA’
Example 7
User wants to create a formula for his workflow on Case object which needs to be triggered on basis of below conditions:
when the Status changes from ‘Closed’ to ‘Open’ with Record Type ‘ABC’ and the Source (custom picklist value- source__c) is ‘Web’.
AND
(
ISCHANGED(Status),
TEXT(PRIORVALUE(Status)) = “Closed”,
TEXT(Status) = “Open”,
RecordType.DeveloperName = “ABC”,
TEXT(source__c) = “Web”
)
Explanation:
Here ISCHANGED compares the value of Status to the previous value and returns TRUE if the values are different. If the values are the same, this function returns FALSE.
PRIORVALUE will return the previous value of Status and TEXT(PRIORVALUE) is used as it compared with a text value.
Also notice that we have used RecordType.DeveloperName and not the RecordTypeId so in order to we do not hard-code the Id here.
Example 8
User wants to create a formula for a validation rule to allow users to update case status to ‘Completed’ only if the logged in user if case is assigned to the user or the User Role is ‘Case Admin’
AND
(
$UserRole.Name <> “Case Admin”,
$User.Id <> Owner:User.Id,
ISPICKVAL( Status ,”Completed”)
)
Explanation:
Here we have checked the logged in User Role $UserRole.Name global variable and logged in User Id with the Owner of the Case.Have combined conditions with AND ,ISPICKVAL is used to check if the status is completed.
Example 9
User wants to create a validation rule on the object such that it allows update of “Main” field only if it’s previous value is Blank and the other two fields “First” & “Second” both are already filled.
AND(
NOT(ISBLANK( Main__c )),
ISBLANK(PRIORVALUE( Main__c )),
OR(
ISBLANK( First__c ),
ISBLANK( Second__c )
)
)
Explanation:
In the formula ISBLANK(PRIORVALUE( Main__c )) determines if the previous value was Blank, ISBLANK determines if an expression has a value and returns TRUE if it does not. If it contains a value, this function returns FALSE.
Example 10
User wants a validation rule on opportunity stage such that opportunities do not move back to the previous sales stage.
For eg: If ‘Qualification’ comes after ‘Prospecting’ then if the stage is updated from Prospecting ,it should never get updated to ‘Qualification’
CASE( StageName ,
“Prospecting”,1,”Qualification”,2,”Needs Analysis”,3,
“Value Proposition”,4,”Id. Decision Makers”,5,”Perception Analysis”,6,”Proposal/Price Quote”,7,
“Negotiation/Review”,8,”Closed Won”,9, “Closed Lost”,9,0)
<
CASE(PRIORVALUE(StageName) ,
“Prospecting”,1,”Qualification”,2,”Needs Analysis”,3,
“Value Proposition”,4,”Id. Decision Makers”,5, “Perception Analysis”,6,”Proposal/Price Quote”,7,
“Negotiation/Review”,8,”Closed Won”,9, “Closed Lost”,9,0)
Explanation:
In the formula,We have compared the two CASE for StageName and PRIORVALUE(StageName) and checked if CASE( StageName) is always less than than the CASE(PRIORVALUE(StageName)
