We want to see per product what we paid over the past years. But what is
in the accounting is not always what was on the invoice.
For a drinks wholesaler with an off licence we built a purchase history: per product per supplier what was delivered and what it cost, over several years. That data sits in two places. On the supplier's invoices, which we read in, and in the purchase orders in the accounting, which somebody at the client entered.
Those two differ. Not by much, but enough to make a list feel unreliable. It is live. The client stays anonymous and there are no amounts, prices or margins here.
The question you should not ask
The first reflex is: which of the two is the truth? That is the wrong question.
The invoices say what the supplier delivered and invoiced. The purchase orders say what is in the administration. Those are two different facts and they are allowed to diverge. A return on a credit note was delivered and still went back. An item accidentally booked onto another item did genuinely arrive.
So we did not choose. There is a switch above the list: "according to the invoices" or "according to the purchase orders". Same structure, same period, two sources. The app picks no winner and says so.
Matching an invoice to an order
To compare anything you first need to know which order belongs to which invoice. There is no field for it. What there is: in the order's reference somebody types the invoice number, usually as "account / invoice number".
So we cut that reference up at anything that looks like a separator, keep the pieces of at least five characters, and turn those into a key with the supplier attached. Suppliers listed in the accounting under more than one name are taken together.
That works better than expected. In the check at the end of October we found 1,716 of the 1,764 invoices back as an order.
And then the lines
Having matched an invoice is not enough. You want to know per line which item it was, because that is what the whole list is about. We do that in three steps, and each step only takes what the previous one left:
1. On the supplier's number. If an item carries two numbers, in the form "old / new", the item counts for both.
2. On name. If every word of the name on the invoice appears in the description of exactly one remaining item, that is the match.
3. On one shared word plus exactly the same quantity. The same quantity on the same order is a strong signal.
In all three steps: if a number or a name matches more than one item, it does not match at all. An empty cell is better than a wrong one.
Step two exists for one reason, and it is the lesson we take from this most: the order line in the accounting carries the current supplier number, because it comes from today's item file. An invoice from three years ago carries the number from then. Suppliers renumber their range more often than you think, and then the number does not match while it is the same product.
What the check produced
Of the 200 most purchased products, 137 came out exactly on what is in the accounting, 41 were within 1 percent, 21 within 5 percent and one at 6 percent. Calculated from the other side, so of the 200 largest products according to the orders, 104 were within 1 percent and 182 within 5 percent of the invoices.
That is not a perfect score and it was not the goal either. The goal was knowing how big the difference is, so that you can trust a number on screen. That is why the check sits per product in the list: "matches Exact" if it is within a percent, and otherwise by how many percent it differs. Below the list the same thing appears for the top 200, 500 and 1,000.
Where the differences come from
Four causes explain nearly everything:
Returns on a credit note. Those rarely appear as an order in the accounting, so the order shows a delivery that partly went back later.
An invoice that is not an invoice. A scan without lines, or a compensation statement. There is an order, there is no readable invoice line.
Another item booked. Two variants of the same product sitting next to each other in the range. This is the most common one, and it is nobody's fault: while entering them they look alike.
Discount on its own line. On the invoice the discount is inside the price, in the order it is a separate line. Then the quantity is right and the unit price is not.
What we took from it technically
Two things we will always do this way from now on.
Fetch the order side clean. A purchase order contains lines that are not a product: transport, deposits, a waste fund, a surcharge, a levy, a discount item, a rounding line. Those have to go before you compare anything, otherwise your top hundred contains products nobody ever purchased.
Make both sources cover the same period. This looks small and it is not. Our invoices start on a different date per supplier, namely the oldest one we read in. Take everything on the order side and you are comparing three years of orders with eighteen months of invoices, which makes the accounting look structurally higher. So we start per supplier at that supplier's oldest invoice in the app.
Beyond that the order side is pleasantly simple: an order line is already in the accounting's unit, so you no longer have to reason about cases and bottles there. Fetching three years of orders with their lines took just over 700 calls, all read calls, and we keep that locally.
Common mistakes
Choosing which source is true. Put them side by side and say what each of the two means. The user knows better than you which one they need for which question.
Matching on the current supplier number only. Old invoices carry old numbers. You need a second and a third step.
Making an uncertain match anyway. Two candidates means no match. An empty cell costs you a question, a wrong match costs you trust in the whole list.
Including cost lines. Transport and deposits are not a product.
Comparing two sources over different periods. Then you are measuring your own import history instead of a difference.
Not putting a check on screen. A number without "does this match the accounting" is a number nobody dares to use.
What an integration on Exact Online can do is on connecting Exact Online. How we read those invoices is in reading PDF invoices to the cent. If you want something like this for your own purchasing, have a look at custom software.