Trouble with Linked Records and Quantities

[Once_Upon_A_Tim]

Hi folks! I have run into an issue that I can’t quite figure out. I have a base where I want to track inventory and profitability for our Event Decor company. The base has a table for Inventory items and a table for Events. On the inventory table I have a quantity field that is used to keep track of how many of a certain item we have in stock. In addition, this table tracks what events the items have been used for and also tracks the profitability of each inventory item.

The problem is when I have an inventory item with multiple quantities. I have no way to specify the quantity used for their event in the events table because every inventory item is displayed in 1 cell. This means that if we have two items in stock but the event only uses 1, I have no way to input that and then perform calculations on that.

[JonathanBowen]

Hi @Once_Upon_A_Time_Eve - I think amending your base structure might be the answer here. Based on your description, I would design this base with an inventory table, an events table and with an “event inventory” table that links the two:

Each item used at an event gets its own record, so you can perform calculations on each record if you need to. Values or calculated values on the Event Inventory table can be rolled up to the Events table if needed too, so if, for example, you calculated profitability on each item at each event, you can roll this up to a total inventory profit on the Event table.

JB

[Once_Upon_A_Tim]

Thank you for your reply. I spent most of the day yesterday trying to figure this out and I ended up with something very similar to what you posted. Basically a junction table. However, now I have to enter the information across all the tables. For myself, this isn’t an issue, but for my employees, this is going to cause some problems. Optimally, is there a way to make it so that we only have to enter information on two different tables. For example, Inventory Items in Inventory Table and then Event Inventory in the Event Inventory Table? With your setup it looks like we would have to enter into each table for every event.

[JonathanBowen]

Hi @Once_Upon_A_Time_Eve - obviously, I don’t know what your exact workflow is but broadly, yes, this kind of set-up would involve entries in all 3 tables. But, in my mind, the majority of the work would be done in events-inventory. The event is set up once in the events table and this single record is referenced multiple times in the event-inventory table. I don’t know if you have new inventory for each event or if inventory is reused across events, but if the latter, then inventory might be relatively static. I can’t really think of a simpler table structure that gives you what you need without coming up against the original constraints (or similar). Generally, this is the sort of base design I would employ for these kinds of situations.

JB

[Once_Upon_A_Tim]

Thanks again for your reply. Still being somewhat new to Airtable, I just learned that you can add a new record from another table as long as that table has a link to that record. So, this solves those problems. I setup a base from what you originally replied and it works great! Thanks for all of your valuable help!