The Best Way to Require Fields Based on Opportunity Stage

Many businesses need to ensure that specific fields are filled in on the Opportunity throughout the sales cycle. You'll often find sales methodologies like BANT or MEDDPICC built this way, where certain fields are required at key stages throughout the deal cycle.

If an org is restricting stage progression (i.e. stages can only progress in a linear fashion and cannot skip nor revert stages), then this is fairly manageable. It means a validation rule focused on a specific stage change should generally cover the bases because it will de facto cover that stage and all stages thereafter. In this scenario, a validation rule such as the following would suffice:

But if an org is not restricting stage progression, then not only the target stage that is being moved into needs to be in the validation rule, but additionally every stage after the target stage also needs to be specified in the validation rule. This is because if a user were to skip the target stage, then they could effectively bypass the validation rule entirely and create data gaps.

For important data points for business insights, this is just not okay. This problem often leads admins to build a validation rule that becomes cumbersome and commonly looks like this, in order to require the target field whether a stage is skipped or progressed linearly:

How to make this better

Instead of validating directly based on the Stage's text value, this can be dramatically simplified by using a CASE() function to return a numerical value for each stage. Then, the validation rule can simply use numerical operators to compare the value of the current stage against the value specified in the validation rule.

Another benefit is that this is highly scalable, especially if you keep some "space" between each number in the CASE() function to allow for room to add stages into the sales cycle later on without messing up all of your validation logic and causing a huge headache. Let me show you what I mean...

Create a "Stage Number" field

First, create a new formula field on Opportunity called "Stage Number" in a formula data type that returns a number value. Use a CASE() function to return a specific number for each value in the Stage field, such as:

Reference the "Stage Number" field in validation rules

Then, you can do a numerical comparison in validation rule logic instead of explicitly listing out every stage text value. Take the example provided earlier where each of stages 2, 3, 4, 5, and 6 were listed out explicitly in the validation rule logic. This could now be written instead as simply:

By running the numerical comparison, the logic will process the exact same way as the other validation rule driven by text values. If the Stage is "stage 2 or later", then the validation logic will be executed, without having to hardcode every stage's text value into the logic.

New stage? No problem!

Now let's consider a scenario where a new stage is added in. This is often a complex project that requires org-wide audits of stage references and countless spreadsheets listing every bit of configuration that needs to be updated. Some of that will still be required, but validation rules can be alleviated significantly using the model proposed here.

Let's pretend stageX is added in between stages 3 and 4. Using the old model, the logic would have had to be manually updated to include stageX in the logic of every single validation rule that's triggered by a stage change. The clunky solution for this would look like:

The much simpler solution to this is to add the new stage only into the "Stage Number" field with the numerical value placed in order. If this update is made, and if all validation logic uses the Stage Number numerical comparison rather than comparative text values, then you could get away with achieving the same outcome with a fraction of the effort.

Here's what that would look like, updating the "Stage Number" formula field first...

... and with that change, all of the validation rules would operate the exact same way without needing any changes at all!