I have created a base to track various components of our commercial kitchen operation.
I have bases for Menu Items, Prep Items and Ingredients.
I have created various columns for the quantity of ingredients for each dish and need to roll them up into the quantity of ingredients in total for a given period, using sales data.
Because there may be multiple meats involved in a dish, I have created separate groups of columns with Qty Meat 1, Single Select Meat 1 and a formula column that multiplies Qty by average weekly sales. I need to roll this up into the meat table so that we can calculate the total quantity of each meat we use per week using a formula to sum the four columns for each meat.
This all works fine for the first meat, and works fine for the initial calculations for the remaining columns (to Meat 4).
However, when I roll up these columns to calculate the total, the second, third and fourth columns are grabbing the Meat single-select from Meat 1. The total quantities for those columns is correct, it is just attributing it to the wrong meat when it is rolled up.
The easiest one to see is the Fettucini Marinara that has 40g each of calamari, prawns, scallops and mussels. Calamari is the meat in the single-select Meat 1 column, but there is 220g calculated in each of the four columns in the roll-up, and nothing for the other three meats. If I change that first column to mussels, then those four lots of 220g is attributed to mussels instead.
How do I force the roll-up to look at the Meat 2 column (or 3 or 4 as the case may be) instead of the Meat 1 column?
Link to base:
https://airtable.com/appiScRMccubMdPE0/shrBlmV9nbjZI1aVO
