Inventory Purchase Improvements

Just so it doesn’t get lost, I’ll re-post this previous request…


@emre, while we’re at it, it would be nice to have what I call Package Types. Then when you define an Inventory Item, you can use a pick-list for defined Package Types for each Unit…

gladly that happend only to beers and sodas, so no receipts with those!!!
just inventoy items…

so thats why i am writing a script to correct data…

G.

@emre, I noticed that in SambaPOS, you can’t make an Inventory (Purchase) Transaction when there is no WorkPeriod open, so in my PHP I make a check to ensure a WorkPeriod is open. I assume this should this be the case whether Purchasing or simply Moving stock from one Warehouse to another? It is a Transaction after-all, so I expect the answer to be Yes.

Is the reason for this related to End of day Records (Periodic Consumptions) in that the Close of a WorkPeriod updates Stock counts based on the the WorkPeriod Start and End Date?

Are the Periodic Consumption tables the only way to determine how much product is in-stock for each item, or is there another way to determine this (i.e. doing column aggregates on the InventoryTransactions table)?

tl;dr;. Yes Periodic Consumption table is the only way to determine inventory levels.

Periodic Consumptions table is the result of the inventory calculation based on sales, products mapped to order tags, order line’s inventory updating configuration and recipes. It also contains manually made physical inventory fixes. For example think of that situation. Inventory shows 10KG meat but when you weigh it you saw you have 8KG meat. The reason of that might vary however you need to fix your inventory. We can fix it with an additional transaction but we also have to fix cost of all portions that uses meat. If we consumed 10 kg meat we need to calculate cost as if we consumed 12 kg meat. For example if hamburger uses 50 GR meat we need to calculate hamburger cost as we consumed 60 GR meat. That’s why we have two “Predicted Cost” and “Physical Cost” values. I know I couldn’t feature it enough yet but this will be one of the most important features of SambaPOS. We assume a “recipe” is just a expection. When you say I use 50 gr meat in hamburger you just expect to use 50 gr meat. Cost calculation shouldn’t rely on that configuration. It should rely on actual consumption and we should track the difference between our goal values and the real values. I mean there is a reason behind that data storing structure and things I don’t allow. I hope to find more time soon to complete my plans.

3 Likes

@emre, I have these working in PHP/SQL:

  • Inventory Purchase
  • Inventory Movement/Transfer (move stock from one Warehouse to another)
  • Show stock counts of all Items in each Warehouse (BaseUnit &
    TransactionUnit)

Now I want to be able to update Physical count using SQL. What are the requirements for this? Do I need to insert rows to any tables, or do I simply execute an UPDATE statement to [PeriodicConsumptionItems].[PhysicalInventory] for that particular Item/Warehouse, and ensure I am updating only records for the Last Closed WorkPeriod?

  • When work period ends periodic consumption items generates.
  • You’ll update generated items.
  • When work period starts SambaPOS will calculate stuff from entered values.

fyi: you shouldn’t update physical inventory when a work period is open.

1 Like

Doesn’t appear to be fixed in 4.1.57 … My first 2 Warehouses are SHOP, then BODEGA. It is still displaying Warehouses by order of creation, not by their [SortOrder]. This is the case for both the Warehouses Screen and the End of day Records display.

I know. But that’s not what I’m referring to. See here…

Thank you! Looks great!

1 Like

Re-post in hopes of jogging memory of @emre to implement such features.

Allow for users to create as many Units as they want (globally), and they must use them from the base-upwards. If the 3rd or 4th or 5th Units are unused for the Item, then they are ignored.

At the very least, it would be nice to have a 3rd Unit. (and associated Multiplier & Default Cost)

@emre, while we’re at it, it would be nice to have what I call Package Types. Then when you define an Inventory Item, you can use a pick-list for defined Package Types for each Unit…

@QMcKay I’m aware of this but sorry for not being able to implement it immediately. Like I’ve said before this is something related with infrastructural design of inventory system. So before diving into that let me finish some immediate tasks.

Poet and didn’t know it. :stuck_out_tongue_winking_eye:

Thanks @emre, just didn’t want it to get lost in the shuffle. I agree, the Inventory System needs some work, and I don’t expect it to come too soon, that’s why I built the PHP system (which I will continue to improve unless it becomes moot).

1 Like

Just to give you something to think about when you get to implementing… Let me give you an example of Croissants:

Unit 1: Piece  Quantity: 8  Cost:  1 (calculated automatically)
Unit 2: Tray   Quantity: 6  Cost:  8 (calculated automatically)
Unit 3: Case   Quantity: 1  Cost: 48 (value entered manually)

