How do I get the sum of data grouped from one column of one table into the column of another table?

1

Hello,

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.

Screen Shot 2022-10-31 at 11.33.50 AM
Screen Shot 2022-10-31 at 11.33.50 AM1516×722 41 KB

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.

2

Hi Jessie,

Welcome to Coda, I hope that you will have an enjoyable and productive journey!

Please have a look at the below:
Rambling Pete's Quick examples · undefined.

It gives you an example and explanation of the formula to calculate the maximum value per sub-group. There are also other examples in the doc.

Formula used for colMax per topic

[tabAverage Formula].Filter(colTopic=thisRow.colTopic).colNumber.max()

You would need to replace .max() with .sum()
In Column colTopic you would store the bean type.

Let us know if you have further questions.

Regards
Rambling Pete

2 Likes
3

I’ve tried using this several times with no luck. It could be I’m just not understanding how the formulas work. :frowning:

4

It may be helpful to write specifically what I need to copy and paste. I looked at the link you provided, and it’s not exactly what I am trying to do with my tables. I may be misunderstanding your response as well.

5

Figured it out!

Here is what I needed to do. It’s a Reverse Lookup.

[Weight Lost].filter([Bean Type].Contains(thisRow)).[lbs. Before].sum()

2 Likes
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.