Learn Salesforce Formulas with Examples – Part 6
Learn Salesforce Formulas with Examples – Part 6 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
User is trying to create a formula to find out how many days have passed between the qualifying date and the close date of an opportunity before it is filled.
(5 * (FLOOR( ( CloseDate - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( CloseDate - DATE( 1900, 1, 8), 7 ) )) - (5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) )
In this formula,We have used certain functions like DateValue, The formula above determines the number of days between the “Qualifying Date” and “Closed Date” fields in the Opportunity object. You can modify this formula to calculate the number of business days between any date/time fields in any other object. Simply switch the date fields as appropriate.Please refer this awesome explanation here to this formula.
User needs a formula for a validation rule such that the Opportunity name should always contain 10 as last two digits
VALUE(RIGHT(Name, 2)) <> 2
VALUE(text) and replace text with the field or expression you want converted into a number.RIGHT will give the position value.
User needs a formula for a workflow rule which will automatically set Close Date to Today when opportunity moves to Closed/Lost
AND ( ISCHANGED(StageName), OR ( TEXT(StageName) = "Closed Lost", TEXT(StageName) = "Closed Lost - Qualification" ) )
ISCHANGED is used to check if the stagename is changed,to compare picklist values we have used TEXT(Picklist Value),We can also use ISPICKVAL for similar purpose.The formula is for workflow rule criteria and make sure the evaluation criteria is set to created and everytime its edited (2nd Option)
A system Administrator wants to avoid users to remove opportunity products for the closed won opportunities.
Create a custom Roll Up Summary field on Opportunity object to count the number of Opportunity Products. Let’s call it: Count_Opp_Line Items
AND( TEXT(StageName)= "Closed Won", Count_Line_Items__c < PRIORVALUE(Count_Opp_Line Items) )
PRIORVALUE will check the previous value of the field,AND will make sure both conditions return TRUE
User needs to update a date/time field to the Now() function when that status field is changed. This shouls also work When the record is first created as well
User is trying to create a formula with Evaluation criteria -Created, and every time it’s edited evaluation criteria.User wants a formula for his workflow + Field Update Action
OR ( ISNEW(), ISCHANGED(Status__c) )
ISNEW() will check if the record is NEW,so this formula will work in either of condition when the record is new and is changed
User wants to create a formula on contact such that if the Account Record Type is either of Type 1,Type 2 or Type 3,then it should populate Field 1 picklist else it should populate Field 2 picklist value.
IF( OR ( Account.RecordType.Name = "Type 1", Account.RecordType.Name = "Type 2", Account.RecordType.Name = "Type 3" ), TEXT( Account.Field1__c ) , Text(Account.Fiedl2__c) )
Here RecordType.Name will check name of the recordtype and TEXT(Picklist_Field) will return the text value for the picklist returned.
User has a standard SF “Date” field and wants to have a formula field to take and populate the date in the following format YYYYMMDD for integration requirements to other external systems.
User wants to have the formula such that all months and days <10 should insert a “0” in front of them to folow the format crieria.For Example – the format should be 19470815 where month is 08,year is 1947,date is 15
TEXT(YEAR(Date__c)) & IF(MONTH(Date__c)<10, "0",NULL) &TEXT(MONTH(Date__c)) & IF(DAY(Date__c)<10, "0",NULL) &TEXT(DAY(Date__c))
Year(Date),Month(Date),Day(Date) will give the year,month and day from the date.Check here to explore more about date and time functions.
User needs a formula to return all characters after a group of characters is found.
For example : ‘Description’ field on Cases contains “text text text text VINAY-1000 text text text text text text text text…”
If the text field contains ‘VINAY’ return VINAY-1000.User is aware that this cant be done as a custom formula field as long text fields cannot be referenced in a custom formula fields.So he has to use a custom field of text data type and then use a workflow with a field update action to update the text field as user can reference the long text fields in the field update formulas.Key word will always VINAY.User wants a formula for this workflow.
LEFT will Returns the specified number of characters from the beginning of a text string and RIGHT Returns the specified number of characters from the end of a text string.FIND Returns the position of a string within a string of text represented as a number.
User is trying to create a formula field called Region on Case where it says If the queue assigned equals “Asia Queue” then the result is ASIA, if the queue assigned equals “EMEA Queue ” then the result is EMEA and so on.
CASE ( Owner:Queue.Name, "Asia Queue", "ASIA", "EMEA Queue", "EMEA", NULL )
CASE will check for the Queue Name and will return NULL if no value is matched.
User wants to create a new formula field that will display “Low” if metric check box A is checked and then “Medium” if both A and B are selected. .
IF ( AND(Checkbox_A__c=TRUE,Checkbox_B__c = FALSE), "Low", IF ( AND(Checkbox_A__c=TRUE,Checkbox_B__c = TRUE), "Medium", NULL ) )
This is a nested IF example where IF is put insider the master IF.