We have SKU's that are bundles and comprise of a combination of other SKU's.
So an SKU can be sold individually and also be sold in a bundle.
Example -
SKU-1 is a bundle comprising of SKU-2 X 1 unit, SKU-3 x 1 unit and SKU-4 x 2 units.
We will be pushing sales data into Airtable that will give us monthly sales by sku
We would like to see sales data of all SKUS by accounting for the components in each bundle.
Sample base here.
Products Table lists all products. Any SKU which is a bundle is marked as a bundle
Sales Table shows the sales for each SKU by month
Bundle table shows all components of each Bundle and the quantity of each component in the bundle.
(Multiple bundles can have the same SKU)
So SKU-2 should show it's individual sales as a product (31 in June) + it's sales as a component of SKU-1 (12 in June)
In the same way SKU-4 would show 76 + 12 X 2 ( as SKU-1 consists of 2 units of SKU-4)
How could we approach this please?