Solved Preventing user from updating quantities from Sales details

[RickHunter84]

Hello friends,

Happy new year 2021! I hope this year is much better than last one, I don't think it will be really hard to make it better

Here is my finding while troubleshooting/testing. this is Inventory management DB - MS ACCESS 2016

I have tblSalesOder connected to tblSalesOrderdetails, and tblSalesOrderdetails related to tblInventoryOnHand (all relationships work), my question is regarding handling the user interaction after inputting the quantity he wants to sale. For example:

I have product XYZ qty 100ea : The product is typed in, all the information is retrieve, then the user sales qty 25ea out of this product. After the line is added the inventory is reduced by 25ea (no problem).

The problem I'm having is that what if the user enters the quantity and realizes that he missed typed the number (25ea) and the correct number was 20ea. Since the quantity was already entered and reduced from tblInventoryOnHand, if the user changes the quantity again, the 5ea difference wont go back to inventory, since the inventory was already removed. Any suggestions on how to handle this situation? opinions?


Thank you in advance,
Best regards,
Rick

 

[Gasman]

OnHand should be calculated, not stored, for this very reason.?

 

[RickHunter84]

OnHand should be calculated, not stored, for this very reason.?

Good point. fair enough, thank you! ill start working on the solution based on that opinion.

Rick

 

[Gasman]

Good point. fair enough, thank you! ill start working on the solution based on that opinion.

Rick

Wait for some experts to chip in, but I think they will concur?

 

[gemma-the-husky]

I would certainly agree with the point about not storing values that can be easily calculated, although sometimes you may decide to do so for a particular reason. Foe instance you will probably store the total value of an invoice, even though it could be recalculated from the invoice lines.

In general, maybe another issue is not committing the entry until it's completed. If there is any possibility of needing to cancel an entry it may be better to try to find a way of storing the new action in a temporary structure until the user is ready to commit the whole process. If the user enters rows, and then starts to delete or amend them, it may cause problems if other users relied on the now modified/deleted entries. If you are just working on temporary data, you can make whatever changes you want, until you decide to either cancel the whole thing, or accept the final version.

 

[CJ_London]

I concur - calculated values should be calculated when required, not stored.

depends how your tables are structured but typically you have a minimum of two tables - products and transactions. Products might link back to suppliers/manufacturers, transaction might link back to other tables providing more information about the transaction such as location, on reserve/on hold/customer PO/company PO etc, but the core transaction values would be in the transaction table.

incoming stock would be stored as a positive number, outgoing stock negative. Or you might store all transactions as positive and have a transaction type field which can be used to indicate if the value should be multiplied by -1. Current stock is then simply the sum of transactions to date. Some transactions are indicators such as reserved so depending on requirement current stock might include it (as 'sold') so it can't be sold again, but is still here for stocktake purposes since it isn't actually sold so still a company asset.

In my stock control system I have a transaction type 'stock take'. When this is entered by the user (who enters the actual stock) a second stock record is created being the adjustment. The benefit of this is when calculating current stock, the system looks for the latest stocktake value and only sums the transactions from there forward. If one isn't found then it sums from the first record. Otherwise 3 years down the line, there may be a lot of transactions to sum.

 

[RickHunter84]

Thank you everyone for your points of view. I went for the calculated field option, I think it simplifies the handling of the problem.

Have a nice rest of weekend!

Rick