Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 04, 2021 12:37 PM
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:
Table 2 (current) We also have another table that has raw sales data imported each day with the following columns:
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.
Dec 13, 2021 06:10 PM
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?