A true inventory table?

[Henke]

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.

[Henke]

This is my current Inventory layout

Airtable

All - Airtable

Explore the "All" view on Airtable.

[David_Haile]

I’m very interested in converting to AirTable, but it is disappointing that your question was never answered. I’ll keep looking around in the Community. If I find that questions are rarely answered, I’ll add airtable to my blacklist.

[Jon_Kirchman]

Were you able to solve this?

[Henke]

Unfortunately, no. Not yet.

[Kendall_Shortt]

Hey Henke,

Sorry you haven’t gotten the help you needed on this yet. I am going to spend some time this afternoon reviewing your question and hopefully give you some pointers on what to do.

I currently have something similar to this for my project team based on my quick read of your question and Airtable has been tremendous so far in helping us keep up with inventories and project workload.

[W_Vann_Hall]

Here’s an answer that at first will look orthogonal to your problem, but bear with me.

Take a look at my scheduling framework base in Airtable Universe, especially the parts dealing with creation and instancing of templates. (There’s a users guide in PDF format as an attachment in the base’s [Documentation] table. Ignore all the parts dealing with configuring task options; instead, focus on the way processes are defined as being composed of tasks and the way the creation of a new process leads to the auto-creation of component task records.

Note: By ‘auto-creation’ I mean the generation of a new task results in the population of a field containing a list of component tasks. The value of that field is then copy/pasted into a linked-record field, which generates new records for each component task. The copy/paste can be performed manually or via an integration service such as Zapier; subsequent copy/paste actions establish the proper hierarchical linkage between processes, tasks, and subtasks.

Now, imagine instead of processes, the [Templates] table contained templates for various guitar models, and instead of component [Task]s you had, well, component [Component]s. Then, whenever an order came in, you would create a new [Guitar] instance, which would then result in the generation of new records for every [Component] required. You could tie the [Component ] table into your current inventory, to know when you needed to call StewMac.

How you handle customization or options depends on your process flow. Since the framework allows for the creation of ad hoc tasks, you could use the template to define common features for that model and have the sales person customize the guitar through the creation of ad hoc components linked to the [Guitar] instance. Alternatively, with a bit of tweaking you could modify the Process>Task>Subtask structure to support Guitar>ComponentType>Component instance. (Depending on the breadth of your supported options and order volume, you could probably work in code from my conditional logic-ish linked records routines that would allow you to select, for instance, ‘neck pickup’ for component type and 'Seymour Duncan ‘54 Tele’ for component instance; the available options for your second choice would be determined by the value for the initial choice.) Again, the actual records for the component instances (when necessary) would be generated by a manual or automated copy/paste.

You could even combine an inventory mod of the scheduling framework routines with the actual scheduling framework routines, resulting in a hybrid inventory/workflow management system.

Finally, if this sounds like such a system would leave room for necessary components to go unallocated, the framework contains logic that generates alerts whenever a new process [guitar] has been instanced but records for all the included tasks [components] have not been created. And if you include the steps for generating hierarchical links between the guitar<-> component definitions, you can likely use the Org Chart Block to present a graphical representation of necessary components for each model. (While it doesn’t currently support this, the framework is likely expandable to allow similar charts to be created on a per-guitar instance basis… I think.)

Again, this is not the answer you were hoping for, but I believe the basic structure I use in the framework could be readily adapted to support virtually any process or item composed of other tasks or items – that is, almost anything. :winking_face:

I realize this requires something of a leap of logic [and, likely, faith]. If you have any questions, please ask.

[Henke]

Thank you so much for your response. I’ve been visiting this post a few times, but I haven’t had a chance to try it out. I just want you to know that I’m grateful and that I will get back to you when I’ve had the time to try it out. It is, as you say, a leap in logic. So it might take a while before this hard working single parent has the time and inspiration :slightly_smiling_face: