Parts inventory system for the lab?

[Skyfox]

Hi all, long time no post.  I hope some of you have some good suggestions on this.  I need to come up with a system to track inventory and locations of the various parts and things in my electronics workshop.  For decades I've been collecting pieces and parts for projects, some things working like power supplies or individual components, other things not working like circuit boards for salvage, and everything in between.  They're scattered between various bins, drawers, toolboxes, junk boxes, and shelves and much of the time I don't know where to find things because I don't have a good system for tracking inventory.

Recently I acquired a bunch of 1D and 2D USB barcode readers (electronics recycling collection is a goldmine!) and I'd like to use them for tracking what I have where with the ability to easily update locations and other details.  Printing barcodes or QR codes is no problem; it's just what to do with them that's the sticking point.  At an office job I had back in the 90s they had a file folder tracking system that used barcodes for every location and every file, and it worked by scanning a location and then scanning every file at that location so that when the data got back to the system it knew how to parse everything by item and location.  It wouldn't necessarily need to work exactly like that, but the ease of use is what I'm looking for.

I can't seem to find an inventory program (certainly not a free one) that does what I'm looking for, but something in a spreadsheet would be fine too.  I use LibreOffice on the linux box and I still use MS Office 2003 on the windows machine.  I don't know the first thing about writing scripts in Visual BASIC for Excel and don't know enough about formulas to make something automated with them, if it's even possible.  Oh, and while I've seen some things that use a smartphone app, I do not have a smartphone.


Do you have any suggestions?  And what do you do to keep an inventory of all your pieces parts so you know what you have and where it is?

Thanks in advance.

[Psi]

For hobby (non production) stuff I just have excel files to track what ICs/semiconductors I have, and for everything else I just make sure to store it in a way that is easy to manually search and find what I want or see what I have/don't have. 


It seems cool to have inventory tracking but its usually just annoying to maintain for hobby stuff.
For production stock it's easier to implement because you can have it automatically decrement stock when you ship an item and automatically add stock when you enter a purchase into the system. (ie, since you already have to do that anyway for your sales/expense tracking you get the convenience of automatic stock updates for free,  but for hobby stuff you have to manually inc/dec stock as you use the parts for stuff, which is pretty annoying and breaks your mental flow state when working on stuff)

[bostonman]

Have you seen this:

https://www.eevblog.com/forum/projects/myelecparts-electronic-component-search-and-inventory/

[Doctorandus_P]

You could start with a spreadsheet, but that will have to keep it updated separately from your electronics work. Looking up stuff and combining it with your electronics design is not well integrated.

A very good way is to setup a database and put all info into a database. Databases are generic software. They're very flexible, as they do not assume anything about the information you put into them. Downside is that it takes some programming to set up a database (and to maintain it).

With a database, you can also integrate it right into your schematic and PCB design. You want to add a part: Open the database from your schematic, select a part and put it in the schematic. When you output the BOM, you can add information on where you stored all the parts, and you can also add ordering information into the database. From preferred parts shops to part substitutes.

KiCad has added an interface for databases and can work with databases for a few versions now. I've read several success stories, of people interfacing a pre-existing database with KiCad, so years of collected company data is available. This is also one of the powers of a database. Because the database just stores and provides data, it's independent of the PCB design software you use. (I guess) all professional PCB design suites can integrate with a database.

[JJalling]

What about https://partsbox.com ? That's what I use (the free version), and I quite like it.

BR Jonas

[RoGeorge]

I would stay away from any dedicated software.

Programs, computers and operating systems are not something to rely on, when it comes to very long term (decades) backwards compatibility.  An update may mess your database.  To give an example, my first database of resistors inventory was in dBase 2?, on a CP/M machine with a floppy disk.  Meanwhile, CP/M went obsolete, same for dBase, and even for the floppy disks media.

For hobby, I am using now a spreadsheet (Libre Office Calc).
A manually spreadsheet with NO automation and NO scripts.

Any spreadsheet program can sort, filter, search, or embed scripting if you need a bar-code reader.  Same, can easily export to text, and they can be easily copied for backup, or moved and read to almost any other PC, no matter the operating system.

[Skyfox]

