Flow
Lookup order for a live sync row
1Register ID
Look up the product by the register system's own ID first.
2SKU
If that misses, try the SKU. If it matches and the product has no register ID yet, save the ID for next time.
3My own ID
As the last keyed step, try my own ID for the product.
4No name guessing
Names are not part of the live lookup at all.
One product in the ordering system showed more than twice the stock the stores' register had. Same item, same store. Somebody checked by hand, and my number was wrong. Another product showed zero while it sat on shelves. After that, I couldn't trust any count.
How the sync works
A gateway pulls stock counts from the register system for each store. A row processor decides which of my products and stores each row belongs to. Then an adder sums the counts across rooms, like the back room and the sales floor, and saves one total.
The adder trusts whatever it gets. A wrong row in a total looks just like a right one.
Four products, one flavor name
The row processor tried to match by product ID, then by SKU, then by name. The ID was rarely there. The SKU often missed because the two systems wrote it differently. I suspect lots of rows were matched by name alone.
The name check counted a match if one name sat inside another. My product was named after a flavor. A product from another brand started with that same flavor name. So did a different flavor in two sizes. All of them landed on my product, and the adder added them up. The real item had well under half of that total.
The bad check is simple.
It matches any catalog name found inside the row's name, so lemon grabs lemonade.
The same matcher also lost rows. Sales rows with an ID where the name should be matched nothing and were dropped, as below. I'd bet that's also how the other product hit zero. So it made up stock and lost stock. I don't think tighter or looser rules would fix that. That's my guess, not a test.
Using the key that was already there
The register's rows already had its own IDs for the product and the store. I added two columns to hold them, one on products and one on stores. They can be empty, so old records stay valid. They must be unique, so two of my records can't claim the same item.
I left the spreadsheet upload path alone, since it still needs looser matching. Live rows got their own path. The product is found by its register ID, then SKU, then my own ID. The store is found by its register ID. No names. The gateway also stamps each row with its store.
If a row matches by a backup step and the product has no register ID yet, the sync saves the ID for next time. That's handy. It also means one bad match could stick for good.
Sales was wrong too
Sales were still off. One product showed about a quarter of its real sales. Many sales rows had an ID where the name should be, so the name check dropped them. Sales got the same store stamp and the same live path. A low sales number just looks like a slow seller, so this one scares me more.
The server ran old code
This cost more time than the bug. I edited on my machine and built on the server, but the copy step got skipped or went to the wrong folder. So the server kept building the old code, and my changes looked like they failed. Now I copy each file, check that both copies match, and then build.
One vendor, two names
One vendor's products stayed too high. The script that links them kept failing, because I store a short vendor name and the register wanted the full one. So I pulled one store's whole stock list, kept rows with that vendor's tag in the name, and matched those. Most of them linked. After the next sync, the product that started all this matched the register exactly.