Considering Airtable for Equipment Usage Log

[LincolnMarsh]

TL;DR: will airtable let me build a usage log?

I’d like to scan barcodes into a Use record, then add all those barcodes to a linked field, so that bringing up the individual gear record, shows each use (a list of dates).

I’d like to do that without needing to scroll through all 200+ pieces of gear, each time I try to add a record to the linked field. And I’d like to do it without needing to add a record for each piece of gear on each use, because that seems wasteful.

Perhaps I’m just misunderstanding the power/use of linked fields and there’s another way to do this.

If there’s a way to Share the base I’ve been playing with, let me know

Here’s my situation: I’ve been tasked with selecting an electronic version of our equipment usage and inspection logging system for our Ropes Course. Our risk management association is also telling us that each piece of equipment needs to have individually identifying marks on it, so I was really hoping to do this with barcodes, but that’s getting ahead of things.

There’s a couple hundred pieces of gear (my “records,” I would assume). On a given day, I would need to be able to edit/add a date to the records of, say, 60 or 70 of these items, depending on what part of the course was being used (these 16 harnesses, these 20 helmets, these two ropes, etc.; this is why scanning a barcode while we put it away would be great).

Result would be the ability to show a log of all uses for each piece of gear, on request, so I’d want to be adding each date to a text field on the record, I think?

OR linking a Uses field to some other table of “Uses” maybe? I fear that a Uses table would turn into a very Record-heavy prospect though, with 50-60 pieces of gear being used on a daily basis? Unless there’s a way to log one day’s uses to one record? A Use form that creates a record for the Date with enough empty fields to cover the maximum potential gear that could be used in a day?

I see that if it’s linked, when I go to add a Use record, I can “+add” and scroll/choose from all the records in the Gear table, but I think it would be easier if I could just scan the tags I’m going to have to add to all our gear anyway.

If the primary name in the Gear table is the barcode, and I have a field for each piece of gear used, could I somehow get all the scanned barcodes arrayjoined into that linked field or something?

[Y_K]

The data structure wouldn’t be too difficult to do in Airtable but I think it would be a pain to use on a daily basis especially if different people are responsible for entering the data into Airtable.

You could have, like you said, a Gears table with one piece of gear per row. Then the Usage table with Date / Staff fields and a field linking to the Gears table. But inputting the data would be tedious on PC or mobile. The mobile app does support inputting/searching with barcode with camera but it’s not designed for bulk inputs. For PC I guess you could program the barcode scanner to add a comma and a space after each number, so you get a comma-separated list which you can copy/paste from text editor into Airtable, you will need an external editor (Notepad) because it’s not easy to add multiple entries to a field fast directly in Airtable. If the barcode number matches the Gears name, Airtable will add the correct record.

Date | Staff | Gears
3/3/19 11:30 | John | 10001, 10002

If your gears are named 10001 and 10002, simply pasting “10001, 10002” into the Gears field will match them up with the correct gear records.

I have done something similar with a client who tracks equipment usage data, but not at this scale (only 1-2 entries per day). I assume the gears could be used outdoor too? And collecting each gear at the end of the session needs to be done quickly with lots of people eagar to leave the class? I think this will be where the bottleneck is. Depending on how comfortable your people are with technology, it may work to have a list of all the equipment numbers printed on a sheet of paper, then crossing them out as you check in the gears. (like crossing off a guest list.)

[W_Vann_Hall]

It may seem a bizarre suggestion at first glance, but I think if you took my Wardrobe Manager base from Airtable Universe, dike out the inapplicable parts,¹ tweak a couple almost-there sections (‘last worn’ and the roll up of garments into outfits) — and, I guess, rename the main object tables ([Garments], [Outfits]) to something more appropriate — you’ll have the bulk of your intended base. Alternatively, you could open it in a second browser tab and use it as scaffolding for your own development. If you want a feel for its underlying philosophy and architecture without having to step through the base, field by field, skip to the last table, [Documentation]; the first 30 pages of the ‘Wardrobe Manager User’s Guide’ found there provides just such an overview.

Beneath the skin, WM is an inventory manager and usage tracker, with the added ability to ‘bubble up’ the status of individual components to reflect their impact on the availability of larger assemblies or collections of components. It’s based on an exception management approach that considers on-hand and in a usable condition to be the normal, default state, with any deviation from this norm representing an abnormal situation to be managed back into compliance. Much of its processing is driven by the item’s current location, with views and alerts designed to flag items physically located outside the collection.²

At first glance, your desired work-flow seemed congruent with WM’s. (Let’s face it: Outside of quantum mechanics, there are only so many ways in which to express an object’s location, most of which being some variation on either ‘here’ or ‘not here.’) While WM’s has a different motive for tracking item usage than your log’s, the mechanics for doing so should be compatible. (Frankly, it’s been long enough since I wrote the base, I can’t confidently address the usage routines’ suitability for tracking high-use items, but I can’t imagine they wouldn’t be…)

Anyway, take a look; with luck, you may be able to appropriate large chunks of it.

---

1. E.g., the routines that generate a standardized, locale-appropriate size based on dimensional measurements.
2. Although it contains the rudiments of a more-detailed inventory control system, in processing Wardrobe Manager only knows of a single place considered ‘in-hand,’ with everywhere else indicating a location off-site. In contrast, your usage log seemingly differentiates among several possible in-house locations as well as [presumably] external ones; some modification of the original code might be indicated here.

[LincolnMarsh]

Thanks, The bottleneck isn’t really a concern, as we actually would like the process of putting the gear away to slow down and include a short post-use inspection.

