Help

Counting Linked Items from different tables

Topic Labels: Base design
Solved
Jump to Solution
2487 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Omar
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions

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

See Solution in Thread

5 Replies 5
Omar
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

Screenshot 2022-10-21 at 2.51.15 PM

I’ve added it in the example table you linked

Omar
5 - Automation Enthusiast
5 - Automation Enthusiast

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)
image

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

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

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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