Inventory updates using batch data capture device

So I have a potential client who is using retail software I was previously involved with, now wanting to see if I can convert them over to SambaPOS. However, for their inventory they use a barcode batch data capture device to update their inventory. I have a similar device in my office like this:

In the most simple usage, it will send via wireless USB connection a comma separated list of what has been scanned, for example:

(header row for illustration purpose only)

The device I have can do more elaborate things and format can be altered via the device, can allow capture of more data like price, batch #, etc. and can store a lookup table of products on the device as well, but these are besides what I need right now.

So is there a way this could be used with SambaPOS? I am thinking for managing inventory purchases, being able to either directly “paste” the output into a field, or pick it up from a CSV file, then update inventory based on matching barcode and the quantity would be either a + value to existing, or to replace the existing with the new value.

I haven’t done much with inventory so hoping some of those more experienced with using inventory can advise what might be possible and point me in the right direction of the best way to implement.

As it is retail, pretty much all inventory would be 1:1 relationship to products.

Hi Markjw

What you are proposing is very possible but did you get a chance to read a thread title “Inventory and Tasks” by QMcKay?

Barcodes are the biggest problem not being available in Inventory but having a 1-1 relationship you can just have a direct Recipe Link so you can carry the Barcode from Products (Menu Items). I am about to dig deeper into Inventory Management so not that skilled here yet.

@emre “proposes” delaying Q’s inventory enhancements in preference for V6 development :disappointed: So I guess you will need to work with what currently exists.

1 Like

PS: If you check up on my Inventory Bulk Purchasing threads where @QMcKay assisted you will find some handy SQL whereby you can create Inventory Documents to properly effect inventory.

1 Like

Yeah I read through that some time ago, but as you say with the 1:1 relationship I don’t directly need the barcode on inventory, however I think it would simplify things.

1 Like

Maybe you could convince Emre to give us a few API’s instead of writing SQL as that at least would be something. I did the Bulk Ordering thing but hammered my way through using string manipulation on a Custom Entity Field (Supplier).

The Stock Take - which is a biggy, I did not do so but somehow would need to access the “End of Day” counts for Stock I think its called.

Anyway the CSV stuff is most doable (see my latest File Monitor stuff).

At the moment I am working on a base install for retail, as we have many potential customers who need POS for retail. I know the stock take devices is a useful thing and we can also source the hardware cheaply (and like my initial post, I have a customer already using another POS software who has a stock take device and I want to convert them over to SambaPOS). So its part for that customer and part for a general setup. I’m planning to go back through all the retail tutorials and topics I’ve bookmarked here and implement them on my base install then I think I will be almost 95% there.

Thinking about usability of the stock take device, I think the best way to integrate with SambaPOS would be to have a screen they can go to and just “paste” the CSV data into a text field, since the scanner will just dump this all out on request via the wireless USB dongle, and it saves all hassle of using third party software (it comes with some rubbish software) to save CSV file, etc.

So I guess I will parse through the contents of that text field using a script and SQL to update inventory. I need to do more reading on inventory first though… :wink:

We can essentially create our own API’s with SQL DETAILS tag and the ability to call other scripts. I know its not as simple but I thought its worth mentioning if you cant wait for Emre.

Update [PhysicalInventory] column in [PeriodicConsumptionItems] table, when joined to [PeriodicConsumptions] table, with it joined to [WorkPeriods] table, based on max WP ID where the WP [StartDate] != [EndDate]

Something like this …

,wp.[Id] as [WPID]
,mi.[Name] as [MenuItem]
,mp.[Name] as [Portion]
,r.[Name] as [Recipe]
,[PhysicalInventory] -- the column we want to update
FROM [PeriodicConsumptionItems] pci
JOIN [PeriodicConsumptions] pc on pc.[Id] = pci.[PeriodicConsumptionId]
JOIN [WarehouseConsumptions] wc on wc.[Id] = pci.[WarehouseConsumptionId]
JOIN [Warehouses] wh on wh.[Id] = wc.[WarehouseId]
JOIN [WarehouseTypes] wt on wt.[Id] = wh.[WarehouseTypeId]
JOIN [WorkPeriods] wp on wp.[Id] = pc.[WorkPeriodId]
JOIN [RecipeItems] ri on ri.[InventoryItem_Id] = pci.[InventoryItemId]
JOIN [Recipes] r on r.[Id] = ri.[RecipeId]
JOIN [MenuItemPortions] mp on mp.[Id] = r.[Portion_Id]
JOIN [MenuItems] mi on mi.[Id] = mp.[MenuItemId]
AND wp.[Id] IN (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
AND wt.[Name] = 'Local Warehouses'
AND wh.[Name] = 'SHOP'
AND mi.[GroupCode] = 'Beverages Soda'
--AND mi.[Name] = 'Coke'
--AND mi.[Barcode] = '234523523452345'
AND mi.[ItemType] = 0
ORDER BY 4,5,6