Skip to main content
Solved

Counting Linked Items from different tables

  • October 20, 2022
  • 5 replies
  • 52 views
O

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.

Best answer by TheTimeSavingCo

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?


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

5 replies

O
  • Omar11
  • Author
  • New Participant
  • October 20, 2022

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

TheTimeSavingCo
Forum|alt.badge.img+31

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

Adam, Airtable Consultant - thetimesaving.company
O
  • Omar11
  • Author
  • New Participant
  • October 26, 2022

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
Forum|alt.badge.img+31

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?


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

Adam, Airtable Consultant - thetimesaving.company
O
  • Omar11
  • Author
  • New Participant
  • October 28, 2022

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

TheTimeSavingCo
Terms & ConditionsAccessibility statement