One step closer to ON THE FLY Stock

Dear @emre, please correct me if I am wrong… (as you know this is an excercise that I will finish no matter what, LOL)…

I created a View that has two things…

  1. Inventory items with last know (last work period) quantities
  2. Last purchases

so, I stored that view… nice piece of SQL…

Now to get the real on line in the fly at the moment stock I just need to substract orders, add canceled orders and add voided orders…

is that correct???

I know this has a mayor drawback at the moment and that is RECEIPTS… I am working on it, but at the moment I am really interested in Inventory items specially Beverages (one to one). I have some issues there when whe have some soccer matches, we sell 80 bottles in 30 minutes… and need to have a very strict control…

This is the VIEW CODE:

SELECT        dbo.PeriodicConsumptionItems.InventoryItemName, CASE WHEN [BaseUnit] = [TransactionUnit] THEN CONVERT(INT, ISNULL([PhysicalInventory], 
                         ([InStock] + [Added] - [Removed] - [Consumption])) + ISNULL
                             ((SELECT        TOP 1 [Quantity]
                                 FROM            [InventoryTransactions]
                                 WHERE        [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10), GETDATE(), 121)
                                 ORDER BY [Id] DESC), 0)) ELSE CONVERT(INT, (CONVERT(numeric(6, 2), ISNULL([PhysicalInventory], ([InStock] + [Added] - [Removed] - [Consumption])) 
                         + ISNULL
                             ((SELECT        TOP 1 [Quantity]
                                 FROM            [InventoryTransactions]
                                 WHERE        [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10), GETDATE(), 121)
                                 ORDER BY [Id] DESC), 0)) * [UnitMultiplier])) END AS Stockfisico, dbo.InventoryItems.BaseUnit AS Unidad, FORMAT(CONVERT(numeric(6, 2), 
                         dbo.PeriodicConsumptionItems.Cost), 'C') AS CostoUnit, FORMAT(CONVERT(numeric(6, 2), 
                         dbo.PeriodicConsumptionItems.Cost * (ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory, 
                         dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption)
                          + ISNULL
                             ((SELECT        TOP (1) Quantity
                                 FROM            dbo.InventoryTransactions
                                 WHERE        (InventoryItem_Id = dbo.PeriodicConsumptionItems.InventoryItemId) AND (Date >= CONVERT(varchar(10), GETDATE(), 121))
                                 ORDER BY Id DESC), 0))), 'C') AS CostoTOT
FROM            dbo.PeriodicConsumptionItems LEFT OUTER JOIN
                         dbo.InventoryItems ON dbo.PeriodicConsumptionItems.InventoryItemId = dbo.InventoryItems.Id
WHERE        (dbo.PeriodicConsumptionItems.PeriodicConsumptionId =
                             (SELECT        TOP (1) Id
                               FROM            dbo.PeriodicConsumptions
                               ORDER BY Id DESC)) AND (CONVERT(numeric(6, 2), ISNULL(dbo.PeriodicConsumptionItems.PhysicalInventory, 
                         dbo.PeriodicConsumptionItems.InStock + dbo.PeriodicConsumptionItems.Added - dbo.PeriodicConsumptionItems.Removed - dbo.PeriodicConsumptionItems.Consumption))
                          >= 0.05)

Thanks a lot, and sorry to bother you so much!!!


That is impressive… I have to ask though why don’t you just use a warehouse and use the warehouse screen? Just seems like a lot of work to do something that you can already see in the warehouse screen. But I do prefer reports myself vs that screen because I would like to view other things at same time so I could see that view of it.

1 Like

I am using a warehouse… but all this started before changing the units… at that moment warehouse showed me really strange numbers… like 3.48 of a box… so I needed a stock report… now I dont need it anymore… but still I like the excercise… makes think a little, but I also know that I bother too much sometimes…



When I was young I had three passions… girls, girls, girsl, LOL… no, engines, computers and sports… I went to college and made my degree in systems, so i am a systems engenieer, then I studied to become a mechanic, licenced mechanich, and then I started to run with my on car… sports… now I eat pizza all day… LOL…

1 Like

Its no bother heck I learn something every time you post… i have starred almost all of your custom report threads. People can seriously learn from it. I just like to bring up the counter to pick your brain :stuck_out_tongue:

Little off topic but you really enjoy custom reports and showing the community how to use them i can feel that passion in you it really sparks your interest. I love to dig in and see how things work. I am currently redoing my entire flow right now because I have learned the power of States lol. I plan to share what I find out when I am done with it. I would love to really demonstrate States and advanced uses of them.

Heck an exercise when i get some free time might be to delete everything, states, automation, rules, templates… and start a custom flow system from scratch. I am messing with Group Codes and Ticket States right now doing some things I didn’t think was possible lol

Getting the inventory used in orders is not going to be easy if you ask me, at least if you have any order tags with products linked to it. SQL server doesn’t have support for JSON, and linking tables through a part of the value of a field (or even more than one part as you can have more than one tag per order) is not going to be easy to do within your SQL code. You may have to do it with some separate scripting (and intermediary tables) and not just with SQL code…

You can look in the documentation for the format of the OrderTags field:

1 Like

We were discussing this earlier in this thread:

I still haven’t found a way to do it in SQL, but you can try (it sure isn’t easy)…

Here is a good article on parsing JSON with SQL. The code is provided and is presented as a function that you would create in the DB to use at any time. At first glance, it’s fairly complex, but it covers many scenarios… I’m not sure we would need something this complex for the JSON contained in the SambaPOS database. You can probably glean some methodology here and simplify it quite a bit for our purposes…


Thanks for the tip @QMcKay. I had actually already seen this code before, but I wasn’t sure how to use it.
I just had another shot at it though, and I managed to come up with an SQL query which uses the parseJSON function to create a new table Order_ordertags with one record per order tag linked to an order.
This could be a good basis to report inventory on the fly (although this method might be a bit too laborious and work too slow on large databases, I’m not sure).

First execute the script from the link above to install the function in your database:
create function (3.8 KB)
(Make sure to change the first line to “Use SambaPOS4” for V4)

Then use my SQL to create and fill the Order_ordertags table:
create ordertags (1.1 KB)
(Also change the first line to “Use SambaPOS4” for V4)

The table looks like this:

I’m not sure how fast/slow this would work on a large orders table (mine is almost empty), so please let me know how it works on a larger database.


By the way, @emre, I noticed that the FT value pair is not created in the OrderTags field for free tags. I did not check in V4 yet, but it might be the same there…
I also don’t know what the TO (Tag note) is for, I can’t immediately find a way to fill that in, and also the OK (order key) pair (is this for ordering the tags on the ticket?) and AP (Add Price, is this the same as CalculatePrice for the order itself?).