Simple Inventory Table - Auto Update


I’m trying to find a way to do this so it’s all automatic. This is probably so easy, but I’m just not getting it.

I have a Locations table, Location1, Location2, Location3.

At Location1, inventory needed is Knives 10, spoons 10, forks 10

At Location2, inventory needed is Hammers 20, nails 20, forks 20

At Location3, inventory needed is Spoons 15, Nails 15, Hammers 15

My thought is that I need an Inventory table of the total number of spoons, forks, etc. As inventory comes in, I want to change that number of “knives” to update it, then, I want to see a view of each location’s needs to see which one is the closest to being complete. These same “knives” could be used at two different locations.

Trying to decide how to hold inventory to fill the larger orders and how to distribute the items among all the locations.

I have a Locations table and Supplies table, how do I get the two to work together so I can distribute more evenly.

Clear as mud, right? Thanks, if you’ve read this far. LOL


You’re pushing Airtable a bit out of it’s comfort zone a bit. But here’s a suggestion (this is hard to do in words).

Table 1 Location
Table 2 Location Inventory
Table 3 Master Inventory
Table 4 Inventory Receipts

Table 1
Probably similar to what you have now
Table 2
Auto increment ID
Insert date
Link to Inventory item
Link to Location
Quantity needed
Table 3
Inventory table with name, Quantity available (sum of table 4) quantity requested (sum of table 2), quantity on hand=quantity avialable-quantity requested
Table 4
ID, item link, date received, quantity

What this wouldn’t account for is locking individual commitments to a specific location.