Inventory Control

Inventory control is a problem.  Our team stores most of our stuff - that includes robots, parts, tools, raw materials, etc. - in our storage room.  The storage room is actually a boys' bathroom on the sixth floor of the school that one of our coaches had commandeered. This year, when we work on our robot, we don't have a dedicated space.  Everything that comes out of the bathroom must go back at the end of the day.

Although we do have shelves and bins (some of which are actually labeled), we don't actually have a good handle on what parts we have or need until we run out.  When that happens, we desperately "Google" until we find a similar part and order it with expedited shipping.  That expedited shipping is expensive and does add up.  Last year, we spent about $1K on shipping.  Waiting for the parts to come also puts a delay in our build time.

So this year, we are attempting to implement a system to track inventory.  Last year, we tried but started in the middle of Build season and it didn't have resources or tools required to complete it. This time, we have a team member in charge and he designed a new system.  It is not meant to become an overhead.  The tool was built over the summer and now we hope to use it.

The tool - now named Inventory Control System (ICS) - is a set of Google spreadsheets used with QR codes.
The requirements:
- be available to all members at any time whether you are in the lab, the bathroom or home
- have enough information to be useful - picture of the part, where to order from, quantity, price, weight
- integration with the Bill of Materials - or be able to generate one - and have the tool automatically remove those items from inventory

Since you can encode a URL within a QR code, we are using the QR code to launch a Google spreadsheet.  Each bin will have a QR code with a corresponding spreadsheet which will hold the information for the contents of that bin.  There seems to be a limitation in the Google Sheets app where even if you provide the gid# for a specific sheet, it will only open to the first sheet.  Originally, I was going to have a sheet for each bin but because of this limitation, we now have a spreadsheet for each bin.  Separate spreadsheets may lessen contention on a single spreadsheet.

Building a consolidated list of all the items consisted of importing the data from each bin's spreadsheet into a Master spreadsheet.  From there you can have a variety of pivot tables and filters to narrow down the view.  One of the reports shows which items to reorder.

Maintenance of all these sheets (such as adding a bin, adding a location, adding a category) is done via a series of scripts launched by button clicks.

The Bill of Materials is set in a separate spreadsheet.  Here the user interface is similar - fill in some fields and click the button to launch a script which will add the item to the BOM and subtract it from inventory.

Another limitation of Google Sheets is that the images are not displayed.  You can see them in the browser on a PC but not on a tablet or phone.  Therefore, you cannot see the buttons either - so most of the administration will have to be done on a PC.  It also would have been helpful for team members to see what the parts look like when they are searching for a part.

At this point, we are about to train the team members how to use it. The tools have gone through light testing so I'm waiting to hear about bugs as well.   I'll post updates on adoption progress later.