Using a Picklist in a Validation Rule

Business Problem:

It’s not uncommon for businesses to use picklists to ensure data quality and conformity.  This often comes up when working with Opportunities.  A good example of this is listing the Competitor that you lost a deal to in order to track who your biggest competitor is.

 
Formula Rule Solution:

 

AND(
ISPICKVAL(StageName,”Closed Lost”),
NOT(
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value1”)),
NOT(
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value2“)),
NOT(
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value3“)
)
)

Let’s break this validation rule down.  In this rule I am introducing the NOT() function which is a great function for determining is something is NOT true… or false if you are following the logic.  However, the NOT() function only works with one parameter at a time.  Let me explain.

THIS IS POOR SYNTAX:

NOT(
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value1”),
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value2”),
ISPICKVAL( Competitor__c, “Competitor_Picklist_Value3”)
)


The NOT() function will only work with one parameter.

The odd thing here is that if your picklist is actually a multi-select picklist you don’t need all this mumbo-jumbo.  All you need is something that looks like this:

AND(
ISPICKVAL(StageName,”Closed Lost”),
ISBLANK(Competitor__c)
)

Next Birthdate Formula

Business Problem:

Often times businesses will want to track customers/clients birthdays.  However, it’s also nice to know that a birthday is coming up (in the future) instead of just the birthday date. 

 
Formula Rule Solution:

IF(
AND(
MONTH( Birthdate ) >MONTH(TODAY()),
DAY(Birthdate) + 1 > DAY(TODAY())
),
DATE( YEAR(TODAY()), MONTH( Birthdate), DAY(Birthdate)),
DATE( YEAR(TODAY())+1, MONTH( Birthdate), DAY(Birthdate))
)

 

 

* This formula works with any date field but Birthdate is the standard field name on Contacts

** Please note: Person Account fields are not yet available via formula’s so this PersonBirthdate will not work here.

Print the Month based on a Date Field

Business Problem:

Often times you might want to filter a report based on the Month of a specific date or you will need a field to display on a record based on a date field.  This is great for formula concatenations or reporting purposes

Formula Rule Solution:

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 1, “January”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 2, “February”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 3, “March”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 4, “April”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 5, “May”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 6, “June”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 7, “July”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 8, “August”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 9, “September”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 10, “October”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 11, “November”,

IF(

Month(datevalue(Any_Date_Field_Here__c)) = 12, “December”,

NULL

))))))))))))

 

This is a relatively simple formula but let’s break this down anyways.  The Month() function takes a date field value and gives you the number representation of that date.  Therefore, if you want to display the actual Month in words you need an additional statement.  The Datevalue() function breaks a date field down further and allows Salesforce to turn the date into a number.  So, when you have the Month(Datevalue()) functions together you can get a Number representation from the Month field.

Opportunity Validation Rule to Require Multiple Fields Validation Rule

Business Problem:

Opportunities in Salesforce utilize a standard field called Probability which is tied to the stage and forecasting.  Once an Opportunity reaches certain probabilities businesses may want to require certain fields to have a value. 


Validation Rule Solution:

AND(Probability > 50,
OR(
ISNULL(First_Field__c),
ISNULL(Second_Field__c),
ISNULL(Third_Field__c)
)
)

This Validation Rule is relatively simple but there are a few gotcha’s that I want to breakdown here that can be big headache’s if you don’t know how they work.

First, the probability ignores the “%” symbol so you don’t need those in your formula.  Additionally, notice that I did not put a “,” behind the ISNULL(Third_Field__c) statement.  This is because this is the last statement in the OR() statement and you don’t need any additional commas inside that statement.

So why not put all this in one big AND() statement?

Here’s why.  If you put everything in an AND() statement everything must be true in order for the validation rule to trigger.  In the business case, First_Field__c,Second_Field__c, Third_Field__c each need to be filled out. However, if they are all included in the AND() Statement, if one of the fields is filled out the validation rule would not trigger.  Hence, the use of OR()!

Calculate a Currency based on a Picklist Value

Business Problem:

In some cases, businesses might use a Picklist Value to reference a number such as a month, quarter, term, or year. This Picklist value often correlates to a number. Sometimes businesses might want to make a calculation based on this number and an amount field or another currency field.

Formula Solution:

Data Type: Currency

IF(
ISPICKVAL( Term__c , “1”), Currency_Field__c * 1,
IF(
ISPICKVAL( Term__c , “2”), Currency_Field__c * 2,
IF(
ISPICKVAL( Term__c , “3”), Currency_Field__c * 3,
IF(
ISPICKVAL( Term__c , “4”), Currency_Field__c * 4
,NULL
)
)
)
)