This means there are 8 Pieces in a Tray, and 6 Trays in a Case.

I only need to enter the Cost of the Case (48) and the other costs are calculated automatically when I leave them blank. Costs are based on the Quantity of the Unit (Transaction Multiplier):

Tray Cost =  Case Cost / Tray Quantity = 48 / 6 = 8

Piece Cost = Case Cost / Tray Quantity / Piece Quantity = 48 / 6 / 8 = 1

I like the way the above works. Obviously you should be able to override the Cost per Unit, but the Automatic Calculation is nice and saves work when entering Inventory Items.

So I think I have to tell in advance what is in my mind.

  • We’ll have a base unit.
  • We’ll have many transaction units (you can think it as packaging)
  • We’ll define how many items per transaction unit contains individually. It wont be cumulative like Piece > Tray > Case. Piece will be base unit. Tray & Case will be transaction units. You’ll define Tray has 8 Pieces and Case has 48 pieces.
  • We’ll always calculate cost for base unit because you’ll configure recipes by base unit. If you purchase 10 case we’ll divide price with 480 and take it as base unit cost. If you make multiple purchases from different units it will be the average cost as packaging generally changes base cost.
  • I’ll probably bring last transaction price to transaction if you leave cost values empty. I won’t calculate it from other cost values.
1 Like

@emre, thanks for letting me know your approach.

I realize that method is how it is currently done. Since you are going to re-visit the infrastructure, I like my method better, because it takes the manual math out of it.

Working in a cumulative fashion as I am suggesting doesn’t require any math on the part of the user… the system does it for you, forward and backward…

Base Unit Cost is still determined by Case Cost - that doesn’t change. I simply don’t want to have to figure out (8 x 6 = 48) in a Case; I want the system to do it for me by taking the Quantity of Pieces (8) and multiplying it by the Quantity of Trays (6).

Consider this, for a Case of Wine.
1 Case
12 Bottles
24 Ounces per Bottle (6 ounces poured to a glass x4)

I don’t want to need to figure out there are 288 Ounces in a Case. That’s why I want a Quantity for Base Unit.

Unit 1 (base): Ounce,  Quantity: 24
Unit 2       : Bottle, Quantity: 12
Unit 3       : Case,   Quantity: (default 1, x number of cases ordered)
Unit 3 should have the Cost as well, per 1 case

Now the system decides that there are 12x24=288 ounces in a Case - honestly, I don’t care how many ounces are in the case - this is what I’m getting at, or call me lazy.

I also don’t want to calculate the Base Unit Cost… Let the system do it for me, i.e. if a Case cost $100, then all the rest is done for me automatically (consider too that I sell by the Bottle, so I need to know the cost of a Bottle, not just a 6 oz glass)… I want to avoid having to make these calculations when I’m entering Inventory.

100/12 = 8.33 per bottle
100/12/24 = 0.347 per ounce
Recipe uses 6 ounces per glass = 6 * 0.347 = 2.08 (cost per glass)

This should also allow me to create a recipe for Bottle of Wine.

No matter the implementation, I look forward to it, and I’ll make it work for me, since I really need a 3rd Unit right now.

@QMcKay wine is a great example. Some wineries offers slightly bigger version of retail bottles for restaurants and they can purchase both.

I know this is not the case but found it while searching for an example :slight_smile:

Still waiting for more Units, and Quantities… :wink:

On another Topic, @emre, is there any Indexes I can add to the DB to improve the performance of Work Period Close? Because since I started tracking Inventory, Close Work Period takes 30+ seconds to complete…

Can you do something for me? Please create a backup before ending the work period and send me the zip of the backup file.

you can have two backups…

I thought that my pc was getting slower and slower… closing a samll work periods takes 15 to 30 secs… closing a saturday may take up to one minute…

Its not a problem, but if we can improve that it would be great!!!

G.

@gerlandog did you noticed an improvement for work period end?

@QMcKay. I’ve added Hidden setting to Warehouse Type's. So these warehouses won’t appear on Warehouse module but you’ll be able to see them on End of Day records.

1 Like

I definitely noticed a massive improvement - it’s nearly instantaneous now - I mentioned this in another Topic. @gerlandog, how about you?

Thanks for that - looks good!

I have not ahd a busy saturday so I cannot tell for sure… I am intalling 4.1.70 now…

Let you know as soon as I try it!!!

Thanks!!!

G.