Counting Linked Items from different tables

[Omar]

Hello!! Hope anyone can shade a light here. Here’s some preliminary information to help you understand what I’m trying to achieve…

I have 3 tables within one base:

1. Inventory (primary field: Product_ID)
2. Packs (primary field:Pack_ID)
3. Sales Orders (primary field: OrderNo)

The “Packs” table holds packs that are made of one or more products from the Inventory table.
The Inventory table holds the individual products used to make packs. I’d like to count the total quantity per item in the inventory table that has been sold, in other words the qty “out” from the inventory. Sales orders are registered in its table and each sale is done by pack.

[Omar]

You can find an example here… Or Does anyone know how I can “attach” an example?

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

[TheTimeSavingCo]

Hmm, if you’re just counting linked items, you could do this via adding a Count field in the Inventory table

I’ve added it in the example table you linked

[Omar]

Thank you @Adam_TheTimeSavingCo , Perhaps I didn’t explain myself clearly. What I’m trying to get to is to know what the stock qty out of the inventory is for each product based on the sales orders. The sales orders are placed by packs and the packs and product relationship is many to many. Does that make sense?
I feels more like an Airtable limitation but I wanted to check if anyone has come across with a requirement like this.

1- Granular data that holds Inventory items (SKUs)

2- Packs (or combos if you like) made out of one or more inventory items

3- Sales orders made out of packs and therefore ivnetory items

How can I know how many items have gone out (due to a sale) from the inventory?

[TheTimeSavingCo · Accepted solution]

Ah okay. For that, you’d put the count field in the Packs table and pull the sum over to the Inventory table via a rollup field with the formula SUM(values). I’ve done that in your base and you should be able to see it there

[Omar]

Thank you so much @Adam_TheTimeSavingCo , that was simple enough :slightly_smiling_face: