Inventory, Even in the Woods

I’m an active member of a local SCA branch. The SCA is a registered non-profit and its branches are much like local chapters, so that non-profit status is inherited. Most SCA branches own some amount of movable property – kitchen equipment, tents & pavilions, wall hangings & other decor, and maybe even a trailer to store and haul it all. Where there’s property, there’s an Inventory!

Taking Inventory the Old Way

In 2019, I attended a “taking inventory” session, where the branch’s property inventory records are compared against the actual items, and the records are adjusted to match (and in some cases, items that went into the wrong numbered box are put where they belong). At that time, the inventory records were captured in a spreadsheet file, with one sheet for every box, plus some sheets for “loose items” and items that are being stored by individuals for reasons of easy access or because they can’t be safely stored in the trailer.

One individual was designated to amend the spreadsheet sheets on the fly, in spite of having paper lists printed out for every box or location. It was torturous, and the general mood was that of rushing to get it done because of that bottleneck. I started marking down notes on the printed sheets, and I also took a bunch of photos (none existed).

Packing Up the Old Way

A few months later, I participated in an event (that’s what the stuff in the trailer is used for!) and helped out with the task of putting everything back into the right box so it would all fit back into the trailer. That consisted largely of guessing which box might be the right one, and visually examining the printed inventory sheet for it to confirm. If you didn’t guess right the first time, check a different box! It took a long time and was very chaotic.

There’s Gotta Be a Better Way!

After that, I was determined to find a better way to manage the branch’s property. I looked at a few self-hosted options, but none of them really fit – they were all too complicated. And so, I decided that I’d see if I could come up with a simpler web app that would meet my local branch’s specific needs and make both inventory and event pack-out a better experience.

Initially, I figured I would use PHP and MySQL to accomplish this, as I had prior experience with both. However, at one point I decided to follow a tutorial to see if I could learn how to use the Django framework. It took me a minute to grok the structure, but yeah, I got it!

Pandemic Project

I checked in my first git commit of the project on September 10, 2020. By the beginning of October, I was able to generate a full set of nicely formatted PDF documents – a full item listing, a document for each box, and a list of boxes sorted both A-Z and numerically. I exported the data in csv and spreadsheet format for backup purposes. I had also given it a name – Mobiliaire.

In October 2020, I accomplished a bunch of tasks:

  • put a demo version online
  • implement a simple search function
  • started adding support for images
  • started adding support for capturing images using a RaspberryPi + PiCamera
  • Released versions 1.0 and 1.0.1

At that point, I had an app that more-or-less worked, but nowhere to take it. My attention went elsewhere and I didn’t do much else with it until July 2021.

Support for More Cameras

In July 2021, I worked out how the app could support most USB webcams, added the code for that, and started planning to abandon a Raspberry Pi-specific fork I’d done (because the ‘picamera’ Python package will scream if you try to install it on not-a-RasPi; plus a few other differences).

Instances for Other Inventories

The app turned out to be flexible enough to be used for managing other kinds of inventory. My spouse has a large collectibles collection documented in an instance of the app. I also used another instance to catalog my fabric stash and sewing patterns. Those happened in September 2021. A good thing came of the collectibles inventory – I was asked to add “edit buttons” to the gallery and reports pages, which I did, for that instance.

Event Sighted Ahead!!

An event was scheduled for April 23, 2022. It would be the first event since Jan 2020. I put my focus in high gear and stuck my head back in a Terminal window sometime around April 6th. I put in over 17 hours of work between April 12 and 23rd. (No data for 6-12 April because I didn’t install Wakatime right away, but I was putting in hours nonetheless)

  • I pulled those “edit buttons” over to the main repository and made them optional.
  • I finished rolling the webcam stuff back into the main repo so I could sunset the RasPi fork
  • I released version 1.1 and 1.2, and created a 1.x maintenance branch for Django 3
  • I upgraded to Django 4 and pulled opencv-python to 4.5.5.x
  • I made a lot of cosmetic changes to the front-end, including standardizing the gallery cards
  • If there’s no camera but the optional “photo contact email” constant has been defined, a modal will display info on how to submit a photo by email
  • I finally deployed an instance on a Raspberry Pi with Apache2 and MySql (previously had only been running on the built-in dev server)
  • I got a bit distracted and fiddled with setting up CI in the repository
  • I then broke that instance by adding a “settings” directory in the project settings folder (oops)
  • It’s okay though – I fixed it!
  • I did dress rehearsals the Saturday and Wednesday prior – brought the RasPi, a configured wifi router, and a Chromebook with me to practice and set it up (and showed it to people)

Event Day went pretty well! Early in the day other staff members learned that it was a quick way to learn where a particular tool was stored. Not a single paper list was consulted during re-pack. We were done and ready to close up an hour and a half after the event closed. In the past that could be 9 or 10pm.

What’s Next?

Inventory Day is next up. Not scheduled yet, but hopefully, they give me some time – there’s a mechanism for reporting inventory checks, but it’s not optimal. I need to build the front-end features that make it possible to scroll through a box’s list of items and click checkboxes for each item seen, then click a “submit” button when done.

At that point, it will be possible, given the availability of multiple notebook computers or tablets, to run multiple teams at once. Each team grabs a box, checks the contents, makes notes, submits the form, repacks the box, marks the physical box as “done”, and grabs another box.

This upcoming inventory day is going to be a bit more complicated, though – we also need to photograph most of the items. We need individual photos, or in the case of multiples, a photo of the multiples all together. These photos are important because they are an integral part of the process of repacking. A visual confirmation that the item in your hand is the item in the search results is needed (some similar items can be found in more than one box due to the need to play Tetris and make everything fit).

