Aggregate Table

1

I have a table of Recipe Ingredients with columns (Recipe, Ingredient, Amount). I want to create a Shopping List table so that when a User picks out Recipes, it aggregates and sums the Amount by Ingredient.

For example:

image
image.png663×713 18.2 KB

2

Hey @Jennifer_Lastimosa,

here is an example how to do that:

The main formula in the ‘total column’ is:

=Recipes.Filter(Ingredients=thisRow.Name).Amount.sum()

This will

  • search your Recipe table by all occurings of the Ingredient in the current row
  • gets the amount of it
  • sums all the amounts

Another thing I’ve implemented is the automatic ingredient picker. It is the formula in the ‘Name’ column.

Recipes.Ingredients.Unique().nth(thisRow.ID)

This formula automatically picks one ingredient at a time and puts it into the row of the ingredients table. All you need is an continous id (I used RowId – Be aware that this may stop working as soon as you manually delete one of the rows of this table!)
This formula

  • Gets all ingredients from the recipe table (Recipes.Ingredients)
  • Removes double entries (with unique())
  • Picks the one that is on the position of the current row (nth(thisRow.id))

Hope that helps you solving your challenge.
Best,
Daniel

4 Likes
3

Sorry for the late response, but thank you so much for this!

1 Like