Flow
What happens to a row the live lookup can't place
Every branch ends in an outcome with its own count.
1Found with stock on hand
Plan the tag change as usual.
2Found with zero on hand
Sold out. Leave tags alone, count it separately, keep it out of the executable count.
3Exact ID in the dated snapshot or the history
Sold out. Force zero on hand and move the old quantity aside. History rows also get their tags marked unknown.
4Only a product name or code matches
Leave it unresolved. This is the case I still owe a test for.
5No trace anywhere
Unresolved, and worth a person's time.
Packages that had just sold out were sitting in the same pile as real typos, because the discount-tag preview put every row it couldn't find into one pile.
A spreadsheet of slow movers
Someone in the stores exports a spreadsheet of slow-moving stock, marks which packages should get a discount tag or lose one, and uploads it to the ordering system. The planner looks up each package in the store's live inventory and shows the changes it would make. In live mode it only reads.
The live lookup lists what's on hand. A package that sold after the export isn't there, and the planner called that unresolved, the same as a typo. That pile is meant to be the list a person checks, so filling it with fine rows wrecks it.
I fixed it in three passes on the same day.
Zero on hand, the report's snapshot, then history
First, a package found with zero on hand is now its own outcome, sold out. Its tags stay as they are, it gets its own count, and it's kept out of the unresolved and executable counts. The live preview also shows the spreadsheet price next to the current price, just to look at.
Second, a fully sold package can drop out of the live read altogether. The spreadsheet is from one day, so the planner now also asks for the inventory snapshot from that day. It reads the date from the file name, and the request can override it. Snapshot rows say zero on hand and keep their old count in a separate field.
Third, for anything still missing, the planner reads the store's transaction history, from a few months before the report date up to today, and adds those events to the same lookup. Each one is marked zero on hand, with its own quantity moved aside and its current tags marked unknown.
Each history event becomes a row with zero on hand, its own quantity moved aside and its tags marked unknown.
What a sold-out label can hide
Sold out only means "not on hand, but something remembers this ID." It can't tell a sale from a move. The date parser assumes month-first, so a day-first file name would fetch the wrong day with no warning. If the history read fails, I expect the whole preview to fail with it. And the history window is fixed, so very old sales stay unresolved.
Match the package, not the product
The part I'd copy: match history only on the exact package ID. Product names and codes are shared by many packages, so matching on them would sometimes pick the wrong one. If a tool like this ever writes, that's how a tag lands on the wrong package.