Learn Salesforce Formulas with Examples – Part 9 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 56
Here the user has three fields, “Last_Date__c” is of type Date, “Picklist__c” is a picklist and “Field C” is a formula field, now here is the use case:
- Based on selection of “Picklist__c” picklist value among (Monthly, Quarterly, Annually, Semi- Annually)
- Based on the Last Date in “Last_Date__c”
- “Field C” should calculate and display when is the next date of commencement of the event.
Field C = Last_Date__c +
CASE (Picklist__c,
“Monthly”,30,
“Quarterly”,91,
“Semi-Annually”,182,
“Annually”,365, 0)
Explanation:
- In this formula, there are two sections, the first one returns the “Last_Date__c” of the event.
- The second one is the picklist bound to the case which has finally attached number of days with each picklist value, this means for each picklist value which has some number of days will now be added to the Last date and the sum would return the next commencement date when event will occur.
Example 57
In this example for validation rule where the user has three fields:
MultiPicklist1__c, Picklist2__c, MyPicklist3__c
Now here is the use case:
- If the field “Picklist1__c” which is a multi-select picklist has some value as “Completed”
- If the field “Picklist2__c” which is single select picklist has some value as “Manual”
- MyPicklist3__c which is single select picklist cannot remain empty
AND (
INCLUDES (MultiPicklist1__c, “Completed”),
TEXT(Picklist2__c) = ” Manual”,
ISBLANK(TEXT(MyPicklist3__c))
)
Explanation:
- This validation rule can be an example of how to make a field required, here the formula checks if the value of field “MultiPicklist1__c” has a value “Completed”.
- Checks the text value of picklist field “Picklist2__c” as “Manual”.
- Then an exception is thrown if value on field “MyPicklist3__c” is empty.
Example 58
Here the user calculates the age group of Contact record which is then used for sending the Subscription Email as per the age group, here’s the use case:
- Assume, “Birthday__c” is the date field on contact object.
- Another field “AgeGroup__c” is a formula field which calculates the age group of the record.
IF (ISBLANK( Birthday__c ), “N/A”,
IF ((TODAY() – Birthday__c) / 365 < 20, “Below 20”,
IF ((TODAY() – Birthday__c) / 365 < 30, “20-29”,
IF ((TODAY() – Birthday__c) / 365 < 40, “30-39”,
IF ((TODAY() – Birthday__c) / 365 < 50, “40-49”,
IF ((TODAY() – Birthday__c) / 365 < 60, “50-59”,
IF ((TODAY() – Birthday__c) / 365 < 70, “60-69”,
IF ((TODAY() – Birthday__c) / 365 >= 70, “Above 70”,
“N/A”))))))))
Explanation:
- Here at line (1) the formula checks that if the value in the field “Birthday__c” is not empty, if it is then the value in the field “AgeGroup__c” is “N/A”.
- At line (2) the formula validates the difference between the Today’s date and value in field “Birthday__c” which is divided by number of days in a year 365, if the returning number is smaller than 20, the value displayed on the field “AgeGroup__c” is “Below 20” and so on…
- On line (8) the formula checks if the difference between the Today’s date and value in field “Birthday__c” which is divided by 365, if the returning number is greater than or equal to 70 than the value displayed on the field “AgeGroup__c” is “Above 70”.
Example 59
This formula calculates the lifetime of lead by calculating the Number of days’ lead has been created (Irrespective of whether it’s converted or not)
Here’s the use case:
- On basis of the creation date of a lead the user want to calculate the number of days since it has been into the system or age of a lead and display using the formula field.
IF (IsConverted,ConvertedDate – DATEVALUE(CreatedDate), TODAY() – DATEVALUE(CreatedDate))
Explanation:
- The first part of the formula calculates if the lead is already converted, in this case the Number of days will be calculated if we subtract “LeadCreatedDate – LeadConvertedDate” if we check we also find that we have actually converted the date-time value of created Date and Conversion date to DateValue.
- The Second part of the formula calculates the Lifetime of lead by subtracting “LeadCreatedDate – Today’s date”.
Example 60
Here is an example of Validation rule, where on basis of values in multiple fields the Validation rule should fire.
Here’s the use case:
- Let’s take an example of Account Object, which has 3 record types say (Group, Branch, Vendor) so If the Account RecordType is “Group”
- Picklist value of Account “Status” is set to “Closed”
- And Value in field ReasontoClose__c is Blank, the validation rule should fire.
AND(
ISBLANK( ReasontoClose__c),
ISPICKVAL( Status, “Closed”),
RecordType.Name=” Group ”
)
Explanation:
- The formula evaluates that if the AccountRecordType.Name == “Group” and the Status Picklist value selected is” Closed” and the field “ReasontoClose__c” is blank than the Validation rule should be triggered stating that “ReasontoClose__c” cannot be blank.
Example 61
In this formula, the user want to display the Final amount to the Donor who makes online donation, if the Amount of donation does exceed a particular amount then the checkbox is changed to true using Workflow and the formula field displays the Final Tax amount to be Donated by the user with the donation.
Here’s the use case:
- Take an example of custom object “Donation” which has a field DonationAmount__c, FinalTaxableAmount__c and the Checkbox named “Taxable__c”
- If the donation amount is taxable then the formula should calculate the final tax amount
FinalTaxableAmount__c = DonationAmount __c *
IF (Taxable__c = TRUE,
0.10,0)
Explanation:
- According to the formula the value in “FinalTaxableAmount__c” formula field is to be calculated based on whether the checkbox “Taxable__c” is true or not, if it’s true the formula returns the “DonationAmount __c*0.10” which returns the tax amount to be paid with the donation amount.
Example 62
Here the user needs to build a Validation formula which evaluates a particular field should not be left blank based on the following:
- There is an object “ABC__c” which has picklist of “ABCType__c” values as say (“A”, ”B”, ”C”)
- There’s another object “MyObject__c” that has lookup relation to “ABC__c” object and another picklist field as “Type__c” having values say (“A”, ”B”, ”C”)
- There’s another field on object “MyObject__c” named as “MyField__c” as text, which should not remain empty
AND(
ISPICKVAL(Type__c , “A”),
OR(
ISPICKVAL( ABC__r. ABCType__c, “A”),
ISPICKVAL( ABC__r. ABCType__c , “B”)
),
ISBLANK(TEXT(MyField__c))
)
Explanation:
- Here in the first section of formula we have checked if the “Type__c” field on “MyObject__c” has the Value as “A” or “ABCType__c” field on “ABC__c” object has values as “A” or “B” then based on this “MyField__c” cannot be left blank.
Example 63
In this Example the user want to create a validation rule such that a particular field only accepts numbers as input that too in a specified format
Here’s the use case:
- Object Contact has a custom field “PersonnelPhone__c” which is text so needs to validate that data entered in this field should be of type Number and that too in a specific format
AND
(
NOT(ISBLANK(PersonnelPhone__c)),
NOT(REGEX(PersonnelPhone__c,”[0-9]{1}-[0-9]{5}-[0-9]{2}-[0-9]{3}”))
)
Explanation:
- In the first part of the formula we have checked if the value in the “PersonnelPhone__c” field is not empty.
- In the second part, we have used the regex function to set the format/expression of the incoming input, however you can change it as per your need.
For more information on Regex functions I would recommend reading: http://sforce.co/2hSFEH2
Also, don’t forget to go through the following documentation about Regular expression syntax based on Java Platform SE 6: http://bit.ly/1BnkCak
Let’s get deeper into setting the format:
- [0-9] this format says that we can only set numbers from 0 to 9 in the field.
- {3} this signifies number of entries
- Bringing together both of these: if [0-9]{3} is specified as the expression in the formula the user can enter three digits in any flow like ”123” or “921” or ”211”
Example 64
Here the user has some fields say “FieldA”, “FieldB”, “FieldC”, “FieldD” if value is entered in anyone of these, then the other fields should remain empty so need some validation rule.
Here’s how:
- Take four fields “FieldA”, “FieldB”, “FieldC”, “FieldD”, only one of these can have a value assigned.
(
IF(ISBLANK(FieldA),0,1) +
IF(ISBLANK(FieldB),0,1) +
IF(ISBLANK(FieldC),0,1) +
IF(ISBLANK(FieldC),0,1)
) > 1
Explanation:
- Here I have used the IsBlank function that returns either “True or False” which is “0 or 1” considering the four fields if none of the fields has a value that means count remains “0” which is >1 so no issues as it is already validated.
- If we enter a value in any one field, then the result is 1>1 so again no issues as it is already validated.
- If the user tries to enter the value in more than one of the fields then the equation becomes X which is X>1 so the validation rule gets fired.
Hope you enjoyed the blog so stay tuned for upcoming blogs in this series.