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.
