Skip to main content

Prevent Duplicates and Double Bookings

Goal​

Make a table refuse two kinds of record outright:

  • Duplicates — a second product with the same code, a second person with the same email.
  • Double bookings — a second booking of the same room for a time that overlaps one already made, a second shift for the same person at the same time.

Prerequisites​

  • You are signed in with a maker role, and the table's solution is open.
  • For a no-overlap rule, the table has two date and time fields: when the period starts and when it ends.

Steps​

  1. Open the table and select Constraints in its menu.
  2. Add a rule:
    • No duplicates — then choose the field. The rule reads No two records may share a value in Code.
    • No overlaps — then choose the start and end fields of the period, and, if bookings of different things may overlap each other, the field that says which thing is booked: No two records may overlap in time, for each Room, from Starts to Ends. Leave the first box on the whole table when there is only one thing to book.
  3. Select Save.

To take a rule off, select the bin at the end of its line and select Save.

What happens behind the scenes​

  • The database enforces these rules, not the form. They hold however a record is written — on a form, by a spreadsheet import, by a script, by a process — and they hold when two people save at the same moment: one save succeeds and the other is refused. A check written in a business rule cannot promise that, because both saves can read "nothing there yet" before either writes.
  • A record with the field left empty is not a duplicate. Any number of records may leave a unique field blank. If the field must also be filled in, make it required as well.
  • Back-to-back periods do not overlap. A booking that ends at 10:00 and one that starts at 10:00 are both allowed.
  • The person saving is told which rule they broke. A refused save says the value is already used in that field, or that the period overlaps another, and nothing is saved.
  • The rules travel with the solution, and are enforced in every environment the solution is imported into.

When the records already break the rule​

A rule can only be added if the records already in the table obey it. If two products already share a code, saving a No duplicates rule on Code is refused with a message naming the field, and the table keeps the rules it had. Your rule stays on the screen: correct the records, then select Save again.

Troubleshooting​

  • The period pickers are empty. — The table has no date and time fields of its own. Add the start and end fields first.
  • A field I want is not offered. — Files, images and lookups to several tables cannot be part of a rule: they do not hold a single value that can be compared. Platform fields such as Created On are not offered either.
  • A rule I removed still seems to refuse saves. — Make sure you selected Save after removing it; the list is saved as a whole.