Different formula for each row

Ask for Help
1

I’m new to Glide and need some assistance. Can we perform different calculations on different rows within the same column?

For example, I’m baking various items with ingredients like flour, egg, sugar, salt, baking soda, baking powder, brown sugar, cream, etc.

Is it possible to set different formulas for different baked items and automatically deduct from the ingredient inventory?

For instance, for baking bread, the formula would be:

  • flour (-1000g)
  • sugar (-100g)
  • brown sugar (10% of the final baked bread weight)

For baking cake, the formula would be:

  • egg (-1000g)
  • salt (-10g)
  • cream (10% of the final baked cake weight)

Since I use different ingredients and formulas for each baked item, is there a way for Glide to apply these fixed formulas based on the item being baked and automatically calculate and deduct the ingredients from the inventory each time? Ideally, I’d just input the amount of baked items, and the rest happens automatically.

Thank you for your help!

2

This is possible using conditional column. So basically, create separate columns for all formulas. Then create final column (IF THEN ELSE), and use conditions accordingly. This approach is not suitable when you have lot of formulas for one column.

Alternatively, you may use Excel Formula column. However, I am not sure how effectively it will work in your case.

3

The biggest problem to me lies in the fact that brown sugar and cream rely on the final baked cake weight, which is dynamic.

I would have a Recipe table, an Ingredients table and a Recipe Ingredients table that looks like this:

Recipe ID Recipe Name
R001 Bread
R002 Cake
Ingredient ID Ingredient Name
I001 Flour
I002 Sugar
I003 Brown sugar
I004 Egg
I005 Salt
I006 Cream

That will also be the table where you calculate the live amount of ingredients left. Next:

Recipe ID Ingredient ID Fixed amount Percentage amount
R001 I001 (Flour) 1000g
R001 I002 (Sugar) 100g
R001 I003 (Brown sugar) 10%

Then, have a Bakes table and a Bake Logs table. The Bake Logs table would need to be a Make.com scenario or a Call API action that logs multiple rows from the Recipe x Ingredients table every time a new Bake is added, based on its Recipe:

Bakes table

Bake ID Recipe ID Final weight
B001 R001 1000g
B002 R001 500g

Bake Logs table: I choose to log the fixed amount and percentage amount as numbers instead of lookups since you might want to change your recipes down the line, and this approach means past logs wouldn’t be affected when you change the recipe.

Bake ID Ingredient ID Fixed amount Percentage amount Bake weight (Lookup from a Bake ID relation) Percentage calculation Final amount
B001 I001 1000g 1000g 1000g
B001 I002 100g 1000g 100g
B001 I003 10% 1000g 100g 100g

Percentage calculation = Bake weight * Percentage amount
Final amount = Percentage calculation if Percentage amount is not empty, else return the Fixed amount

You would then use a rollup on that final amount to deduct from your inventory. You would also have another Manual Logs table to log instances where you buy more ingredients for your inventory, or taking some out for other purposes than baking.

Ingredient ID Date Amount Notes
I001 29 May, 2024 2000g Purchased at the supermarket
I002 29 May, 2024 -100g For reason A

Then, the live amount of each ingredient should be equal to Rollup amount from Manual Logs minus Rollup amount from Bake Logs table.

3 Likes
4

Thank you ThinhDinh

Another quick question, when we use a quantity column in glide, we can input the amount we need, but is there a way to have multiple units?

For example,
Ingredient. Quantity
Sugar. 100g

Instead of the unit g, can I have multiple units like 1kg / 1 pack/ 1 teaspoon etc

Appreciate for your help.