Two records for every machine, and neither was wrong

The same machine, in two places

The fleet was represented twice. Each unit had a product record, which is what a line item on a deal points at, and an asset record on a custom object, which is what a service ticket points at. Same machine, same fleet number, two rows.

Proving they were duplicates rather than two genuinely different things took a direct comparison on specific units: matching fleet number, matching internal identifier, same physical machine. Confirmed on more than one unit before drawing the conclusion, because a single coincidence is not a data model.

The reflex here is consolidation, and it would have been wrong. Both records exist because two different systems need to attach different things to a machine — commercial history on one side, service history on the other. The problem was not that there were two. It was that nothing reliably tied them together.

The join key everybody was using was the wrong one

The obvious key was the stock number, which is human-readable and appears on both objects. It is also filthy. Real values in the fleet included the same number with a trailing underscore, the same number with a suffix, one with an embedded space, and several records literally named as unallocated with a counter appended.

Joining on that string produces a result that looks plausible and is quietly wrong for a subset of the fleet — the worst kind of wrong, because the report still renders.

There was a better key already present, on all three objects, and it was numeric and system-generated rather than typed by a person. Once identified, it turned an unreliable string match into an exact join and made the whole model tractable.

The number that looked duplicated but wasn't

Machine hours appeared in two places, which looked like the same redundancy problem in a different costume. It is not.

The asset carries a current meter reading and the date it was taken — that is lifetime state, the odometer of the machine. The line item carries a start reading, an end reading and hours used — that is a per-hire event, the trip meter.

They are different measurements that happen to share a unit, and collapsing them would destroy both. Pushing per-hire hours onto the asset would mean many concurrent hires overwriting each other. The asset needs its own meter because off-hire, standdown, workshop and breakdown tickets have no line item attached at all, and service intervals are calculated against lifetime hours.

Recognising that a number appearing twice is sometimes two numbers is most of the work in a model like this one.

What that unlocked, and what it exposed

With the model mapped, an association between line items and assets that already existed but had never been populated could finally be filled, and the client's own developer ran the catalogue refresh and backfill against it.

The mapping also surfaced problems that had been invisible. Roughly fourteen thousand nine hundred orphaned placeholder line items from a single historical import, carrying no deal, no product reference and a zero value. Service tickets carrying two different association labels to the same asset, which is a double-count waiting to happen in any report that groups by them. Meter readings that are obviously junk — one machine reporting a five-figure reading that is almost certainly kilometres from a different gauge, another reading as a repeated digit.

A nine-month activity export across calls, meetings, emails and 4,372 deals gave the client the first reliable baseline of what the fleet and the sales team were actually doing, rather than what fourteen accumulated dashboards claimed.

One decision that was deliberately not automated

The ticket creation form needed to guarantee that an asset gets attached. The flexible option was to allow any association label; the strict option was to require one specific label every time.

Strict won, on the client's call, for a reason worth recording: the ticket pipeline already distinguishes breakdowns from customer repairs and warranty jobs. Carrying that same distinction in the association label as well would have duplicated the meaning in two places, and two sources of the same truth diverge eventually.