A Sneaky Validation Rule Trick

A short while ago I wrote a post covering some of the intricacies involved when working with the system fields that are present on all records on the force.com platform, part of which included a validation rule which could potentially be used to prevent record creation according to some date based criteria. After reading that post, the inimitable Mike Gerholdt dropped me a note to say that the idea could be worth expanding on, and so here is a post for the ButtonClick Admins out there.

If you are a developer you should read on too, for precisely the same reason that you should read Mike's blog, which he explains here; because if you don't know know the ButtonClick side of things, then you're not yet ready to work on the code side.

The Scenario

Say we have a custom object, called "Special Bonus", which can be attached to an opportunity during the sales process and represents some kind of freebie that should be sent to the customer in question. Sure we could use a couple of custom fields on the opportunity, but hey, what if we wanted to give two special bonus prizes to a customer for placing a single order? We're better off using a custom object to provide maximum flexibility.

The Special Bonus object in the schema builder

For financial reasons, the company has decided that from now until the end of the year (Dec 31st) no more special bonuses are to be granted to customers as they'll be given Christmas hampers anyway, and those things aren't cheap.

The Solution Mk I:

Send the sales team a message on Chatter and tell them not to give out any special bonuses.

This may work well for a small company, but for a large company with a few hundred staff doing deals? Probably not going to be as robust as we'd like.

The Solution Mk II:

Revoke the Create permission from the sales team member profile.

Definitely a workable solution, but there may be quite a few profiles involved and there could be a chance some of them would be forgotten when revoking and/or granting the permission later on; also it would be nice to remove the manual work involved in reinstating the permissions, especially if staff are likely to be on leave.

The Solution Mk III:

Use a validation rule to prevent record creation until the specified date.

Ah ha! A nice, easy, repeatable solution! All we need to do is check the CreatedDate against the allowed date, and we're good to go, right?

The validation rule intended to prevent record creation until 2013. Note that CreatedDate is a datetime field, hence the need to use the DATEVALUE() function when comparing it with a date

Unfortunately not. If you try and test this rule by creating a record you'll find that it doesn't prevent you from doing so. The reason for this is that validation rules run on the data entered before the record is actually stored in the system, and because the record has not yet been stored in the system it doesn't have a time stamp in the CreatedDate field— the record hasn't been created yet.

At this stage, most developers would likely write a simple trigger which runs before the records are inserted, and if the current date is less than the required date, add an error to each of the records (we bulkify our triggers of course) to prevent them from being saved. Doing so would then necessitate test coverage, and already it's more of a maintenance hassle than the basic workflow rule would be, in addition to the fact that it would be harder for non-developers to work with.

The Payoff

Taking a step back however, the problem that prevents our rule from functioning correctly (the lack of a value in the CreatedDate field) is actually a blessing in disguise; by specifically checking for a value in this field we now have the ability to author validation rules which only run on new records, or conversely, only on records which are being updated!

This check can be performed through the use of the ISNULL() function. For our particular scenario if the CreatedDate field is null (i.e. it has no value) then the record in question has not yet been saved, meaning that all we have to do is check whether the current date is an acceptable day to be creating records or not! The new formula is:

ISNULL(CreatedDate) && (TODAY() < DATE(2013, 01, 01))

If the created date has a value then the whole expression equates to FALSE meaning the record will pass the validation rule and saved as required. If it the field has no value, i.e. it is a new record, then whether it passes or fails is entirely dependent on the second part of the test.

If the '&&' in the rule is unfamiliar, you may want to read one of my previous posts, Two Developer-Style Tips for Salesforce Admins, and if you're wondering what all the parentheses are for, one pair is there purely to make the expression easier to read as a human.

Finally, just to round things off nicely, if you wanted to write a validation rule which only applied to existing records, you'd simply check that CreatedDate was not null:

NOT(ISNULL(CreatedDate)) && <<_validation logic here_>>

Once again, clicks not code is always the first approach to take if there is a viable solution available, after all, coding is so 1998*.

Related Posts

• Two Developer-Style Tips for Salesforce Admins
• Guest Posting For the Dark Side (Admins!)
• Coding on Force.com — What Do You Need to Know?

*Not that I will ever stop coding, it's far too much fun!