3 Replies Latest reply on May 1, 2017 5:43 AM by philmodjunk

    Inventory/Asset Management System

    dimitrisf

      Hello,

       

               I am in the process of building an inventory/asset management solution for a state organisation. (no prices etc involved). It is actually a distribution network of items. From Department to Users and back after usage is completed.

       

      The basic processes are:

       

      a) Receive new items

      b) Loan items

      c) Receive lent items back

      d) Write-off items (e.g.  consumed items)

      e) Change items location

       

      The table structure is like this :

       

      Processes--<Process Lines

      Inventory

       

      -User initiates a new process and populates process lines  by selecting available items from Inventory table.

      -A script populates a Transaction Table and updates the Inventory Table where total quantities are kept

       

      In Inventory table a record represents a group of items sharing the same value in both three fields :

       

      a) Item

      b) LOT

      c) Serial Number

       

      So item qty is grouped for same Item/LOT/Serial Number.

       

      QUESTION:

       

      How should I handle items in case for example, I have given a Loan of 20 Steel Wrenches (no LOT or Serial Number) and when the user returns them, reports that 3 of them malfunction,

       

      How should I store that information?

       

      Thank you!

        • 1. Re: Inventory/Asset Management System
          philmodjunk

          "I have given a Loan of 20 Steel Wrenches (no LOT or Serial Number) and when the user returns them, reports that 3 of them malfunction,"

           

          Why would you loan out materials without recording that information? (Ideally, each inventory record should have a single field (primary key) that uniquely identifies each record.

           

          And what hat should be done, in terms of current procedures when a user reports loaned items as list, defective, or damaged?

           

          usually, this is all logged in the transactions table where you have field to log number of items out and a field to log number of items in. Broken or lost items would be logged in from the bottower but then logged out again with an appropriate comment in a text documenting why the items are no longer available for loan.

          • 2. Re: Inventory/Asset Management System
            dimitrisf

            Thanks a lot!

             

            Dimitrios Fkiaras
            "
            I have given a Loan of 20 Steel Wrenches (no LOT or Serial Number) and when the user returns them, reports that 3 of them malfunction,"

             

            philmodjunk

            "Why would you loan out materials without recording that information? (Ideally, each inventory record should have a single field (primary key) that uniquely identifies each record."

             

            Well, in the specific example the manufacturer of the Steel Wrenches does not build them with LOT or Serial Number, (From what I know LOT number usually applies to items may show a different behaviour under certain conditions. e.g. expiration date).

             

            So if I understood correctly the suggestion is to use a record for every steel wrench? At the present I only have a record in inventory table:

             

            ItemName:Steel Wrench, LOT:--, SerialNumber:--, Qty onHand:10000.

             

            I was thinking too keep items in groups of same LOT,  rather than in separate records. Of course for items with a serial number each item will have its own record.

             

            philmodjunk

            And what hat should be done, in terms of current procedures when a user reports loaned items as list, defective, or damaged?

             

            Some items have their Shelf Life reduced when they are unpacked. Lets say that "Mike" opened a box of 100 electronic regulators and he didn't use any after all. Upon returning the items he fills a form where he reports he made any changes to the items that affect the quality (opened package, damage, defective etc)

             

            The Department that gave the Loan receives back the items and labels the item or the box.

             

            The item is kept separately to be given again, or writen-off or repaired.

             

             

            - I thought that we could give a serial number to all items or packages but that is costly and maybe not necessary.

            -What is the usual practice for these cases?

             

            -For example a food store may record expiration date for its products  but they don't give serials to every can of beans.

            -A library which loans books may take back some books with missing pages, how do they handle this? Do they have serials for every book and they record the defect in the record of the book?

             

            Dimitrios.

            • 3. Re: Inventory/Asset Management System
              philmodjunk

              "-For example a food store may record expiration date for its products  but they don't give serials to every can of beans."

               

              No, but they do have a bar code that identifies them as a specific product. You need at least that much info for every item in inventory.  Note that I stated that you needed such an ID for each record in inventory, not each individual item. One of the most important decisions you have to make is whether you have one record for every individual item (cars) or for groups of items(cans of beans).

               

              Yes, library books typically have a bar code sticker affixed that uniquely identifies every book.