Thanks everybody!  I'll definitely look into My Elec Parts and Partsbox.

[mr ed]

Keep a list of equipment and expensive things only. You will never keep inventory on small parts even close to accurate.

[kripton2035]

I would stay away from any dedicated software.

Programs, computers and operating systems are not something to rely on, when it comes to very long term (decades) backwards compatibility.  An update may mess your database.  To give an example, my first database of resistors inventory was in dBase 2?, on a CP/M machine with a floppy disk.  Meanwhile, CP/M went obsolete, same for dBase, and even for the floppy disks media.

For hobby, I am using now a spreadsheet (Libre Office Calc).
A manually spreadsheet with NO automation and NO scripts.

Any spreadsheet program can sort, filter, search, or embed scripting if you need a bar-code reader.  Same, can easily export to text, and they can be easily copied for backup, or moved and read to almost any other PC, no matter the operating system.

I don't think you will have any trouble reading an sqlite database in many decades from now ... with any computer.

[Skyfox]

Hey all!  Since posting the original question and looking at some of the software options, I ended up starting a spreadsheet with macros to keep track of my stuff.  It's still a work in progress as I figure out how best to categorize things, but I've come up with some very handy macros to really easily enter or update locations of parts.  While entry can be done with the keyboard, it works super quick and easy with the barcode readers I acquired.  Making all the barcodes for things has been quite tedious because they all have to be manually resized and laid out on a label template for printing but it's been enjoyable work.  Once I get the spreadsheet more towards a finished work I'll post it here for others to use and customize however they want.  I've been making it in Excel 2003, mainly because I hate the modern versions of Excel, but I can also post a converted version for the current 64-bit Excel (the macros require one little tweak).  I can't seem to get it to work right in Open Office Calc but maybe that just requires a little more time to look into it.

In the future I might look into combining the spreadsheet contents into a MS Access database for easier sorting and display based on part category, but I know pretty much nothing about Access so there'd be a steep learning curve.

