Most pricing complaints get diagnosed as UI problems: the screen is slow, approvals are confusing, nobody can find last month's number. So someone proposes a nicer pricing module. In a last-mile fuel distribution engagement the actual defect was one line of SQL semantics. The ERP did an UPDATE on the price field, so every change overwrote the previous value in place. The screen looked fine. The data underneath had no memory.
For a commodity business that is a liability, not a cosmetic gap. Fuel prices move with the market several times a week, across cost, transfer, and selling price, per product and per customer tier. Store only the current value and you cannot answer the two questions that matter when a margin dispute or an audit lands: what was the price on a given past date, and who approved the change. One overwrite destroys both the defensibility and the analytics, and nobody notices until they need history that no longer exists.
The overwrite failure mode, precisely
Off-the-shelf ERPs, including Dynamics 365 Business Central, model a price as a mutable attribute on an item or price-list line. That is correct for order entry, which only needs the price that applies right now. It is wrong for a business whose margin is the spread between three independently moving prices that must be reconstructable after the fact. The damage compounds in three invisible ways:
- No point-in-time truth. You cannot price-as-of a past date, so you cannot recompute the margin you earned on a delivery booked three weeks ago, or reconcile a disputed invoiced rate.
- No defensibility. In a regulated commodity market, 'trust me, that was the price' is not an answer. With no change record there is no evidence of who set what, when, and under whose approval.
- No analytics. Elasticity, margin erosion, and the lag between a cost move and a selling-price move all need the time series. Overwrite-in-place leaves one snapshot and nothing to analyze.
Put the history beside the ERP, not inside it
Do not fix this by customizing core Business Central tables. That couples you to the vendor's upgrade cycle and turns every patch into a regression hunt. The pattern we deployed is a standalone historical pricing table in SQL Server alongside Business Central. The ERP stays the operational system of record for the current price; the table is the append-only ledger of every change. It has no UPDATE path and no DELETE path. Each row carries the keys (product, customer or tier, and which price: cost, transfer, or selling), the value with currency and unit, effective-from and effective-to bounds, the full approval provenance, and a correlation key back to the source change record.
Closing the prior row's effective-to when a new row opens turns 'what was the selling price for product X to customer Y on a given date' into one indexed range query. No reconstruction, no guesswork. The history is the data structure, not something you derive after the fact.
Append-only is not a logging nicety bolted on after the build. It is the data structure that makes point-in-time pricing a query instead of an archaeology project.
Governance in the workflow, integration kept thin
An immutable ledger records what happened; it does not stop a bad price. We moved changes out of direct edits into a two-level approval flow in Power Apps on the same SQL Server store. The decision rule is deliberately simple, because complicated approval matrices are the ones people route around: an analyst proposes a draft change with a reason; a line manager approves or rejects it against the current price and cost basis; only a second sign-off from the commercial or finance owner makes it effective, at which point the history table opens a new row, closes the prior one, and the value is pushed to Business Central. Every step, including rejections and resubmissions, is written to the revision history, and role-based access stops an analyst self-approving or an approver editing mid-flight.
Keep the data movement thin. Business Central is written to only at the single moment a price becomes effective, through its web services, with one targeted update rather than a scheduled bulk sync that creates two stores drifting between runs. Two pitfalls worth flagging: make the effective-to update transactional with the new-row insert, or a fast double change leaves an overlapping window that breaks point-in-time queries; and never grant UPDATE on the history table 'just for a quick fix', because the moment that is possible the audit trail is no longer defensible.
What this changes
The outcome is full pricing auditability: every price ever charged, for every product and customer, reconstructable as of any date with its approval chain attached. A regulator, auditor, or disputing customer gets a complete answer with provenance instead of a snapshot and an apology. The structured flow replaced ad hoc email sign-offs, writing to the ERP once per change removed a class of drift bugs, and a real price time series makes margin erosion and cost-to-price lag measurable. None of it required replacing the ERP or building a prettier screen. Treat pricing as what it is in a commodity business, a governed append-only time series that the ERP reads the latest value from, and the compliance, speed, and analytics follow.
