Duplicate records, and how to stop making them

A table with the same customer in it three times reports that customer three times. Every total, every filter and every list inherits the error.

The duplicates arrive quietly, one at a time, because two people each looked for a record and neither found it.

Most duplicates come from the search, not the typing

Somebody searches for a customer, the search misses, and they create a new one. The record existed; the query did not match it.

A trailing space, a middle name, an abbreviation or a different spelling each defeat a search. Fixing the entry point starts with making the search forgiving.

Decide what makes a record unique

A business that has never answered this cannot deduplicate anything. Two people with the same name may be two people, and one company with two trading names is one company.

One field genuinely identifies the thing: an email, a phone number, an ABN, a licence number. That choice governs everything after it.

Merge, rather than delete

A duplicate usually holds something the original does not: a phone number, a note, a linked job. Deleting it loses that.

A merge keeps the older record, moves the useful fields across and relinks everything that pointed at the duplicate. Deleting first leaves orphaned jobs nobody can trace.

Relinking is the part people forget

Jobs, invoices, quotes and notes all point at a customer by its identifier. Removing a duplicate without moving those breaks history that looked fine yesterday.

That is the reason a deduplication run belongs in one considered pass rather than in idle moments across a month.

Fix the entry point or it happens again

A cleanup with no change to how records get created buys a few months of tidiness. The same forms produce the same duplicates.

A create form that searches as somebody types, and shows near matches before offering a new record, prevents most of them. Our guide to naming records covers making those matches findable in the first place.

Imports are the other main source

A spreadsheet imported into a table that already holds those customers doubles them in one action.

Deduplicating the file against the table before the import is far cheaper than merging afterwards. Our guide to importing a spreadsheet covers the order that avoids it.

Run the check on a schedule

Duplicates accumulate slowly, so nobody notices until a report looks wrong. A monthly look at near matches keeps the number small.

A short list of likely pairs, reviewed by somebody who knows the customers, is far more accurate than any automatic merge.

Some duplicates are real and should stay

Two branches of the same company, a person who is both a customer and a supplier, a franchise and its head office. Each is genuinely separate.

Merging those causes worse problems than leaving them, which is why a person reviews the list rather than a rule applying itself.

Recording the decision matters too. A pair somebody has already reviewed and kept apart should not reappear on next month's list as though nobody had looked.

Near matches are more common than exact ones

Two records differing by a middle initial or a suffix will never collide in an exact search, which is why they both exist.

A comparison ignoring case, punctuation and spacing finds far more of them, and it is the only way an automated list is worth reviewing.

What to change first

• The identifying field gets chosen before any merge begins
• Merging moves the fields across and relinks the records that pointed at it
• The create form searches as somebody types and shows near matches
• Imports get deduplicated against the table before they run
• A short list of likely pairs gets reviewed monthly by a person
• Genuinely separate records stay separate, by decision