Table rules that compare two fields in Base by Zaweba
Use a table rule in Base by Zaweba to check one field against another in the same record, such as a due date that must fall on or after the invoice date.
Some checks can't be made by looking at one field on its own. A due date is only wrong in relation to the invoice date. A finish time only makes sense if it comes after the start time. A discount is only too big compared with the price. For these, Base by Zaweba has table rules: a rule on the whole table that compares fields in the same record, with its own message for when a record breaks it.
You set them in the Rules dialog for the table. A typical rule reads [DueDate] >= [InvoiceDate], with a message such as "The due date can't be before the invoice date." Field names go in square brackets, just as they do in Base's other expressions.
When a field rule isn't enough
A field rule checks a value against something fixed: zero, a range, a pattern, a list. That covers a lot, but the moment the limit is another field, you need a table rule. The test is simple. If you find yourself wanting to write a field rule that mentions a different field, it belongs in the Rules dialog instead.
Dates are the most common case. Bookings with an end before their start, memberships that expire before they began, and loans returned before they were lent are all easy typing slips, and all are caught by one comparison. Base's own help points to sensible date checks as the first use for table rules, and it is a good place to start.
Writing the comparison
Think about what should always be true, then write that as the rule. "The end is on or after the start" becomes [End] >= [Start]. Choose between greater than and greater than or equal with care. A one day hire might start and end on the same date, so >= is right; a strict > would refuse a perfectly good record.
Think too about empty fields. If the due date is sometimes left blank until later, decide whether that should be allowed, and test the rule with a record that has the field empty before you rely on it. It is better to discover how a rule treats a blank on a test record than on a busy Monday morning.
The message matters as much as the rule. Name both fields in it, so the person knows which of the two to change. "The return date must be on or after the date the item was lent" is clear; "Invalid dates" sends people hunting.
How it fits with the rest of Base
Table rules sit alongside the field rules, input masks and value lists in the table designer, and together they describe what a good record looks like. Use field rules for single values, value lists for fixed choices, masks for typed patterns, and table rules for anything that involves two columns. The Rules dialog is also where you add indexes over several fields, so it is the natural place to think about the table as a whole.
Because Base enforces its rules in the database itself, a table rule protects every way records arrive: typing in the datasheet, forms, imports and button scripts. A script that sets a due date too early is stopped just as a person would be, which makes scripts safer to write.
A few rules worth having
In an invoices table, a due date on or after the invoice date. In a bookings table, an end on or after a start. In a stock table, a reorder level no higher than a maximum level, if you keep both. In a members table, a renewal date after a joining date. Each is a single line, and each saves someone from a puzzling report later.
Add them early if you can, and keep the list short. A table with two or three clear rules is easy to work with; a table with twenty becomes a puzzle for the people typing into it.
