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 fourth 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 16
User has a requirement where they do not have dependent picklist but they want two picklist 1 and picklist 2 to be related in such a way that If one picklist 1 is selected then picklist 2 should not be blank
AND(
NOT(ISBLANK(TEXT(Picklist_Field1__c))),
ISBLANK(TEXT(Picklist_Field1__c)
)
Explanation:
Here AND is used to combine two conditions.TEXT(Picklist) is used to to check if the text of picklist is blank.Most important is that We could have used dependent picklist feature for above but in case We do not want that feature then use above formula.
Example 17
User needs to create a formula on Account that says if the Name field begins with ‘VI’ AND the account type (picklist field) is “External” then to take the amount and multiply it by 0.1.
IF(
AND(
BEGINS(Name, ‘VI’),
ISPICKVAL(Type,’External’)
), Amount *0.1,
NULL
)
Explanation:
In the above example BEGINS is used ,the formula will return Amount *0.1 if the condition within AND satisfy.
Example 18
User want to have a formula calculate difference between two currency fields ‘Selling Price’ & ‘Cost Price’. in such a way that the diffrence should never be a negative value i.e. it should be an absolute value
ABS(Selling_Price__c – Cost_Price__c)
Explanation:
ABS Calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign.
Example 19
User needs a Formula to calculate the % difference between two fields
(Field1__c – Field2__c) / (Field2__c)
Explanation:
It is a simple formula,not much explanation required.
Example 20
User has a pick-list field labeled ‘Picklist Field’ with 3 different values in Opportunities, Value 1, Value 2, Value 3. User wants to create a formula field which adds a percentage to the ‘Amount’ field based on the value selected in Picklist Field.
So say if Value 1 is 17%, User would like to add the 17% to the value in the ‘Amount’ field and show the result in the formula Field.
CASE(
TEXT(Incoterms__c),
“DPP by air”, Amount * 1.20,
“DPP by Boat”, Amount * 1.17,
“Courier”, Amount * 1.15,
Amount
)
Explanation:
The return type of formula should be currency. The above adds 20% to the amount filed for Value 1 , 17% for Value 2 and 15% for Value 3.
If the picklist field is blank it just shows the amount field.
