Inventory controlled parts storage bins

Posted by: @woodnboats

↑

BTW, there was a screwdriver hanging crooked in your last video 😊

While this is certainly a creative suggestion, it also would take a lot of work just to wire in all the LEDs.

This is my parts storage area:

There are 540 small drawers, plus a bunch of larger ones. Most of the drawers are split into two, so they store two components. Wiring up LEDs to every drawer would consume most of the rest of my lifetime. And I also don't relish the idea of counting all my resistors for inventory purposes!

I do have a spreadsheet that lists all the parts, but that's just so I can print labels.

So while I would certainly give you points for creativity here, I don't think I'll be doing this too soon!

😎

Bill

Posted by: @dronebot-workshop

↑

There are 540 small drawers, plus a bunch of larger ones.

Wow, how many thousands of parts in that set up there must be.  Dread to think of how many unused rpi's must be hiding away in that storage room.

Forgetting about the LED's to light a path to the parts as not practical, the ability to control the parts list in a database that highlight the 'bin number' where they can be found and records the additions and removals from the 'bin' is quite trivial and easily programmed on a Rpi utilising the likes of a mysql database.  

But not to do any grunt programming for oneself, why not just look up the plethora of free inventory programs you can find to run on your PC, or indeed a raspberry pi. 

One just needs to a label printer to number each bin, or drawer such as cabinet 1 drawer at row 4 col 6 (or C1-4/6) or whatever numbering scheme you choose and to identify the 'bin' location with the same number scheme in the inventory program  

In practice, as it us dump people that actually add and remove the parts, dumb mistakes will eventually lead to parts not being found as indicated by the inventory program 😎 but it should be good for a 99% hit rate (maybe 😉 ).   I know I'm going to have to tackle this sometime soon, finding stuff in my office is becoming quite impossible (half an hour yesterday just to find a usb3 A to A connector - I almost gave up and ordered another one from Amazon) and my current simple spreadsheet is not cutting it.  Just for the heck of it I will eventually be doing my own inventory Db, it makes it easy to do additional things like link parts to web pages containing datasheets where desired or record where I obtained them etc.  

@byron 

Even if you have a lot of parts if you store them according to some logic they should be easy to find. For example you mentioned a usb3 A to A connector. I have them all in one box easy to find. Like should be stored with like. I know some people just toss everything together and have to do a serial search for every item. "Oh its in one of the drawers somewhere." I open every drawer full of random stuff and it is like the game "Where in the world is Wally".

It does require discipline but in the long run it is easier to make the effort not to leave things lying about or placed in some random position after use.

If I want a particular screw, bolt, tool they are all sorted according to kind and in turn sorted according to type and then size. Need a Phillip screw driver look in the clearly labeled screw driver section, then to the Phillip section and they are ordered according to size. Easy for anyone to find.

@byron I also will be building a DB as soon as I get settled. That may be this year or next spring. Still a lot of unknowns. I am sure you will be doing yours with some form of Python while I will be taking advantage of my 'Msft all the products we make for $100 a yr plus 1TB cloud' The SQL DB is called Access, and once I get started, it will come together fairly fast. I might even see if any templates will give me a leg up, but I doubt it. I have built a few of these before and consulted on one for the Ontario Government, but it has been a while. I couldn't organize anything with the tiny space in the trailer, but now I have half a bedroom! I will post some screenshots on this topic when I get something done in case anyone can benefit, but it's all pretty basic (no pun intended)

Posted by: @robotbuilder

↑

Even if you have a lot of parts if you store them according to some logic they should be easy to find.

It often starts out that way.  Say you put all your transistors you have in some storage drawers in cabinet 1 and you use the first row of 8 draws for all the varieties you have.  Soon other parts get put into adjacent drawers.  A year later you get a few more different varieties of transistors, you would like to store them nearby your other transistors, but heck, the adjacent drawers are full.  So they get put in cabinet 4 that has a few empty drawers.  Six months later you want to see if you have a particular transistor in stock.  Will you remember where all the transistors are stored, has your initial storage logic been compromised? Probably.   Now if you recorded you parts in an inventory program you could very easily ask for a list of all your transistors and their location.  

Of course you would go and grab the required transistor out of its drawer and entirely forget to record you removed it, but thats down to old age.  Chaos awaits us all 😎 

@ dronebot-workshop Instead of LEDs, a ceiling-mounted laser that shines a light on the needed drawer. Other than a little math complexity, it should be possible to convert an XY position into an angular measurement for the laser.

Posted by: @zander

↑

I am sure you will be doing yours with some form of Python

Possibly, but I might also do it in Access as I still have Access 97 that still runs on my PC.   Another option it to use the Libre office Db that runs on linux, also runs on the mac.  I wont get around to doing mine until I finish up my current DIY project of remodelling my 'snug' room, and then refitting out my office/lab room. I think you may get there before me, even with half a bedroom, so I may well follow use your published example, but I expect a professional front end to input and query the data 😀 

