Inventory System for Technicians

Hello, have you considered using forms instead of uploading CSVs? That would help accomplish the “locking” mechanism after it is confirmed and submitted. This would also eliminate any inconsistencies with the Excel files, and is easier for non-technical users to complete. We do something like this weekly with our field technicians, and all inventory is tracked in AirTable rather than spreadsheets. How many items are they typically inventorying? 

Hi,

Yeah—I’d just copy and paste that from Excel anyway. The Excel file—or maybe it’ll even be a PDF—comes directly from the SAP system. There will be around 200 items per technician.

What are the columns in the Excel file? 

It's a mix of numbers and text. That's generally not a problem. I was just concerned with the structure and the relationships between the tables. Right now, I’m thinking of two tables: Technicians (synced), then the items to be counted, and the inventory itself (linked to technicians and the items assigned to them). Then a view that displays the data (filtered by Airtable user). When the technician clicks a button in this view, the “done” flag is set for all entries containing a counted value, and they disappear from the view.

This seems like a good table setup. You can create an automation that when a record is created (uploaded) it’s tagged to the technician based on their user account. The names in your item table should match your SAP exactly, so they auto-link upon upload (otherwise you’d need to incorporate that into your first automation as well).

Regarding the visibility, I recommend making a checkbox field “Reviewed” that is not surfaced on the interface. Once they’ve inputted all the information you need, you can make an automation (triggered by interface button) that looks for all “unreviewed” entries tagged to the user who took action, and update them to “reviewed”. Use an interface filter so these leave the page.

Thanks for your input. It confirms that I'm on the right track.

Hm yeah, your idea with the automation + checkbox to update it sounds good.  So the automation would look for all the Inventory items tied to the user who triggered the automation, is that right?

I’m curious how the technicians are doing data entry.  Will they all have paid seats?  Is the intention for them to be confirming line items individually?  

I was thinking if cost was an issue you could potentially give them a form to confirm the entire inventory / put in notes to say what was missing etc instead

---

I was also thinking about whether the inventory list changes every year.  If not, I’m wondering if there’s value in creating a Vehicle <> Inventory system if you’re doing individual item confirmation (although, after typing out everything this might be more trouble than it’s worth given it’s annual?)

Steps:

1. Create a ‘Vehicles’ table
2. Create an ‘Inventory Template’ table
3. Link the Inventory Template records to the Vehicle 
4. Create a ‘Vehicle <> Technician’ table
   1. Each record here will represent a single year’s link between a technician and a vehicle.  This will allow us to filter the view by the logged in technician
5. Create a new table called ‘Inventory tracking’
6. Create an automation that’ll help us create all the Inventory Template records so that the technician can confirm each item

Good morning,

The Technician table is sufficient as an index, since the vehicle information is subject to change. The inventory list is also updated every year. In addition, the data is exported upon completion and must be entered into the SAP system. This means the list can simply be deleted from Airtable once the process is complete. All technicians have full access to Airtable and can enter the data directly into it.