2 Replies Latest reply on Dec 3, 2013 9:21 AM by willrollo

    Stock taking.


      Stock taking.


           I have seen a few threads on this topic already but they may be more complicated than I actually need. A brief description of my database. I have the following linked tables that create a working invoice solution. Customers - Invoice details - invoice items - products. 

           Only around 5 of the products that the company sells are held as stock. The user manually orders these products as and when required and manually adds the amount ordered to the stock count. I believe most stock taking solutions have this automatically calculated from purchase orders - this is a feature I do not require. I am trying to copy an old database which is why I wish to do it this way.

           How do I set this up as I feel this may not be a simple calculation field 'Stock' that would be a calc of 'Stock' + 'Stock received' - 'back ordered'. Back ordered I assume would be a field on an order. I know this is a very basic overview of what I have and require but was hoping to go more into detail once asked the right questions! Many thanks in advance...


        • 1. Re: Stock taking.

               Can you explain, in terms of your database, what actions will increase Stock levels and which will decrease them?

               There are two basic approaches to what you want, a script can update a number field in products or a calculation field can compute current stock on hand from data in the related tables. The simple number field makes for fast reports but risks a much greater chance of storing an incorrect stock level. A list or table view of many Products records may update slowly if you use a calculation field to show the stock levels but the calculation field will always show a total consistent with the entries in the related table that affect the calculated total.

          • 2. Re: Stock taking.

                 Thanks Phil - I believe now that this has more or less been sorted from my other post fixing my script. 

                 However, in answer to your question - To add stock, the user need simply add the amount to add to a product, on the product record in a list view. A script is then initiated to add this number to the physical stock field. 

                 To use stock, as it were, the user clicks on a button called confirm, once an order has been placed, on the order layout, This script loops through the invoice items records in the portal and takes off the relevant quantities from the physical stock fields for each product. If the amount will go to 0 or less, then a warning comes up.   I will need to add a back order function to the database but will do that this week...