From Order to Stock (1 Viewer)

[Cowlers]

Hello all,

I've previously posted about my database which is being used to manage stock of Aluminium Extrusions. I can now successfully raise a purchase order with an extruder using the system and can also print this order out using a report. The next stage is what happens when the material arrives. I want to manage stocks using a "Stock Transactions" table as I believe this will make it as simple as possible for me to track the actual stock figures without having to consult a multitude of different tables (This is complicated because I have three different warehouses to which my material could be delivered). My problem is this:

When a user generates an order on the system I am happy to use the tblExtrusionOrderLines to generate a "projected" stock figure but when this actually comes into stock I need a trigger to create the relevant records in the stock transactions table. Again this is something I think I will be able to do, my order has a status field so I can run some code when the status becomes "Complete" or "Delivered" etc. Or I could have a button to change the status to complete or delivered and run additional code at this point. My concern is what happens if a user changes an order back from complete to "Open" and makes some changes to the order lines when a stock transaction record already exists for that order? Is there an easy way for me to remove all related records if someone changes back from Complete to Open and then I can reapply when it becomes complete again? Alternatively if there is a fundamentally better way of doing this please let me know!

Many thanks,

Dan

 

[spikepl]

what happens if a user changes an order back from complete to "Open" and makes some changes to the order lines

Why does your DB workflow allow this? Like in any other recording of transactions, there comes a time when the thing is done. If a mistake was made, then you could provide means for an adjustment, labeled as such, to compensate for the mistake. Otherwise, no change. E.g. if you issued an invoice, you cannot just fiddle the lines of the invoice afterwards, but have to issue a credit note.

 

[MarkK]

There is a fundamentally better way: always calculate stock quantities. The basic math is . . .

Stock = StockIN - StockOUT

You should have an order system with a product table, and anything that moves goes on an order. If you move stuff between warehouses, make up an order, fill it, receive it.

The reason you must calculate stock is that it varies over time, and you need to be able to make predictions based on orders, so you need to know that when the order arrives on Tuesday, you won't have room for it. You need to be able to calculate stock quantities for dates other than today, and that is impossible if you store the number.

Makes sense?

 

[jdraw]

If you want to read more about Inventory Control concepts and sample code, see this link by Allen Brown

 

[Cowlers]

Thank you all,

spikepl - You are absolutely correct, I should be locking out an order once it is completed or cancelling it before it is completed. This is the approach I will take.

Lagbolt - My problem here is that I have to combine several ordering systems into one as I have two manufacturing processes as well as a raw material order and a customer order so my thought was to query on these orders to find pending stock and to sum the stock transactions to find todays stock. I do like the idea of being able to "project" stock forward though so I will look into this, thank you!

jdraw - I shall read up, thank you!

Best regards,

Dan