Looking for a very specific inventory system!

[FloridaFlyer]

Hello all; excited to finally be on GJ after years of lurking as a guest. My first post is gonna be a bit of a longshot, but seems like there's a chance that somebody on here might have an answer I'm looking for.
My main workshop space is currently a 25x35 ish barn, with additional storage in 32’ and 14’ enclosed trailers and a 14’ U-haul van that doesn’t run. Parts, hardware, and tools are contained in freestanding shelving and bin racks, with most of the tools in rollaround boxes. The challenges I’m facing are primarily:
Short on space, everything is very condensed
Because of #1, things are constantly moving around, and I’m working on some sheds for additional space, so more moving around when those are finished
I’m sharing the space with my Dad, so when one of us moves something, the other party often can’t find it.
So, I need an inventory system. I’ve worked in Aerospace and Power Generation but the systems I’ve seen are built for facilities with manpower dedicated to inventory. I need something super streamlined so we will actually use it. I’ve done some looking, mostly at Android apps, but haven’t found what I’m looking for.
I’ve come to think my #1 feature requirement is this : I want each bin of hardware to have a printed QR code, and every shelf to have its own QR code as well. When the urge to move stuff around hits, I want to be able to use my phone (or scanner) to scan the QR code on the bin of stuff, then scan the QR code of the new location. Done. No typing. This would allow reorganizing quickly, especially by trial and error, finding a good way to fit say 5 bins of LED bulbs onto a 5 shelf unit that already has many bins of electrical components on it. Very often, this means moving a dozen bins around to get everything to fit. Very different situation than a huge parts room with extra space and a planning committee to generate a location schedule!
Then for finding what I’m looking for, I need a good search function of this system; maybe something that feels like McMaster-Carr’s menu. Search for “Bolt”, then choose “½”” and this elusive inventory app would tell me the ½” bolts are in a bin on shelf 2A.
So. Anybody else have any experience with something like this? I’ve thought about just having a google sheets file , and if I knew everything was going to stay where it was, that might be fine. Certainly having an old laptop set up in the shop for searches would be doable, but as soon as it’s time to shuffle bins, it would be a nightmare. I’d need a helper to type while I shouted things like “bin S/N 3402019 labeled bolts, ½” is now located on shelf 13F”! The QR codes are key.
If I draw a blank here, I’ll keep looking and update when I make some progress. Right now I’m back to building shelves - If I can get all the electrical stuff in the 32’ trailer, I can clean out the van and get it insulated and finished, so the A/C isn’t fighting a losing battle in the Florida heat. It’s like a giant Rubik's cube.. And I don’t like rubiks cubes!!!

 

[vavet]

That is an ambitious project. Obviously it's possible. I have no idea if there's an existing turn key solution for you.
I wish you the best. Hopefully you find something. Please keep this thread updated so others can follow in your footsteps.

Out of curiosity - what kind of work are you are Dad doing that you have this much stuff to organize?

 

[FloridaFlyer]

My fear is definitely that there may be a turn key solution, but way too expensive for a homeowner.
The source of all this stuff... well, where to begin... haha

• I'm an A&P / IA aircraft mechanic, though that is mostly in the past. Still have a lot of tools and hardware. Plan to build an experimental aircraft someday, and in the more immediate future, buy an older certified aircraft to keep my A&P and IA current.
• I work on my own vehicles: 2000 F350, 2005 Focus, 2004 HD Sportster.
• I own a travel trailer, and plan to build a custom truck camper for the F350 soon.
• My folks will build a home on the 10 acres in the near future, and much of the finishing work will be done by us.
• The ONLY project for pure fun right now is building a racing simulator setup. Current a pile of parts in several bins.
• maybe a boat soon??

Most of the inventory stock is what I would consider very general purpose, with the exception of the very small amount of aircraft stuff and racing sim parts.
Just creating the workspace from what was an open pole barn a year and a half ago has sucked most of my free time and filled most of the available shelf space with electrical components, pipe and fittings, and so much paint, glue, and solvents...

 

[sberry]

This is somewhat of a brain fart. That may seem crude to a new member,,,, which we like but for general use stuff there is no good way to catalog a bunch of general junk. Shelves are good, some boxes, some with fronts cut so you can see in them but,,, box for bulbs, box switches and recepts etc all in the same area. Not worth sorting every piece and nuts/bolts need a cab the stuff can be tossed in general order. No point in sorting every screw.
I have a couple more shelves I don't have pics but general organized and similar. Simple box for like items. All that listing and sorting is good when a place needs to reorder in timely fashion but for a few 3/8 nuts not worth it, not worth the effort for left over random items.
I have swpace I created out of the way, shelved for stuff too good to toss but don't ewant to rummage thru in the work area or common stock.
Toss out lots of vague packaging, I strip a lot of it off, not providing comfy storage for cardboard and plastic bags. Someone gave me a bunch of packaged stock the other day, nails, screws, eye bolts etc and I strip it and toss it in the coffee can with the rest of it.

 

