How to delete old records in bulk

Use a delete action query. In Base by Zaweba you set conditions that pick out the old records, such as dates before a cut-off, and run the query. Base says how many records will go and asks before deleting. If other records still need any of them, it deletes nothing and tells you why.

That last part is what makes bulk deleting safe enough to do without fear.

Choosing what counts as old

Build the conditions as you would for any query. A date field before a given date is the usual one: bookings before last January, enquiries older than two years, stock movements from a closed financial year. Combine it with others if needed, such as status equals Cancelled as well as an old date.

A between condition with two dates removes one specific period, which suits a yearly clear-out of a single closed year.

Look first, delete second

Build an ordinary select query with the same conditions, run it, and read the list. Count roughly how many you expect. Then make the delete query.

When it runs, Base shows how many records it will remove and asks you to confirm. If the number is bigger than you expected, stop and check.

Press File > Back up now before you start. Base keeps automatic daily copies too, but a backup from a minute before a deletion is the cleanest way back if you change your mind.

When records depend on each other

Deleting is where links between tables really matter. An old customer may still have orders. An old product may still appear on invoice lines. Each lookup field has a rule for what happens when the record it points at is deleted: refuse, delete the referring records too, or empty the lookup.

If the rule is refuse and some of the records you're deleting are still needed, Base deletes nothing and explains. That's deliberate. A bulk delete that removed half the records and then stopped would leave a mess. Delete the dependent records first (old order lines, then old orders, then customers with nothing left), or change the rule if a cascade is really what you want.

A cascade deletes everything hanging off the record as well. That is right for an invoice and its lines, and dangerous for a customer and years of history, so think it through.

Archive instead of delete

Old records often have value later, for accounts, for reports, or for someone asking what happened. An append query can copy them into an archive table before you delete them from the main one. Or a make table query can create a new table from the selection. Then the delete only tidies the working table, and the history is still in the file.

Another approach is to export the old records to CSV or to an .xlsx sheet, keep that file somewhere safe, and then delete.

After a big clear-out

Deleted records leave unused space inside the file. Database > Compact and repair hands it back, rebuilds indexes and checks the links, after making a backup copy. Running it after a large deletion keeps the database small and quick.

Saved delete queries can be run by double-clicking them in the navigation pane. They stay in the desktop app and are not exported to the website Base by Zaweba can make, so nobody can bulk delete from a browser.