Calculate totals based on column data across 3 Tables

[Seth_Stevens]

We have created a scenario that I would greatly appreciate help on. The summary of the need is that we have a table that lists conversion data for each of our SKUs at our store (conversion data=if an item is sold in a case pack of 12 bags, then we need to convert the sale of 1 case into a total number bags we need to create…1 case coverts to 12 bags in Airtable Table. We have 75 skus and need to be able to automatically calculate total bags per SKU.

Table 1 (current, This is a reference table and rarely changes) We have a table setup that defines the following:

• SKU in Column A
• the converted number for that SKU in Column B

Table 2 (current) We also have another table that has raw sales data imported each day with the following columns:

• Order Date
• SKU
• Quantity (which, in this case, would be 1 case…hence the need for a conversion process)

Table 3 (proposed) In a final 3rd sheet I would like to have the following display and be filterable based on Date Range (between 2 specific dates) to calculate and summarize the aggregated data:
- SKU (if we have 75 SKUs in Table 1, then there will be 75 rows to match in this Table 3)
- Case Quantity Total: Scans Table 2 and asks “for SKU #1, how many total cases are on order” and then returns that # here. It would do the same for each SKU until all 75 have been assessed and a number returned here next to the appropriate SKU
- Converted QTY (The column would reference the SKU in question, reference Table 1/Column B and would then essential run the formula: SKU Converted # = (Case Quantity Total X (Table 1/Column B) and would return the total number of individual pieces required to fulfill all case orders for that SKU

I hope that makes sense and I greatly appreciate your help.

[Justin_Barrett]

Welcome to the community, @Seth_Stevens! :grinning_face_with_big_eyes: I don’t think that you need a third table for this. With the right setup, I believe that you can get what you want shown in [Table 2], or possibly a custom interface build with Interface Designer.

The key lies in connecting the two tables, specifically linking each record in [Table 2] with its associated SKU in [Table 1]. With that link in place, you could use a rollup field to bring in the number of items per case and do the math to calculate the total number of items in that order.

It all starts with the import process, and that leads to my first question: how does the new data get added to [Table 2] each day? CSV import? An external API that creates new records? Something else?