[vavet]

Had another thought - once you start cataloging things, don't be afraid to put dissimilar things together. For instance, don't put the 1/2-13 x 1 bolts right next to your 1/2-13 x 1.25 inch bolts. This makes it easy to compare, but it also makes it easy to pick the wrong one. If you're going for a bolt, and you go there and find an alternator - you know you're in the wrong place.

 

[Bruce 993 SEA]

Had another thought - once you start cataloging things, don't be afraid to put dissimilar things together. For instance, don't put the 1/2-13 x 1 bolts right next to your 1/2-13 x 1.25 inch bolts. This makes it easy to compare, but it also makes it easy to pick the wrong one. If you're going for a bolt, and you go there and find an alternator - you know you're in the wrong place.

This is how an electrical distribution company I worked for does there warehousing. The warehouse is a couple of football stadiums in size. It is counter intuitive but Vavet is right, if you see what you are looking for, you do not have to compare it to the adjacent ones. Most people want to have them lined up together...Human nature.

 

[bdbecker]

You could build a custom database in Microsoft Access that would be capable of doing what you are asking. It would be a little legwork up front, especially if you're not familiar with the software, but you could make it exactly what you want it to be.

EDIT:
FileMaker could be another DIY option as well, and you could have it run on a tablet or smartphone.

 

[Strouty]

Had another thought - once you start cataloging things, don't be afraid to put dissimilar things together. For instance, don't put the 1/2-13 x 1 bolts right next to your 1/2-13 x 1.25 inch bolts. This makes it easy to compare, but it also makes it easy to pick the wrong one. If you're going for a bolt, and you go there and find an alternator - you know you're in the wrong place.

I was at an auction one time, it was an older guy's shop that was retiring, he had two sizes of bolts in the same bin, think 5/8 and 1/4", no mistaking the two. Made his storage a lot more compact as one bin held two sizes. Only works when they are dissimilar enough though.

 

[Dumber than lumber]

Well you are likely the perfect personality type for such a specific system/need. I mean, it is a great idea, just few people would be compelled to pursue it in such an analytic fashion.
A few ideas:
RFID tags - so the tag will help you find anything
3D bar codes with a lookup table
I am sure there are other good ideas. The RFID is appealing as the cost of those tags has come down a lot. But I don't know what the hardware costs run.
I do know that in the amusement park and cruise ship industries they are doing a lot with the smart armband devices.
So I expect there are dozens of hacks out there if you know where to look.
Good luck, and please keep us apprised.

 

[ScottsGT]

I went down this road within our department at work. We are the campus A/V integration department, so you can only imagine the various cables, nuts, bolts, terminations, projectors, screens, rolls of cable, etc... that we go through.
We looked into the same thing using the smart phone to pull inventory.
I'll put it this way, no one was willing to invest in the cost of the software systems I found. Plus they wanted it to talk to the billing system.

 

[SGKent]

you are really looking for a Point of Sale system. You can assign the cost to what you paid. The reason is you are wanting STYLES to be a part of the system. Example - you are going to have a product say cad plated metric bolts. That is a STYLE description. Within that style you will have sizes and lengths. This is the same type system a vendor might use selling blue jeans. Levi 501 is the style. Within that style are the waist and length combinations and quantities. I would guess even simple point of sale software are in the thousands of dollar range and up. So if you are looking for something in the DIY home market then try using just a series of Excel Workbooks with different spread sheets for what you keep in stock. Another reason Point of Sale systems would work is the ease to inventory things. It will not be as easy in Excel. Good Luck.

 

[outdoorspace]

Look up Partkeepr.

 

[PartsGuy]

Is the purpose simply to track location and find the items, or also to keep count?
I set up an Excel spreadsheet for stocking spare parts in my farm shop, with shelf location, part number, and "formula" columns for keeping track of min/max quantity.
For example, I had three Ford tractors that used the same fuel filter. I assigned a shelf location, entered the part number, and set the min/max at 1 and 3. All my employees knew that if they used a part, the box top/label went in a box on the bench. At the end of the day, those part numbers were adjusted on the spreadsheet, and if it dropped below the minimum stock level, the cell turned red, indicating that it should be re-ordered, up to the max level. Then I'd filter the red cells, print it, and place my orders with my supplier as needed. When the restock arrived, anyone could put stock away, based on the shelf location assigned to the part.

 

[matt_i]

I think you need a bigger shop.

