Keep the stock of our wholesale warehouse and of the shop separately, and
transfer what the shop sells automatically.
One administration, two places where the stock sits: a wholesale warehouse with pallets going in and out, and a shop that sells by the bottle. In Exact Online that works fine with two warehouses. The hard part starts as soon as you want to see that stock outside Exact, and as soon as the shop's sales have to come off the right warehouse.
We built that for a drinks wholesaler with a shop. It is live. The client stays anonymous and there are no amounts, prices or margins here.
Fetching stock per warehouse, every quarter of an hour
For stock we use Exact's sync API, not the ordinary list of items. The path for stock positions gives one line per item per warehouse, with a timestamp attached.
That timestamp is the whole point. You only ask for what has changed since the highest timestamp you already have. On a quiet day that is one call per quarter of an hour, with an answer of almost nothing. On a full run the integration fetches everything, a thousand lines per page, and whatever is no longer in it disappears from the copy as well.
One thing we had to look for: with stock positions Exact gives the warehouse as an internal reference, not as a warehouse code. If you want to know whether something is in the wholesale warehouse or in the shop, you have to fetch the list of warehouses separately and join them. The sum across all warehouses is the item's total stock.
Booking the shop's sales back
The shop rings up sales on a till. Those sales have to come off the shop warehouse in Exact. Somebody did that by hand: go through the till's weekly export and create a warehouse transfer in Exact from wholesale to shop.
Now the app makes that proposal. The export gives a quantity per item, and that becomes one transfer with a line per item. Creating that transfer is the only action in our app that writes to Exact. Everything else we do around it is reading.
The proposal first shows what is going to happen: which lines are matched, to which item, by which route, and what could not be matched. Only when that is right does it go out. Every entry is a line in the app's log, with the number of lines and the number of units.
Matching on code, and otherwise on description
The till knows the Exact item code, in a column of its own. If it is there and it exists, you are done. That is the pleasant half.
For the rest we match on description, and that is where the work is:
Spellings differ. Capitals, double spaces and whether it says "70 cl" or "70cl" should not matter. So all descriptions go through the same normalisation before they are compared.
A code that no longer exists. A till can remember an old Exact code. The line then falls back on the description, and the app reports that the code was not found.
Two items with the same description. The app then picks the only one that has not expired. If more than one is active, it does not match, and the line ends up on the list of unmatched lines, with the candidates' codes next to it. A guess at the wrong item pollutes your stock for months.
What may never come along
Three kinds of lines always go out, and that matters more than it sounds:
Deposits. That is not stock moving from one warehouse to another. It sits in an item group of its own and we filter that group out.
Costs and services. Also their own item group, also out.
Till buttons and catch-all lines. A button like "Miscellaneous" is not an item. Those sit on a list in the settings, so the client can add to it without code being involved.
And one rule of arithmetic: lines for the same item are added up first. If the balance is zero or negative, for instance because more came back than was sold, the app transfers nothing. A negative quantity in a transfer is not a return but an entry in the wrong direction.
Preventing double entries
This is where manual work goes wrong, and automation even more easily. Transferring a week twice does not show up in today's stock, but it does in next month's stocktake.
The description of every transfer contains the period of the export. Before the app offers anything, it searches Exact for a transfer with that period in its description. If there is one, it refuses. That is one read call, and it has already paid for itself.
Counting with a list per warehouse
For the stocktake the app produces a count list per warehouse: item code, description, item group, unit, barcode and the stock according to Exact, with two empty columns for counted and difference. Sorted by item group, because that is how you walk through a warehouse.
That list is a file for the person counting, not an entry. Nothing goes to Exact. Whatever was counted, somebody enters deliberately.
What struck us once stock per warehouse was finally visible: negative stock in the shop warehouse. That is not a bookkeeping error but a signal. It means things were sold in the shop without being written off anywhere. Exactly the gap the weekly transfer now closes.
Common mistakes
Using the ordinary item list for stock per warehouse. That holds one stock figure per item. For stock per warehouse you need the stock positions.
Expecting the warehouse code on a stock position. You get an internal reference. Fetch the warehouses separately.
Transferring deposits along. They are not part of the product's stock. Filter on item group, not on the description.
Matching on description without normalisation. A double space or "70cl" against "70 cl" is enough to miss an item.
No check against double entries. Put the period in the description and search for it before you book.
What an Exact integration can do besides this is in beyond invoices and on connecting Exact Online. If you want something like this for your own administration, have a look at custom software.