Last week I wrote about using the Column Validation feature to help guarantee that information you capture in your lists is formatted correctly and the right type of information. This is done using the “pattern-matching” functionality, and it’s very easy to set up and use.

However, there’s a lot more that you can use the Validation feature for; you can also base your validation on conditional data, dynamically drawn directly from the list!

Let me show an example. In this list, my salespeople are all assigned to specific regions. I want my system set so that salespeople cannot register sales outside of their regions – no poaching allowed!

To set this up, I’ll start by marking the Sale column as the one I want to act on. If sales are anything over than $0, the validation rule will run.

Now, I’ll set the conditions. Just to keep it simple, I’ll just concentrate on one salesman for the moment – Mr. Smith. Smitty can sell in the West and South regions, but he’s not allowed to sell in East or North.

So, we need to set Salesman name to Smith, and then add an “And” to the rule to set Region to East, then an “Or” for region North.

Let’s save this and test it.

So, this was almost right – Smith is blocked from selling in East and North, but the way I set the rule up blocks everyone else from selling to North, too! I need to make one more condition in my Validation rule – I need to specify Smith on that North exception.

This might be a good time to point out that when you’re using “And/Or” conditions, you need to be cognizant of the logic. Using “Or” basically sets the rule back to zero and ignores all previous conditions.

You’ll notice that the order in the two “Or” clauses are reversed, but this actually doesn’t matter; since they’re connected by an “And,” they both have to be true.

Anyway, when I test this now, it works exactly as expected – Mr. Smith cannot sell to East or North, but everyone else is unaffected by the rule. At this point I can either add more conditions for the other salespeople, or I can create new Validation rules for them individually – it’s user’s choice.

Make sure you check out the tutorials and videos about Validation on the Infowise site!