2 Replies Latest reply on Mar 1, 2016 5:39 PM by AdamHorne

    Advice on tracking inventory items with no unique manufacturers serial number or just plain generic items.

    AdamHorne

      I'm looking for advice on tracking inventory items with no unique manufacturers serial number or just plain generic items. 

       

       

      I've been building a database to track some items I have in inventory.   For example, lets say I rent computer monitors and I have (2) types of items in my inventory called MONITOR and POWER PLUG

       

       

      The way I'm currently set up is like so:

       

      ITEMSERIALQTY ON HANDSTATUS
      Monitor AMON-011IN STOCK
      Monitor BMON-021IN STOCK
      Monitor CMON-031IN STOCK
      Power PlugPLUG3IN STOCK

       

      When I rent out a monitor, I have a script that takes the monitor (via barcode) and gets its "accessory item" in this case, the plug and checks it out.

       

      ITEMSERIALQTY ON HANDSTATUS
      Monitor AMON-010CHECKED OUT
      Monitor BMON-021IN STOCK
      Monitor CMON-031IN STOCK
      Power PlugPLUG2IN STOCK

       

      So when I Return an item, it adds Monitor A back into stock and sets qtyOnHand to its value and adds 1.

       

      I'm beginning to wonder if this is the wrong approach, though.  I'm now wondering if I should make a unique Serial ID for plugs and have the script check out the first unique serial it finds "In Stock" and assign it to an order.

       

      ITEMSERIALQTY ON HANDSTATUS
      Monitor AMON-010CHECKED OUT
      Monitor BMON-021IN STOCK
      Monitor CMON-031IN STOCK
      Power PlugPLUG-010CHECKED OUT
      Power PlugPLUG-021IN STOCK
      Power PlugPLUG-031IN STOCK

       

      I do not rent out monitors, I'm just trying to use a common example that everyone can follow.  I literally have hundreds/thousands or generic items (that ARE COUNTED), but all tied back to ONE unique ID.  No matter how significant/insignificant the item is, it has a unique ID per 1 item.   One concern is that there really is no way to say this plug missing is PLUG-03. Should that be a concern or do I just delete records when I do a physical inventory and match my record count with inventory count?

       

      Is it too much to have thousands of records in your inventory table, when you're really only concerned about 900 or so?

       

      Thoughts?

        • 1. Re: Advice on tracking inventory items with no unique manufacturers serial number or just plain generic items.
          siplus

          I think it's better to track everything with its own number.

           

          I do have a similar problem in a system we built. In that system, people take stuff from deposit, go out and use it then return it. Originally, they wanted to say "give me a 21" monitor, a cable and an adapter". Later, though, they wanted to associate a preference to items, because brand A is "better" than brand B, so if possible they would like to choose the best 21" monitor, based upon availability and ratings. Not only. Monitors (everything is an example, of course) have a calibration date and time, so they don't want a monitor that has to be calibrated every 2 years and the next calibration date is next week.

           

          For your second question, thousands of records when you're really only concerned about a handful: look at your basement. How many things have you gathered there ? Stuff you will never touch again, maybe. But an inventory worth its name has to list them all. Of course, you can divide it into static and dynamic: note the last used date for every item and at night run a script that deletes all the records from your dynamic table and imports only the stuff you've used in, say, last year. You'll work 90% of the time with the dynamic inventory, in 10% of time you'll take a look at the bigger, static one and eventually items will migrate from one to the other. Or be bought, because you don't have them yet.