Make sure our order portal shows each customer the price that ends up on
their invoice, straight from the accounting.
An order portal where every customer sees their own price is not a matter of a price field on the item. In a wholesale business there are agreements per customer, per item and sometimes per quantity, in a system of price lists. Those agreements live in the accounting, and that is where they should stay.
We built this for a drinks wholesaler with a shop. It is live. The client stays anonymous and there are no amounts, prices or margins here.
The hard part is not fetching it. The hard part is that it has to happen again every day, within the limits of the API, and that half a synchronisation must not produce a usable-looking answer.
Four lists, not one
A customer price does not sit in one list. You need four things for it:
The price lists themselves. Which lists exist, what they are called, in which currency.
The periods per list. A list can have a start and end date. Without the periods you do not know which version applies today.
The price lines. This is the bulk of the work: a line per list per item, with the quantity it applies to, the unit, the base price, a discount and the new price.
The customers with a price list. In the accounting the customer card points to a list. You do not have to fetch all customers, only the ones with a list. That saves a lot.
The first two and the last one are small enough to always fetch whole. The price lines are not.
Why the first time costs a thousand calls
On the first full synchronisation our integration fetched 78 price lists and 63,707 price lines. That cost 1,104 calls.
That comes down to the page size. Price lines arrive 60 at a time. So tens of thousands of lines means over a thousand rounds of "give me the next page". The items themselves are a separate store of around five hundred calls, and we refresh those overnight.
After that the picture is different. On an ordinary update round the integration only asks for the lines that have changed since last time. On a day when nobody touches the prices, that is one call with an empty answer.
One exception, and one gap
Two things we put firmly in the code.
The exception: if there are no price lines in the copy at all, the integration always fetches them in full, even on an update round. Otherwise, after an empty database, you get a copy holding only the lines that were changed since yesterday, and that looks like a working synchronisation while you have nothing.
The gap: filtering on "changed since when" leaves deleted lines in place. What is gone does not report itself. So a full round runs regularly, in our case when the last full round is more than a week old. Then whatever is no longer in the accounting disappears from the copy too.
That second point applies to any synchronisation based on modification dates, not just to prices. Update only on changes and you slowly build a copy with ghost lines in it.
Staying within the limits, politely
The API allows 60 calls a minute and a few thousand a day. With over a thousand calls in a row that stops being theory.
Three things we do:
Keep the pace. At least a little over a second between two calls. That is slower than allowed and keeps you off the per-minute ceiling.
Read the headers. Every answer tells you how many calls you have left, per minute and per day. That is a hard counter and not an estimate, so use it.
Stop in time. If the day counter enters the danger zone, the round stops with the status "aborted" instead of with an error halfway. If the minute counter is nearly used up, the integration waits a little, at most a bit over a minute.
That last one sounds like a detail and is not. A synchronisation that uses up all of the day's calls also flattens everything else that still has to talk to the accounting later that day: the stock, the orders, the invoices.
Half a round must not wreck anything
Every round is a record of its own, with its type, its start and its outcome. And it writes per page, not everything at the end in one go.
That means a round that breaks off halfway keeps the pages that already came in. The next round picks up where it stopped. An all-or-nothing synchronisation of tens of thousands of lines fails sooner or later, and then you have nothing.
One more thing we learned along the way: if the accounting refuses a list with an error about a field that does not exist, the integration tries once more without the optional fields, and reports that. Fields appear and disappear between administrations and versions. An integration that stops dead on that fails over a field you did not even need.
Common mistakes
Thinking a customer price sits on the item. You need the lists, the periods, the lines and the reference on the customer card.
Fetching all customers. Filter on customers that have a price list. The rest can stay.
Updating on modification date only. Deleted lines then stay forever. Run a full round periodically.
Updating onto an empty copy. Fetch everything as long as there is nothing there, or an empty copy will look filled.
Estimating the limits instead of reading them. The answers tell you exactly how many calls are left. Stop before you reach zero, because there is more that has to talk to the accounting today.
How we keep stock per warehouse up to date with the same approach is in several warehouses in Exact Online. What the documentation otherwise leaves out is in the Exact Online API, and what else you can build on it is in beyond invoices. If you want something like this for your own administration, have a look at custom software.