We have already covered connecting your own e-shop to ABRA Flexi accounting in a separate article. This time, we will take a closer look at how to transfer the current inventory status from ABRA Flexi to your e-shop. If you record goods receipts and issues only in your accounting software and your e-shop does not handle them, this is the best way to get a clear picture of how much of each product can be sold in the e-shop. So how do you correctly retrieve the inventory status?
There are several options. Most e-shops have a dedicated warehouse set up in ABRA Flexi — usually the main warehouse. If the company also has brick-and-mortar stores, each of them has its own warehouse. Stock transfers to and from the stores are then handled via transfer documents from the e-shop warehouse. After every movement (receipt or issue), the e-shop must be informed that the stock level of a particular item has decreased or increased.
Inventory status as of a date
The first option is to use the inventory status record as of a specific date. While accurate, this is a rather inefficient approach. It can calculate the exact stock level for a specific day, but that level of precision is usually not necessary. Unfortunately, compared to other methods, it is slow. Even if you use a custom detail limited to only the fields you need, retrieving real results takes a very long time.
http://localhost:5434/c/muj_eshop/stav-skladu-k-datu.json?sklad=4&detail=custom:cenik,stavMJ&limit=0
This first attempt to retrieve the inventory status took an average of 2 minutes and 20 seconds on 30,000 records. It is therefore far from an optimal solution. However, it returned exactly what you need for an e-shop — records containing the price list item and the inventory status. The total size of the JSON file generated by Flexi is 6 MB. Pagination can be enabled, but it does not help much in this case.
Also note that with each additional month of the year, the number of movements will grow and the total time needed to retrieve the data will get worse.
Stock cards
Another way to retrieve the current inventory status is to use stock cards for the current accounting period. This involves a pair of queries. The first query retrieves the ID of the current accounting period. However, this information does not change throughout the entire accounting period (the whole year) and therefore does not need to be fetched with every inventory status query.
The second query looks like this:
http://localhost:5434/c/muj_eshop/skladova-karta/(ucetObdobi = 6 and sklad = 4).json?detail=custom:cenik,stavMJ&limit=0&no-ext-ids=true
The query takes around 35 seconds and also generates a 6 MB JSON file. Pagination can be enabled here as well, but mainly just for the purpose of processing smaller files.
Note: If the "no-ext-ids=true" parameter is not used, the time increases to up to three minutes!
Stock cards with a balance
The number of stock cards retrieved can be further reduced. There is no need to load cards with a zero or negative balance. Most e-shops do not have all products in stock, and a large portion of the assortment is available to order. It is therefore sufficient to retrieve only the stock cards that have a positive balance. For all products that do not appear in the results, we know they are out of stock and can set the inventory level to 0.
http://localhost:5434/c/muj_eshop/skladova-karta/(ucetObdobi = 6 and sklad = 4 and stavMJ > 0).json?detail=custom:cenik,stavMJ&limit=0&no-ext-ids=true
The resulting JSON, just under 1 MB in size, is generated in 5 seconds. That is already a significant difference compared to the original several minutes.
Custom query — "everything" variant
If even five seconds is too long, there is one more option: creating a custom query in Flexi, which can be even faster.
The query will look like this:
select c.kod, k.stavMj from skarty k left join ccenik c on c.idcenik = k.idcenik where k.iducetobdobi = <> and k.idskladu = <>
This query can of course then be executed via the REST API to retrieve the results.
http://localhost:5434/c/muj_eshop/uzivatelsky-dotaz/35/call.json?iducetniobdobi=6&idskladu=4&limit=0
With a simple custom query, we are able to retrieve the complete inventory status for all records in the price list in 5 seconds, regardless of their status (zero or non-zero). An additional advantage is that the resulting JSON is only 1.1 MB in size.
Custom query — "non-zero only" variant
What if we slightly modify the query and return only non-zero stock levels?
select c.kod, k.stavMj from skarty k left join ccenik c on c.idcenik = k.idcenik where k.iducetobdobi = <> and k.idskladu = <> and stavMJ > 0.0
The call is the same as in the previous case.
http://localhost:5434/c/muj_eshop/uzivatelsky-dotaz/35/call.json?iducetniobdobi=6&idskladu=4&limit=0
And the time? Around 700 ms (0.7 s).
The result contains all the necessary information and the generated JSON is 145 kB in size. Such a small file is quite easy and fast to transfer and process. This means the memory footprint on the e-shop side is also minimal.
Summary
If you own a license that includes Custom Queries, do not hesitate to use them. They allow you to maximize search response speed and minimize result sizes. If you do not have such a license, it is still possible to achieve reasonably good results using the standard stock card API. However, consider whether custom queries could make your work easier and more efficient.
Note:
All measurements were performed on localhost using the same company data throughout. No new records were added to the company during testing. The price list contained 30,000 records. The total number of stock cards for the accounting period in question was 100,000 (500,000 in total), and 5,000 cards had a non-zero balance.