@byron There is a saying in DB design, the best Primary Unique Key is a meaningless number assigned by the system automatically. Even in my paper files, I file a document in any old place as long as it is recorded in the DB. The DB is organized. There is no need for the physical files or parts to be.

I have seen the ultimate, where each new incoming part gets a bar code sticker (see Sheldon and his clothes packing) so it can be scanned in and out. Too much for me, I will take my chances on my memory plus a periodic inventory driven by such things as unit cost, turnover, low On Hand Qty and so on.

Posted by: @zander

↑

a ceiling-mounted laser that shines a light on the needed drawer

Don't forget the security door and mandatory eye protection before entry is permitted as accidentally directly looking at the Laser will not be good.  And what happens if you stand in front of the drawer and block the laser beam.  Nope, I think you should go back to the drawing board for that idea. 😎  

Posted by: @zander

↑

There is a saying in DB design, the best Primary Unique Key is a meaningless number assigned by the system automatically.

Well indeed, I hope that is how any Db would be designed.  A simple sequential number can be assigned to any new part description you enter, no need for a fancy barcode numbering system. Your entry screen should assist in automatically showing you similar part descriptions to you newly entered part as the bugbear of inventory (or indeed other Db systems such as a customer/address Db) is inadvertent duplicate descriptions for the same part.  

At your age relying of memory is risky, but it would depend on the size of you parts inventory stock, but as I think you are moving into a new mansion I expect the number of parts will grow until SWMBO puts her foot down. 

I broke the problem down into 3 parts. I'm not worried about inventory count, I can just make a new order when I notice that I'm running out.

There are three levels to my system box type/box number/bin number.

Top level is a box type which is intended to help the search by eliminating a lot of potentials. For instance, I have boxes that have 23 compartments and I also use used 3D filament cardboard boxes which have only one compartment. These both have different codes so I can eliminate all 60 filament boxes (whose code is W) if the part location doesn't start with a W.

Each box has at least one compartment (called a bin). I usually buy them in groups of 8 or 12, but of course the next time you need boxes it seems that the style you bought is no longer being made. So each box type is identified by a picture and a bin count (and also a record of how many boxes have been added to inventory). Each new box must have its bins labelled with the bin number (eg E14001) before being added. Boxes that have only one 'bin' (like the recycled filament boxes) are just given the bin number 001 (eg bin W28/001 contains all 2-pin LEDs).

The box types are assigned manually and require a picture supplied by the user. Box numbers within that type are just sequentially assigned by the app. Each box will be created with the specified number of compartments in order from 1 to the max count. The user determines how to assign these bin labels in the box. So you can label left to right or back to front, whichever best matches your ability to find the bins. 

Here are examples of a rack of W style boxes and a shelf set containing different box types.

Here's an example of a box with multiple bins. You can (barely) see the bin labels on the back of each compartment.

The database has some very rudimentary reporting capability (I always use live lookup so paper reports were never a priority).

And the main user interface looks like this ...

The search function allows you to look stuff up by box type and number (if you're too lazy to scroll down to it) or by searching for some text in the part description.

The program is written in Objective-C for the Mac and built using my wrapper on top of the Flying Meat SQLite wrapper.

The only real overheads are setting up the box types and labelling the bins of new boxes.

When I order parts and the arrive, I just do a search of the database and, if I already have that part, add the new stuff into the bin (or move everything to a larger bin). If I don't have it, I just add it to an empty bin.

I don't bother cataloguing stuff that's visible like wood, rods, lead screws and other such items that are stored in sight in racks around the periphery of my workshop.

@will

Nice Will, very nice.  I like the idea of pictures, perhaps even pictures of some parts.  A year or so back in my organisation 1, I put some parts into one of those boxes of drawers.  A couple of parts and even a couple of boards did not have any recognisable part number and I did not have a clue what they were.  I had to spend a fair bit of time perusing my electronics ordering receipts to identify what they were. Fortunately the order/receipts that I made are kept in an archive mailbox so I could eventually identify them and describe them in a simple spreadsheet I keep.

So whatever inventory system I come up with, the ability to record pictures of the parts and the ability to link to the original order/receipt will defiantly be part of the design.   And as parts are often kept in ad hoc boxes of various sorts,  a picture of the box and perhaps a pic of the storage area with an x on the location is also a jolly good idea I will be copying.   

I see you are another chap with a rather large storage of parts, but I see you have a well thought out system, and I'm taking your ideas on board.  Good food for thought.  

@byron 

Posted by: @byron

↑

I see you are another chap with a rather large storage of parts, but I see you have a well thought out system, and I'm taking your ideas on board.  Good food for thought.   

The pictures only cover a fraction of my inventory. You can see in the first app screen above that it's holding 176 different boxes representing 1664 bins and 2480 parts (that's 2480 descriptions of the part, I normally have more than one piece of each).

I HAD no alternative but to automate !