Validation rules with your own messages in Base by Zaweba

Bad data rarely arrives all at once. It creeps in one record at a time: a negative quantity here, a score of 150 out of 100 there, an invoice number typed without its prefix. A validation rule stops each of those at the door. In Base by Zaweba you set a rule on a field in the table designer, write the message people should see when they break it, and from then on the database refuses records that don't fit.

The rules are written in the style anyone who has used Access will know. Some examples: >= 0 for a quantity that can't be negative, Between 1 And 100 for a percentage, Like "INV-*" for a reference that must start with INV-, In ("A", "B") for a code that may only be one of two letters, and Is Not Null for a field that must not be left empty.

Writing a rule that helps people

The rule decides what is allowed. The message decides whether people understand why they were stopped. A message such as "Quantity must be zero or more" is far kinder than a technical error, and it tells the person exactly what to change. Write it the way you'd say it to a colleague at the next desk, in plain words, and say what is expected rather than only what went wrong.

Keep each rule about one thing. If a field needs to be both present and within a range, it is often clearer to make it required in the designer and use the rule for the range, so each problem gets its own message.

Where the rule is enforced

This is the part that makes Base's rules worth setting. A rule is not just a check on one form. Base turns it into a CHECK constraint inside the SQLite database, and into the same kind of constraint in MySQL or MariaDB when you export the database as a website. That means the datasheet, every form, imports, button scripts and the exported website all enforce it, and all of them show your own message.

So you write the rule once. You don't have to remember to add the same check to a new form next month, and an import of a spreadsheet can't sneak in a row that a form would have refused. People working in a browser on the exported site meet the same rule, in the same words, as you do on your PC.

Adding a rule to a table that already has data

Rules are easiest to add when a table is new, but you will often think of one later. Base handles that carefully. If you add a rule that records already in the table break, Base refuses it and tells you how many records are the problem. Nothing is half applied and no data is quietly changed.

Treat that count as useful information. Use a query or the datasheet's filter to find the records that break the rule, put them right, then add the rule again. Sometimes the count tells you the rule itself is wrong: if forty records have a value you thought was impossible, it may be a real case your design didn't allow for.

Choosing sensible rules

Rules work best on things that are always true. A price is never negative. A score is never above its maximum. A code always has its prefix. Be wary of rules that are only usually true, such as an age below 100 or a town from a short list, because the day a real exception turns up, someone can't enter it and may type something false just to get past.

For a choice from a fixed set, a value list with the option to allow only those choices is often friendlier than an In rule, because people pick from a dropdown instead of guessing. For a rule that compares two fields, such as a due date after an invoice date, use a table rule rather than a field rule.

A handful of well chosen rules, each with a clear message, will keep a database tidy for years with almost no effort from anyone.