I am trying to create a doc where we can keep track of the number of pounds lost at our roastery, then use the data from that table to help our head roaster know when he needs to reorder more beans. I have this table set up here he uses as he roasts each batch. The numbers are generated from a form our head roaster fills out as he’s roasting.
As you can see from the table above, as he roasts in batches, he keeps track of the Bean Type, the pounds before he roasts, the pounds after he roasts, the pounds lost, then a percentage that is lost during roasting. There are two bean types here, and we have one entry for one bean type and multiple entries for another.
What I’m trying to do is take the data from the above table and put it into a new table that has a column titled “lbs Used” and a “Bean Type” column. The Bean Type column is, of course, the same values that are in the table above. What I need to do is take the SUM of the values from the “lbs. Before” column from the above screenshot and put them in the “lbs. Used” column, but it needs to be specific to the Bean Type. So, from the above screenshot, the second table should read “Brazil Lua Roxa” and the “lbs Used” should calculate to 53.5, while the Colombia Excelsa Cauca should calculate to “11.5”.
Something like this:
Bean Type | Lbs Received | Lbs Used | Lbs in Stock
Brazil LR | 160 | 53.5 | 106.5
Colo. EC. | 160 | 11.5 | 148.5
I’m very new to Coda, so new that this is my first question, and I can’t have more than one screenshot, and I’m having a hard time figuring out a formula for this. The goal of this would be to have our head roaster know when he needs to order more beans, based on his usage from the first table. This will be calculated from subtracting the total pounds of coffee used from the first table (the screenshot) from the total pounds of beans we have here the roastery, and when this number is below a certain threshold, he will be notified that these beans need to be ordered.
The best I could do was get the sum of “lbs Before” from the first table to apply to all rows in the “lbs Used” column of the second table, but I’m not sure how to get it to apply to the row that is specific to the bean type from the first table, if that makes sense.
Any help would be appreciated.