Hello all,

This is a different kind of post compared to what I've written in the past. I'm going to talk about inventory management. I'm not going to talk about it on an industrial scale, but more for hobbyists like me. Some of the stuff I introduce and talk about can probably apply to small tech labs etc, but it is aimed solely at hobbyists.

Introduction

As my home lab started growing, I realized I have just WAY to much stuff to know what is where and how much do I have. As some of you know, I live on Hong Kong, and here space is a premium, in fact, my so called "lab" is actually just half of my living room. And this means I need a lot of drawers and cabinets to handle all my storage needs. That is all well and good, and now I'm at a comfortable place where I have enough storage for all my stuff and a little room for expansion.

The real problem starts here, I may have found a place for everything but it is still a huge task to find anything. I also realized that I come up with a lot my ideas when I'm away from home, so that meant I needed to know what components I have in my lab without being physically present. This made me start thinking about setting up an inventory management system for my self.

As a reference point I have around 300 unique components/items amounting to a total of around 5000 pieces.

Phase 1 - Spreadsheets

I knew I needed a cloud based system so that I have remote access, and the improvisor in me figured that I should just create a spreadsheet. I started with Google Sheets(my goto spreadsheet processor). I started by mapping out all the columns I needed, and at first glance I just needed item_name and location. I then saw that another crucial metric I needed was quantity so that I could know if I had a specific item in stock. With this I started filling out the sheet. I got 6 items in and I realized that the location column just wasnt enough, as I had multiple drawers that had many things in them. So I added a sublocation column and continued adding items.

Around an hour in, I realized that I need to make it easier to filter my components, so added category and subcategory columns, and just like that I continued. When i had around 100 items in my spreadsheet, I decided to do a test run. I started looking for something, here is when I realized my excel skills where just not up to the par, and I could do this way better if I had a database like SQL. This is where I gave up on Google Sheets, and while it's great for basic inventory management, it is just too annoying to do complicated searches(unless you are an expert at spreadsheets).

Phase 2 - Available Solutions

I started with a simple Google search and found StackExchange [questionhttp://electronics.stackexchange.com/questions/107869/software-for-workshop-electronic-components-stock-management asking for exactly what I wanted. I went through the answers and the first possible solution I found was Parts-In-Place and while it looked promising I couldn't quite figure out the licensing restrictions and I wasn't willing to pay for it yet.

I then came across zParts also promising, but I realized it was local and only worked on windows, and it was also immediately sidelined.

I was getting discouraged, but I kept searching, and soon realized none of the available solutions were right for me.

The programmer in me finally woke up and decided to look for something open-source so that I could just extend it for my needs, but quickly enough I realized that most of the solutions were based on old technologies(like PHP) that I didn't want to deal with.

Finally, I just gave in and decided to write my own!

Phase 3 - My Solution

The process started with deciding what to write this piece of software in. I immediately narrowed it down to the web technologies that I knew(RubyOnRails, Python/Jango and Node.JS/Express.js). I also knew i wanted development to be quick and easy and immediately realized that I needed a good ORM system. The ORM that I had most experience with was ActiveRecord, and so familiarity go the better of me and I picked Ruby on Rails.

I then started creating my Database, and using my Google Sheets spreadsheet, I was quickly up and running with just 5 tables - Items, Categories, Subcategories, Locations and Sublocations. I used the rails bootstrap generator to create all the forms and index pages and viola just like that I had a pretty simple inventory management system. I then adden in Wice Grid and just like that all my searching filtering and sorting was taken care of, and in just under 2 hours I had a system that was more than enough for my initial needs. I quickly added in devise authentication and some styling and just like that I was done. One push to Heroku and I had my system raring to go.

Phase 4 - Upgrades and Future

I am now planning on adding the following features:

  • API for Mobile apps and automation
  • Projects table to price out projects and create BOM's
  • Barcode Management

Any other suggestions are welcome!

Source

You can find my project on Github here. Feel free to use and modify it to your pleasure!