Adventures in Salesforce formulas & validation rules with WEEKDAY(), IF(), & INCLUDES()

September 3, 2018 work

Formulas are one of those tricks up an admin's sleeve that are extremely powerful and cool.

It almost feels like coding and you can accomplish a lot with just a few lines of formulas in a field or validation rule.

Until recently, my experience with formulas had been pretty elementary but recent projects at work and Trailhead got my gears turning and I realized the endless possibilities of formulas.

In this post, I'm going to walk through a formula I recently built and list some resources that have really helped me as I've expanded my formula skills.

All changes in Salesforce require clear requirements and this one was no different. I work for a company that sells online advertising. My Ad Operations team who is responsible for trafficking ads noticed that sometimes sales reps would put the wrong date in for a product on a contract and that would cause confusion.

Wouldn't it be nice if depending on the product a sales rep entered they'd only be allowed to enter specific days of the week?

Validation rules and a complex formula to the rescue! If you're new to validation rules, check out the Salesforce docs and this Trailhead module.

I wanted the solution to be modified by an end user and flexible. Some of our products don't have a specific send date so I couldn't make a solution that requires a send date.

Example of the picklist on a product record.

I created a new multi-select picklist called 'Product Send Days' and put that on the Product object.

Now it's time to flip to the validation rule and the formula. The Opportunity Product object has a relationship to the Product object, which means I'd be able to reference it in my formula.

Accessing my Product Send Days field by drilling down from Opportunity Product.

IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false, true)

So what does the above say? Let's break it down.

WEEKDAY checks the ServiceDate to see if it's equal to 1, which is Sunday. The INCLUDES() is a function unique to multi-select which checks to see if the multi-select has the value you want.

We want something to happen when we ask the question 'Does the ServiceDate equal Sunday and does Product2.Send_Days__c include Sunday?' So we are gonna wrap it in an IF() statement!

IF(logical_test, value_if_true, value_if_false)

We already know our logical test. For value_if_true we're putting false because we don't want the validation rule to fire. If the sales rep is entering the date correctly, we don't want to stop them!

So now that we've broken down how to create it for Sunday, you just need to copy & paste to make it for the other 6 days of the week, like so:

IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false,
IF(WEEKDAY(ServiceDate)==2 && INCLUDES(Product2.Send_Days__c,'Monday'),false,
IF(WEEKDAY(ServiceDate)==3 && INCLUDES(Product2.Send_Days__c,'Tuesday'),false,
IF(WEEKDAY(ServiceDate)==4 && INCLUDES(Product2.Send_Days__c,'Wednesday'),false,
IF(WEEKDAY(ServiceDate)==5 && INCLUDES(Product2.Send_Days__c,'Thursday'),false,
IF(WEEKDAY(ServiceDate)==6 && INCLUDES(Product2.Send_Days__c,'Friday'),false,
IF(WEEKDAY(ServiceDate)==7 && INCLUDES(Product2.Send_Days__c,'Saturday'),false,
true)))))))

I didn't talk about the double ampersands (&&) but that's just a logical operator for AND.

I thought this formula was looking pretty good…until I went to test it. Remember when I said that some products didn't have specific send days? Well in the formula above there is no place to allow for that.

So I added some extra logic at the very top.

NOT((ISBLANK(Product2.Send_Days__c)) || (ISBLANK(ServiceDate)))

Here I'm saying, 'If the Send Days field is blank OR the ServiceDate field is blank, you can ignore and not fire.' I had to wrap it in a NOT() because I only want the validation rule to keep working if the fields aren't blank.

Similar to the double ampersands (&&) the double pipe (||) means OR. You can use either!

So here is the final formula in all its glory.

NOT((ISBLANK(Product2.Send_Days__c)) || (ISBLANK(ServiceDate))) &&
IF(WEEKDAY(ServiceDate)==1 && INCLUDES(Product2.Send_Days__c,'Sunday'),false,
IF(WEEKDAY(ServiceDate)==2 && INCLUDES(Product2.Send_Days__c,'Monday'),false,
IF(WEEKDAY(ServiceDate)==3 && INCLUDES(Product2.Send_Days__c,'Tuesday'),false,
IF(WEEKDAY(ServiceDate)==4 && INCLUDES(Product2.Send_Days__c,'Wednesday'),false,
IF(WEEKDAY(ServiceDate)==5 && INCLUDES(Product2.Send_Days__c,'Thursday'),false,
IF(WEEKDAY(ServiceDate)==6 && INCLUDES(Product2.Send_Days__c,'Friday'),false,
IF(WEEKDAY(ServiceDate)==7 && INCLUDES(Product2.Send_Days__c,'Saturday'),false,
true)))))))

As promised, here are some resources that have helped me a ton in my formula journey: Formula Operators A-H (Official Salesforce docs), Formula Operations I-Z, Formulas — Help, Tips & Tricks (Success Community), Steve Molis (Salesforce MVP and general formula guru), Trailhead, And of course, Google!

Shout out to Natalya Murphy who sat with me at WIT DC's #SalesforceSaturday to work on this with me.

Let me know in the comments if you've tried a similar formula or if you would have done it differently!