by Serguey Shinder
In March 2024 a plumbing contractor in Leeds returned a pallet of brass fittings with a note that said, in capitals, wrong again. He had ordered fifteen millimetre compression elbows by our product code three times in a month, and three times he had received twenty two millimetre isolating valves. The supplier had sent exactly what our purchase orders asked for.
The supplier's own codes for those two products were 00731 and 731. The year before, they had bought a smaller manufacturer and merged catalogues, and their older range kept its zero padded codes while the acquired range kept plain numbers. To the supplier those were two different products with two different names, which happened to be written in digits.
Our system kept the supplier's part number in a whole number column. It dated from 2009, when our first connected suppliers used numeric codes, and somebody had sensibly matched the data in front of them. When the merged price file arrived, our import read 00731 as the number seven hundred and thirty one, which as a number is exactly what it is. A few hundred rows later it read 731, found a product with that code already loaded, and updated it. Two hundred and twelve pairs collapsed this way. Each pair became one product in our catalogue, with the description of whichever row was read first and the supplier code and price of whichever was read second.
The buyers had met a version of this for years. Supplier files opened in a spreadsheet lose their leading zeros too, and people had long retyped codes by hand with an apostrophe in front.
What I had wrong was the word number. A part number is a name that happens to be made of digits. Nobody adds two of them together or asks which is larger. The only question anyone ever asks of them is whether two are the same, and storing them as numbers had quietly changed the answer.
We changed the column to text and reloaded every supplier's codes from their original files, character for character. The import now compares what it stores with what it read, and stops the whole file if any code would change on the way in or if two different codes would land on the same product. Supplier files are read directly by the import and never pass through a spreadsheet first. And any new field that holds an identifier, a postcode, a barcode, an account reference, is text unless somebody can name a sum they intend to do with it.
The contractor had his elbows the next morning. We also found about nine thousand pounds of the wrong valves in three warehouses, all ordered by our own system in good faith.
If you would never add two of them together, they are not numbers, however many digits they have.
– Serguey Asael Shinder
Leave a Reply