Oh, does anybody know how to get a macro VBA script to play a .wav file that's embedded in one of the sheets?  My spreadsheet plays notification sounds to confirm a good entry (or a failure/not found error alert sound) without having to look at the screen, but I can only use external .wav files.  Supposedly it can be made to play embedded wave files but I just can't figure out the coding to make it happen.  (What I've made so far has required a lot of google searches.)

[RoGeorge]

My very first inventory was in a database, but then I've learned that the inventory will outlive many generations of operating systems, computers and storage media, so my advice is to keep at minimum any automation. 


A script for a bar code reader is OK, though I wouldn't move to a database, that will only limit your freedom/possibilities to get creative.  You'll see that not any part will fit with with the fields of a given database, and modifying a database structure, and keeping your database queries updated with a new structure is a maintenance nightmare.  I did that and fall back to manual spreadsheet.

To give an example, my first database was in dBase2 under CP/M, on 5'' floppy disks.  Then, the IBM PC and MS-DOS came, and had to port it to MS-DOS.  Then Windows 95, then Microsoft Office and downgraded all from database to an easy to maintain and more flexible Excel file, and so on.

Now I don't use any of those, using Linux, and my hobby inventory is kept in a LibreOffice Calc spreadsheet, a single file with many sheets (Calc looks and works the same as Microsoft Excel, just that it is free and open source, not from Microsoft but compatible, can work with Excel files, too).

These recommendations are for a hobby lab, not for business.  If you do production/boards assembly, it's a different story.

[RJSV]

   Optical Recognition system ?
   I've noticed Google does a lot of object recognition innovation, although for myself, I would prefer to obtain only the assigned home-based part name and my own issued bar codes.
   You point camera and system indicates:
   'Special Hammer,  bar#. 123-456'.      OR,

   'Fleagant type Hammer',   No bar# assigned


   Having said that,  you might,  whenever you encounter someone who cleans local folks houses, for a living,  ask them about special clean-up and organize issues;  you might gain some insights.

Myself,  the organize problem,  is that there isn't clear, sheltered space,  to place things,  while I organize the office furniture, and mass numbers of boxes and bags.   Piles everywhere.

[calzap]

Agree with those who advocate a spreadsheet for home/personal use.  That’s what I use, and there is really only one choice:  Excel and clones.   KISS.  Macros and fancy formatting may not survive the next software generation or across platforms. The basic Excel format will be around for decades. If you’re paranoid, keep CSV in addition to XLS versions.

I keep inventories of even the simplest parts, like resistors and project boxes.  Not being able to find simple parts will hold up a project or repair just as much as something like a processor.

Mike

[kloetpatra]

I host my own version of https://inventree.org - it's free, open source and customizable. Scanning parts with your phone is very convenient as you may not always have a PC nearby. I have also integrated a label printer and automated tasks using QR codes. I love putting QR codes everywhere. Only my pants don't have a QR code because they're not as comfortable to wear with it.

[Skyfox]

It definitely seems like my spreadsheet is the way to go.  Some additional things I've done with it is to add code that checks to see which version of windows (32-bit or 64-bit) and which version of Visual Basic is in use so that the pertinent code is automatically compatible with everything from my Excel 2003 32-bit up through the current Office 365 Excel on windows 10/11 64-bit.  That really only affected the embedded sound routine.  That's the second thing I've done with the help of someone on youtube who made a tutorial just for this purpose.  Apparently it's impossible to embed a wave file as an object in a spreadsheet and have Excel play the sound.  Since my macros use sound effects for audible indicators, I didn't want the spreadsheet to have to rely on .wav files being present with it for them to play.  So, I put in a macro that can strip the hexadecimal code out of the wave file and put it into a worksheet, and then to play the sound it reconstructs the wave file in the user's temp directory to play that. In some instances the playback is instant and in others there's about a half second delay, but still it works very well.  If the user would rather have it play some other wave files because they don't trust having a file constructed in their temp directory, they can adjust the code to play any wave file they like.

The sounds are switchable with a checkbox on the macro popups in case the user doesn't want it to play.  The instances for sound playback are successful update, failed update/item # not found, successful search, and failed search.  The search feature is another recent addition that really makes a barcode reader handy:  when a bunch of similar looking parts are in a box or something and things all look alike, and I don't feel like looking at the numbers on the barcode, I put the cursor on the item # box I want to find, start the macro, and then start scanning the barcodes of things in the location.  A failed search makes one sound to indicate there is no match (so I don't even need to look at the screen to know), but when I scan the matching barcode the successful search sound plays and a popup box shows it's a match.  That may not be useful to those who don't have a barcode reader but I felt it was a handy feature.

While I did make my inventory spreadsheet with a barcode reader in mind, all entries in the sheet or macros can still be done manually with the keyboard.  One of these days I'll get the thing a bit more polished up and upload it here so anyone who wants it can get it, use it, tweak it, modify it, or whatever.  I'd love to see what other people do with it to make it more efficient, look better, and so on.  Oh, and if in the future a version of Excel no longer runs the macros because the VBA is far too outdated, it still acts as just a spreadsheet.  At that point the macros could be ignored, deleted, or updated with new coding for that future version of Excel.  The same goes for LibreOffice/OpenOffice Calc; until I can adapt the macro code to work in it, the spreadsheet still acts as a spreadsheet.

[calzap]

I’ve inserted audio files into Excel spreadsheets.  Put the cursor on an empty cell.  Click on the Text group, then Object, then Create from File.  Browse for the audio file.  Never tried to make a macro play it.  Excel uses Windows Media Player to actually play the sound.  Not sure if this can be changed.

Mike

[free_electron]

I Keep it simple. Every storage bin has a letter , every bag in the bin  a number. Fill sequential. don't try to "insert" things in the middle like adding a TTL ic and having to shift all the parts. That is endless effort.

Since these are all components i use/used in some sort of design that means there is a symbol and footprint in the cad library.
My cad library has a field "LOCATION". i simply put the bin/bag in there.

RES,SMD,1K,1%,100ppm,0603   location field set to J107 . i know where to go.

If i print a BOM i simply include the location field and i immediately know where the parts are.
I can make labels by simply printing the description field.

Since my library is a dblib i have it as a ACCESS database so i can do anything i want. Even if a new part does not have a symbol/footprint yet ( they just arrived from supplier) i can quickly add them to the access DB so the data is there even if not populated in the CAD yet. So it works both ways