The last piece will be to label everything that can be without being obvious to guests, preferably via marker or etching. Because Django is Django, the URLs are RESTful, so any Item can be viewed by appending its ID to the end of the items url.

You Made it This Far, and You Still Want One?

You can git clone it from the Mobiliaire repository on Gitlab and give it a spin. Check the Wiki there for some more detailed setup instructions. If you get stuck, please submit an Issue so I’ll know I need to refine the documentation.

What was that About Woods?

Oh, right, the Woods. With a machine loaded up with Mobiliaire + local (vendored) bootstrap, JQuery, and DataTables libraries + an optional wireless router, your event staff can have access to the inventory system independent of whether the event site has Internet access, and independent of whether there’s even good cell phone signal at the event site. All you need from the event site is a working power outlet to plug your gadgets into. Or a generator. Whatever, I don’t know your life.

The database is incredibly easy to transfer from instance to instance with Django’s ‘loaddata’ and ‘dumpdata’ functions. The resulting files make excellent backups.

Image: “Rhuddlan Castle” by Jeff Buck is marked with CC BY-SA 2.0.

Building a Better (and free!) Equipment Inventory – part 2

Since I published my last post on this topic, I have developed the entire application and released version 1.0!

A demo is available at http://cordelya.pythonanywhere.com.

You can grab a copy at http:github.com/Cordelya/mobiliaire. If you are going to run the app on a Raspberry Pi computer, there is a RasPi specific fork available at http:github.com/Cordelya/mobiliaire-raspi.

The main application allows administrators to enter and edit items, boxes, and warehouses individually via a user-authenticated admin site. Admins can also upload data in csv format for easier bulk adding.

The front end displays database information organized into two distinct view types: browse and reports. The browse views display warehouse, box, and item information on tiled cards with associated images (if available) and human-friendly names, descriptions, values, and keywords. You can begin at any level and drill down from warehouse to box to item. The Items browse view includes a keyword filter that allows you to limit displayed cards to items matching the selected keywords.

The report views present the same information in a tabular format and allow exporting of the tabular data to PDF, csv, Excel, or local printer.

The application is built with a lack of Internet access in mind – you can install it on a portable or semi-portable device, transfer the database and static files for your inventory, import the database, and bring it to your event or inventory day where it will function whether or not reliable Internet access is available.

The RasPi fork has built in support for capturing item photos using the Pi Camera – and when you activate it via the “take a photo” link on any item, box, or warehouse detail page, it will automatically name the image file with the associated item, box, or warehouse ID to help the administrator associate the resulting photos with the correct item, box, or warehouse without needing to have an in-depth familiarity with the inventory items.

This application can run on any device that supports Python 3.6 through 3.8. That includes Windows and any device on which you can install Termux or an equivalent terminal application.

Tested on Raspberry Pi, Ubuntu, WSL Ubuntu, and Samsung Galaxy Note 8 (with Termux). If you get it running on another platform let me know!

Submit your tickets at the github repositories.

Building a Better (and Free!) Equipment Inventory

My local SCA branch, like most branches, owns property. The group collectively owns a cargo trailer, kitchen equipment, tournament field equipment, navigation signs, and more. All that *stuff* has to be documented.

Right now, that documentation is in the form of a spreadsheet, with one sheet for each numbered box in the trailer, one sheet for items stored loose in the trailer, and one sheet for each member who is holding items for the group (because they are frequently brought to practice). It is messy and doesn’t facilitate pulling statistics (for example: the total replacement value of all items should the trailer suffer a major catastrophe). It also doesn’t currently produce nice (or uniform) hard copies.

Currently, a hard copy of each sheet is inserted into each corresponding box, allowing event staff to find items and later repack the right items into each box. Finding an item requires leafing through all pages of the full inventory and scanning down each list until the desired item description is found. Repacking works in a similar fashion – it’s chaotic. I think I can improve this process by creating a web application that is portable enough to be installed on a Raspberry Pi board and served up via wifi at a site that has no native wifi available.

Yes, commercial inventory web-apps are available. However, they cost money, are often more complicated than is necessary, and may require server environments that groups don’t have access to. This application will be written in php (and maybe some javascript) with a mySQL database. All of that software is available for free and it isn’t difficult or expensive to spin up a suitable hosting environment.

As I work through creating this application and its database backbone, I am going to use this blog to talk about features I build (or plan to build) to remind myself of those good ideas for later. I will also use it to share this information with other members of my branch.

When the application is functional, I will advertise it within the SCA – first within the East – for other groups to clone and use the base code, which will be hosted at an online git repository.

Right now, today, I am still building out the database. I’m using this as an opportunity to improve my database structuring skills. I spent a little time populating the database with some item records so that when I write my SQL queries for the app, I can test them and get real and meaningful query results.

After today’s data-entry session, I have already identified another wanted table: consumable item use reports. The property list contains consumable items – plastic storage bags, trash bags, soap, sponges, latex exam gloves, bandages, alcohol wipes – you get the idea. When a consumable item is used, a staff member can report that within the application via a button on the item’s information page. The button brings up a form, item ID, name, and description pre-filled, with new entry fields for the quantity used and comments. A later option can be a quick-fill tabular form for entering multiple reports (when the staffers keep track using a paper tally and the entry is done later).

Additionally, some of those items have expiration dates. I want to make a report that lists all items that expire, shown with their expiration dates, optionally by order of expiration date to allow timely replacement of expired consumables. This report should be short since it is composed primarily of items in the First Aid boxes, but it’s also a very important routine maintenance task.

Catch up with me later and if you haven’t seen any posts here about this recently, ping me on Twitter @cordelya.