Multiple Totals with Multiple Factors


#1

Have built a base with several linked tables and they are working fine but I have a set of excel spreadsheet that I use to create a production schedule that I would like to convert to Airtable. I just can’t figure out how to structure it in Airtable.
Have a Sales Line Item Table in Airtable

       Color    Product    WeekDue      Quantity                       maximum # of Colors is 5
      Red        bowl              3                  10                            maximum # of product types 14
      Blue        Cup              4                   20
     Yellow     plate              2                  12

Need a total production plan by Color by week - need to mix the plastic by a color batch

Table Needed – Separate for each color

Red Wk1 Wk2 Wk3 Wk3
Product Quantity Quantity Quantity Quantity
cup 12 0 4 25
plate 7 6 14 0
bowl 0 4 5 12
saucer 4 11 0 1
Total 23 21 23 38

The total quantities for the week for each color then need to transfer to another table for additional calculations.

Have it built in Excel but just cannot get my mind to think Airtable structure for this.

Your help would be appreciated.