I’m still hung up on the technical aspect of getting a field I can scan into “linked” … or getting the data from fields I can scan into, concatenated into a linked field.

I appreciate the idea of a sheet of paper, but that’s what we’re moving away from. We have a vague paper log now that indicates that we used “the normal loadout” of gear, but since we’re going to more detailed usage, we’d rather not waste all that paper. Paperless program-wide, someday, is the hope.

[LincolnMarsh]

Wow. That is impressive, and much, much more than I need. We will obviously have a bunch of other information attached to this gear, and your views have opened my eyes to being able to see a certain shed’s worth of gear, or all the helmets, or all the carabiners… and I could see some benefit in “bundling” some of our staff rescue gear into an “outfit” of sorts.

My main concern is the best match to my “usage” need is “worn” and that seems to only have one date. It’s not really a rental-type situation where we’re so concerned about In or Out or Where at a given moment, as much as we’d like to see:
Used: 2019-02-02,2019-02-05,2019-02-26,etc.

[W_Vann_Hall]

I also thought my ‘worn’ was most similar to your ‘used’ — not just similar, in fact, but fundamentally equivalent.

If I remember correctly (and, admittedly, it’s probably even money on whether I do), each [Worn] record represents a single occasion on which a garment or outfit was worn. Since it’s occasion-specific, it only requires a single date field; multiple dates are supported by having multiple records linked to the garment or outfit.

The important clause to note here is ‘or outfit.’ I was thinking your ‘outfits’ might encompass the full set of gear required to train a class of such-and-such size on a specific section of the course. A linked '[Worn](or, rather,[Used]`) record could then be created to document an instance of use for the entire bundle of gear. (Admittedly, other than simply breaking down outfits into component garments, there isn’t much existing garment-related ‘worn’ functionality, so you get to implement your own. It’s a target-rich environment!)

I assume the driver for much of this item-specific logging is the need to build an after-the-fact evidentiary trail following an accident on the course, so tracking group-level usage may not be enough for the Risk Management guys. (I didn’t mean for that to sound so cynical; I’m sure it’s an honest desire to examine an item’s history to understand if past use could have inadvertently damaged its integrity.) (I just made it worse, didn’t I? I think I’ll quit while I’m at least this far ahead…) Regardless, you may need to track use at a finer level of granularity: Who used the item? How much did he or she weigh at the time? Where on the course was it used? When was it last inspected? tested? for fraying? cutting? damage? elasticity? You may find it’s more appropriate to treat ‘in use’ as a location with a number of possible modifiers.

Better yet, maybe what you need is sort of an outfit/worn hybrid: For each instance of use, you would create a BundleUse record, containing information on the situations of the use itself — course section, individuals involved, assumed skill level, weather conditions, and the like — plus a linked-record {Gear} field allowing you to itemize the gear involved. Finally, a second linked record ({GearExceptions}) would allow the documenting of anything out of the ordinary observed or experienced concerning an item of gear. Such an approach would keep you from having to generate a per-item usage record while still allowing capture of potentially significant information that would most likely be lost in a system based on more-frequent, more-detailed, less-relevant tracking.

I think I just managed to address precisely none of your question, while at the same time opening countless new vistas of concern. Looks like my work here is done!

[LincolnMarsh]

I mean, if you want to dig deep…
-Risk Management is requiring the individual labeling and “tracking” of equipment, not sure how specifically they’ve defined tracking.
-Our official builder/inspector does a yearly required inspection of the course and all the gear. During that inspection, they ask to see/implement usage logs for equipment. They were OK with “We used our standard load out of gear for this section of the course” on one log, and “We used our belay ropes this many times” on a separate log, but I’m sure they would not be opposed to notes on individual gear when there are “incidents” or heavy use.
-We do an in-house inspection of the entire course and all the gear monthly.
-Before using and putting away each piece of gear, there is supposed to be a pre-use and post-use inspection, perhaps not as thorough as the monthly or yearly, but a visual and tactile inspection and confirmation that all moving parts operate correctly.

So, yeah, there’s room for detail, but generally, we’re trained enough to know if it’s OK to use, Not OK to use, or Put it aside until we get our inspector to look at it. A Status and/or Notes field will get added, certainly.

To simplify the Where variables, very little of our gear is used in more than one place. If it’s a climbing tower harness, that’s where it stays. If it’s a ropes course helmet, that’s where it stays.

I certainly appreciate the discourse on the structure of the data, unfortunately I’m still at a loss on how to get that list of dates to show up on the individual piece of gear without manually selecting each Gear record in the linked field on my Use record. That wouldn’t be impossible, but not ideal. A lot of our gear already has QR codes on it which includes a serial number. Airtable is able to read those codes into a barcode field… now if I could just use that somehow to add to a linked field.

[LincolnMarsh]

I’m going to bump this one more time and try to simplify my ask in the original post.
I’d like to scan barcodes into a Use record, then add all those barcodes to a linked field, so that bringing up the individual gear record, shows each use (a list of dates).

I’d like to do that without needing to scroll through all 200+ pieces of gear, each time I try to add a record to the linked field. And I’d like to do it without needing to add a record for each piece of gear on each use, because that seems wasteful.

Perhaps I’m just misunderstanding the power/use of linked fields and there’s another way to do this.

If there’s a way to Share the base I’ve been playing with, let me know

[LincolnMarsh]

Well, thanks for trying… I was excited by Airtable because it was recommended by another course for keeping track of forms AND it had this barcode reading ability that I thought would solve another problem, but it seems like it just can’t do what I need?

[Pearl_Pospiech]

You can share the base you’ve been playing with, or the individual view.