Creating An Inventory Tracking System

[tbradley22]

Hi folks,

This is my first post on the forum as I'm just getting started with AirTable. 

I've set up a sample AirTable project to explain what I'm looking to https://airtable.com/appEMeJBL3Crynbxm/shrghnubXMunVieh1

• There's an "Orders" table with all of the orders we receive from customers, and the parts that we need to fulfill those orders. 
• There's an "Inventory" table with those exact same parts listed as records/rows rather than fields/columns. I want to sum all the parts required for open orders and display them in the Inventory Table "Required For Orders" field. Then it's just a simple calculation to get a read out on whether or not I have enough parts in inventory to fulfill the order. 

My actual process has about 60 parts and hundreds of orders, but if I can figure out how to do the above it should be the same to set it up. 

Thanks,

Ted

[tbradley22]

I received this solution from an AirTable developer via email: 

Hmm, while what you want is possible, this is also not really how Airtable is intended to be used and so I would generally advise against it. 

That said, assuming that the total types of "Rings" and "Hard Parts" rarely changes, then this can be done by:

1. Linking every record in "Orders" to every record in "Inventory Synced"

2. For each inventory item, create a single rollup field in "Inventory Synced" that will have a formula of "SUM(values)" and a conditional to only include Orders where "Parts Binned" is X

3. Finally, create the `Required Parts for Non-Binned & Non-Assigned Projects` field as a formula field with the following format, where you would have to add in the inventory item name followed by the rollup field:

SWITCH(

Name,

"12 Bisque Swag", {12 Bisque Swag Rollup (from Orders)},

"12 Bisque C/N", {12 Bisque C/N Rollup (from Orders)}

)

You could use an automation that will trigger whenever a new order gets created to link all your inventory records to that new order, and so everything will be automated.  This would be done via a "Find record", "Update record" action.  Note that "Find record" can only find a maximum of 100 records, so if you have more than 100 inventory records you'll need to add another "Find record" set of steps; simple enough to do

I've built this for you here and you can duplicate it to look at the setup, formulas etc

----

I would like to again advise against the above, however.  Doing this technically works, but makes it somewhat clunky to deal with the data and will probably negatively impact features you might want to add in the future.  The advised way to do this would be with a junction table, where each record represents a single inventory item, the order it's tied to, and the quantity required for that order.

We could then do a rollup quite simply, and it's much much easier to set up and maintain as well.

I've set this up here for you to check out