AnsweredAssumed Answered

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

Question asked by AdamHorne on Feb 29, 2016
Latest reply on Mar 1, 2016 by 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?

Outcomes