3 Replies Latest reply on Mar 29, 2011 1:29 PM by philmodjunk

    Stock List

    ThomChase

      Title

      Stock List

      Post

      Hi All, 

      One quick questions about Stock Lists. I want to create a goods in form that adjusts the items in stock. 

      The way I have it at the moment is for each stock item type to be an entry with a Qty field. So for exampleI

      Item Qty

      Screw   20

      Bolt   10

      I want an 'Input' report called a goods in where I can drop down a menu of stock items and a field called, New_Qty. I will then put the new item in to the form and place it in the warehouse and the stock list is updated. 

      Do I need a new Table for goods in that feeds the stock list or is the goods in layout based on the stock table. 

      I'm a little bit confused and I can't seem to find an example of this anywhere. 

      Thanks

      T

        • 1. Re: Stock List
          philmodjunk

          How have you set up your tables and fields? Relationships?

          You may want to have one table where you log each change in inventory and a second table where you have one product for each item on your stock list. They can be related by an Item ID field. A calculation field in the Produtcts table can use Sum to compute the current inventory or you can define a number field in the products table that is updated to show the quantity on hand each time you add a new record to the Log table to record a change in inventory.

          A log table is useful to show how your inventory levels change over time and to audit for and correct mistakes. Logging items into inventory is as simple as creating a new record in the Log table and filling in appropriate fields. If you deal with invoices and purchase orders, they can be set up to use this same log table as a lineItems table.

          You would have this relationship:

          InventoryLog::ProductID = Products::ProductID

          Inventory Log would have tables such as these:

          Date, ProductID, QtyIn, QtyOut, cBal (calculation defined as QtyIn - QtyOut), sBalance ( Summary field, total of cBal, running total that restarts when sorted on ProductID).

          • 2. Re: Stock List
            ThomChase

            Hi Phil, 

            Big help. What you have said is exactly the direction I want to head in. 

            I have set up the new Stock_Log table and set the fields as you've said. I have it linked to my Stock table using the Stock_ID key. 

            The questions now is how are changes in the Stock_Log going to affect the Item Qty in the Stock table? Do I have to link them or set up a new realationship join?

            Thanks

            Thom

            • 3. Re: Stock List
              philmodjunk

              If you define the Item Qty field in the stock table as a calculation: Sum ( Stock_Log::cBal ), it will automatically compute the current inventory for each item.

              With very large sets of records in the log table, a list layout that uses this calculation field to display inventory levels for all your products, may update very slowly. To speed things up, you may want to use a number field and use a script that automatically updates the inventory level each time you add a record. The produces a total for each item that lists rapidly in the stock table, but you have to control your scripts with care to avoid introducing errors in this level.

              You can use the OnRecordCommit layout script trigger on you Stock_Log layout to run a script like this:

              Set Field [StockTable::Item Qty ; StockTable::cItem Qty]

              cItem Qty would be a calculation field defined in StockTable to use the above sum function to compute the inventory total for the item just updated in the Stock_Log table.