A true inventory table?


#1

TLDR: I need to find out how I should set up my inventory. I want it to auto update when I enter or update other records in the base. I want to specify how many of each part every record under the “Models” tab uses. And when I add a record to the “Guitars” tab, which is based on the “Models” tab, I want to specify yet another set of options that auto updates the inventory. The inventory should also be updated when I specify spare parts in the “Service & Support” tab. Of course, the records in the “Models”, “Guitars” and “Service & Support” tables need to be updated too – if only I knew how!

The case:
I’m starting a small company that will build and sell guitars I design. I like Airtable and I’ve got a pretty good understanding of how to work with it. But there are a few things I’m trying to get a grip on.

I’ve created two bases. The first is called ”Guitars” and the second is called ”Marketing”. The Marketing base is nothing more than a planner tool. The Guitars base on the other hand, is pretty complex.

I need to keep a lot of information tied together to make this a long-lasting choice of software. I have set up these tabs within the Guitars base:

Guitars
Whenever a new build it started it gets into this tab. If you order one Model X I will enter a new record here. I will pick a model (linked from next tab) and it will generate a serial number. I will link to the Customers tab too, where I’ll put your personal information. I will also enter estimated start and finish dates (connected to Zapier, which marks this in my calendar – super sweet!) and a checkbox for “Finished” so I can sort the views depending on if I’m looking for ongoing builds or not. There are off course other fields too, like some specs that are not set by the model. Could be colors, pickups, neck wood.

Models
This is where I set up the base models that the Guitars tab is based on. Let’s say I have 5 models. When I enter a new record in the Guitars tab I pick one of these to start with.

Sales
When you call me and tell me that you want a Model X in Surf green, I first go to the Guitars tab, enter a new record, pick a model from Models tab, enter your specs in my “Options” field within the Guitars tab. Then I go to the Customers tab (next) and enter your data. Then I go to the Sales tab and I link to the record in the Guitars tab and to the record in the Customers tab. I enter a few other stuff, like payment method, notes, order date. I’ve got automated order numbers going for me.

Customers
This is a record of all clients who have ever owned one of my guitars. Apart from sales, t’s mostly used for warranty and service tickets. But I can also use this as a base for newsletters and special offers (a view showing only the records containing an email address and a checked box for ‘Newsletter’).

Suppliers
I’ve got a select number of quality suppliers in one tab. It’s linked to Inventory and Orders.

Inventory
This is a tricky one, that I would love to find a solution to. My dream scenario is this:
When I create a new model in the Models tab, I would enter all the standard parts (8 pcs of [screw type X], 4 pcs of [screw type Y], 1 pcs of [Body wood species] etc). Of course, this should not affect the inventory. But when I enter a record in the Guitars tab and pick a model – that’s when the inventory should be updated. And as I enter this record and specify things like color, pickups etc, this should also update the inventory.
The Inventory tab should also be linked to the Service and Support tab, so it updates whenever I need to repair something.

Orders
The Orders tab is my orders from my suppliers, to update the Inventory. But I think it would be too complicated to auto update the Inventory from this tab. So this is more of a practical thing for me, to keep track on what I have ordered/received.

Service & support
Whenever a guitar breaks or need adjustment I will keep track of it here. It will link to Guitars, where I pick the specific guitar I’m working on. It will also link to Customers, and Inventory. Now, everything works fine – except that I want the Inventory to be updated when I add a part in the Service record.

Drawings
This is simple. I just link to Model, and then I’ve got a number of attachment fields.


#2

This is my current Inventory layout