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
[codeblocks name=’Formula_Example_ 46′]
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
[codeblocks name=’Formula_Example_ 47′]
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.
[codeblocks name=’Formula_Example_ 48′]
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’
[codeblocks name=’Formula_Example_ 49′]
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.
[codeblocks name=’Formula_Example_ 50′]
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.
[codeblocks name=’Formula_Example_ 51′]
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.
[codeblocks name=’Formula_Example_ 52′]
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
[codeblocks name=’Formula_Example_ 53′]
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.
[codeblocks name=’Formula_Example_ 54′]
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.
[codeblocks name=’Formula_Example_ 55′]
Explanation:
- IMAGE Inserts an image with alternate text and height/width specifications.
- DATEVALUE Returns a date value for a date/time or text expression.