Someone has to spend time "engineering" the label so the 2D matrix will have the proper info in a standard format. Whos going to print these on what stock and will it be durable enough so it doesn't get damaged slightly and now nobody can figure out anything...or are you also going with english text and what is the format for that. Many questions just around the proposed labels.

I recommend a cordless scanner once you get going

 

[PCMusicGuy]

I think all of the systems that can do what you desire will be cost prohibitive because no one designs for that narrow of a focus. Everyone these days wants their inventory system to tie in with purchasing, etc.
What you describe seems specific and rather simple and likely it wouldn't be too hard to program. Do you have any android programmer friends that could help you out?

Maybe contact a local community college's programming department and see if a student wants to help write the program and offer to be the tester. They could use it for a project or something.

 

[cvairwerks]

I can tell you that from practical experience, you will abandon a computerized system within a month or two. We had to change our inventory system for our serialized accountable parts. It took almost 18 months to get the software to work correctly ( mainframe SAP based), and then it took 6 of us working at least 1/2 a shift, every day, for 9 months to get the basic info into the system on a total of about 69,000 individual items. I spent about an hour a day doing updated information, on average, for the next 4 years I was in that group.

This was a forced replacement for our manual system that had been in use for the prior 50 years. With the manual system, I could update the records for 50 parts in under 10 minutes. With the computerized system, the same update took at least 40 minutes.


The big thing to think about, is do you want to spend your time playing inventory geek or working your projects....

 

[bugnut]

My .02$
Similar need at work before retiring a few years ago, best idea at that time was rfid, tags per container/item/bundle/box, etc and then searchable database for location.

Thoughts to ponder, if it has a barcode just scan the barcode and note location.

I would expect a N, S, E, W for position at the door to a general location may get you close enough. If that isn't fine enough clock direction or shelf along with the compass direction.

AS a note, you want to use a phone to manage this, so why not just create a location, barn, 32’ trailer, 14’ trailers, 14’ U-haul, then utilize SIRI or BIXBY to create a list with location. This would get you close, but then the person moving the piece would need to update the new location. And it would have to be shareable...

 

[FloridaFlyer]

Lots of great responses! Very quick update as I really need to get outside and prep site for the 12x24 slab that a new HF 10x17 carport thingie is going on, will be used for Harley and human powered bike storage. I was supposed to do this yesterday but much of the day instead went to building a shelf unit for my CR-10 FDM printer, which turned out disappointingly non-rigid, so that is officially an incomplete project. Sigh.
It looks like the immediate solution is going to be a Google sheets file. For now each bin and box will have a four digit barcode / serial number, which can be turned into a QR code with the QR Code generator add-on for sheets. Each shelf location will be numbered with a two digit number, and letter for shelf. Same goes for QR codes for the shelves. I can have a cheat sheet for what shelf is where, mostly for my Dad : Shelf or Rack 12 is in race trailer, Shelf/Rack 07 is in the barn.
The key to the search functionality of sheets, for me, is Data>Sort Range>Add sort columns as necessary. I was thinking for a while I’d need to mess with index match or vlookup formulas which I don’t know anything about… learning all that right now wasn’t appealing. But prioritizing sort columns should work well enough for my purposes ; the current setup goes something like hardware>nut>M6. Then I’ve have several different M6 nuts - plain, self locking nylon, stainless, etc. If I add a new variety of M6 to the inventory, It’ll still show up with all the others on the sheets file, even if barcode numbers are 0013, 0120, 5203 and 7700, and physically on a different shelf (hopefully temporary) if there was noplace to stick them with the others.
Assigning the location will, for now, just be a matter of manually entering it. But, now that I’m getting a specific system in place, I may be able to garner help from some programmer type for what should be a simple app to cook up - when I scan a four digit QR code, the next code scanned, if two digit number/alphabetical character, will be the new location, and these changes will automatically occur in my sheets doc. Again, in the meantime, I’ll just have to manually enter stuff.
The main thing is getting labels printed with the four digit barcodes for each bin. Smaller hardware is in Plano fishing lure style divider bins, and larger or mixed items is in 16 qt and 6 qt sterilite clear plastic bins. Those will probably be printed on return address labels. That won’t work for the small open bins (Ex: Harbour Freight “20 Bin Rail Mount Stackable Parts Storage”), those will probably get printed on cardstock which I’ll just have to then cut apart into 1” x 3” strips or whatever they need to be. I mostly use those type bins for larger hardware or that which I have a lot of. All told, just as a rough guess I’d say I have about 150 of those bins, about 30 Plano divider bins, and at least 100 16qt/6qt clear bins. So about 200 labels that need printing initially. Will keep me busy for a while but doable.
I’ll post some screen shots of the inventory file so far, and photos of the shop soon, but for now it’s getting hot outside and I need to get laying gravel.