Let’s break this formula down.

An IF() Statement is comprised of three variables, something variables to compare, an action if the variables to compare is true, and an action is the variables to compare is false.

IF(ISPICKVAL( Term__c , “3”), Currency_Field__c * 3,

In this case, the ISPICKVAL( Term__c , “3”) is the variable to compare Currency_Field__c * 3 is the action if true. Notice we don’t have an action if False set in this example. This is because we have several IF() statements, one for each of the picklist values in your picklist. At the end, you only need one action if all the IF() statements are false.  The last thing to remember is to always close all of your statements with closed parentheses and in the example, we have there are 4 close statements.

Make your Formulas #Error! proof

I recently ran into an issue where a formula was producing a #Error! result. This was actually fine because I was expecting this since part of the formula wasn’t displaying. However, I wanted to make another formula field where I would use this field as part of my criteria. When you do this, the dependent formula breaks.

Here was my situation:

Original Formula:

Seats_Used__c / Total_Seats__c

This formula produced a simple percentage used formula. However, without the Total_Seats__c field filled in this formula results in #Error!. Again, I was fine with that until I used it in a dependent formula. Anytime a formula with #Error! as the result is referenced in a dependent formula the result will also be #Error! for that formula.

The fix is to make your formulas #Error! proof.

#Error! Proof formula:

IF(
Total_Seats__c <> 0,
Seats_Used__c / Total_Seats__c ,
0
)

This formula takes into account the source fields and selects the denominator and checks if this field is zero or not. Then we can return a result of the actual formula or zero. Either way, a result is returned and will never result in a #Error!

Time Based Formulas – Converting a Number to Hours and Minutes

Time Based formulas can get complicated so I like to have some base formulas that I work with. Here’s a good example of one of the base formulas you can use to build on top of. This formula uses the FLOOR() function to convert a number field that has a decimal in it to Hours and Minutes.

TEXT(
FLOOR( Time_Number__c )
)
+ ” Hours”
+ ” and ”
+
TEXT(
(
(
Time_Number__c – (FLOOR( Time_Number__c ))/1)*60
)
)
+ ” Minutes”

Let’s discuss what FLOOR() does for just a second – https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#FLOOR

It essentially returns the whole number of a number field.

Let’s break down our formula.

TEXT(
FLOOR( Time_Number__c )
)

** Returns Text of the Whole Number. (i.e. 12.5 will return 12)

+ ” Hours”
+ ” and ”
+

* Adds in some text. Take note of the spaces added in to make it read well.

TEXT(
(
(
Time_Number__c – (FLOOR( Time_Number__c ))/1)*60
)
)

** Takes the Number and subtracts it from the whole number. This returns just the decimal portion of the Number which is then divided by 1. This returns a percent of the decimal portion of the number and then multiplies this by 60 to return the number of minutes. For Example, this 12.5 converts to 12 Hours and 30 Minutes.

Adding to the Backpack – Activity Closed Within the Same Week

I geek out over formulas and a fun one came across my desk today. I responded to a question on the Salesforce Answers Community regarding a formula that will allow for the measuring of whether an activity is closed within the same week of the Due Date. The use case here is interesting because most organizations that measure activities also measure whether it was closed. Taking that a step further you can measure if the activity closed on the same date of the Due Date which lets you measure the accuracy of when those activities are actually getting closed out. But, its not always fair to measure just Due Date against Completed Date.

This formula, combined with a custom date field called Completed Date and a Workflow Rule, will check a checkbox if the Due Date is completed within the same calendar week the Activity is marked Completed.

I’ve added this to my Backpack as a unmanaged package you can easily install.

Activity Closed Within the Same Week – https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000lT4v

IF(
AND(
YEAR(ActivityDate)=YEAR(Completed_Date__c),
IF(
	  CEILING( (  ActivityDate  - DATE( YEAR( ActivityDate  ), 1, 1) + 1) / 7) > 52,
	  52,
	  CEILING( ( ActivityDate  - DATE( YEAR( ActivityDate  ), 1, 1) + 1) / 7)
	)
= 
IF(
	  CEILING( (   Completed_Date__c - DATE( YEAR( Completed_Date__c), 1, 1) + 1) / 7) > 52,
	  52,
	  CEILING( ( Completed_Date__c- DATE( YEAR( Completed_Date__c), 1, 1) + 1) / 7)
	)
),
TRUE,
FALSE
)