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.