Why is my formula not filtering out multiple conditions?

I’m trying to make a tool that will allow me to select recipes I want to cook for the week + servings I want of each recipe and populate a shopping list with all ingredients I need for those recipes.

I have a table: ingredients_recipes that stores all ingredients needed for each recipe + the quantity of each those ingredients. I then do some fancy stuff that allows me to select the number of servings that I would like for a particular recipe. Using a formula, that results in the column total_quantity on table ingredients_recipes which tells me the total quantity of a given ingredient I need for each recipe. ALL good. No problems there.

Next step: build the shopping list.
Because some ingredients will be repeated between recipes, I have a separate table (shopping_list) at the grain of ingredient. I’m using a formula to sum up the total quantity of a single ingredient needed from the ingredients_recipes table to populate the total_quantity_to_buy column on the shopping_list table.

**ingredients_recipes**.filter(**ingredient**=thisRow).**total_quantity**.Sum()

Here’s the catch. The table ingredients_recipes stores ALL the ingredients for all the potential recipes I could cook and their quantities. Currently the above formula will sum the total needed ingredients across ALL recipes, not just the ones I’d like to cook this week. On ingredients_recipes I have a column called planned_day_meal. If that column = “Unassigned,” that means I WON’T be cooking the meal that week and that ingredient row shouldn’t be included in the shopping_list table calculation. I thought using the below filter addition would allow me to exclude ingredient quantities that are currently categorized as “unassigned” but the value of total_quantity_to_buy doesn’t change when I add this additional argument:

**ingredients_recipes**.filter(**ingredient**=thisRow && **planned_day_meal** != "Unassigned").**total_quantity**.Sum()

Essentially without the qualifier of planned_day_meal != “Unassigned” , the value for total_quantity_to_buy for the chicken breasts row is 6.75. When I add in the qualifier of planned_day_meal != “Unassigned” I would expect the value for total_quantity_to_buy to go down to 6 but it stays the same (6.75).

Not sure what I’m doing wrong here. I’ve gone through a million coda threads to try and find a similar solution with no luck.

Hello @Mallory_Reese-Bagley!
Thanks for the thorough write up of your problem!

The first thing you need to know is that when you use the ‘=’ for it to be true, both sides of the comparison have to be of the same Type, in your formula, in the part where you compare

[planned_day_meal] != "Unassigned"
The left side is a row reference from another table and the right side is just text

To make this work you’d either change the left side to text or change the right side to a row reference.
I recommend the latter, change the right side to a row reference, and to do this start by typing @ and start writing Unassigned, you will get the reference down in the suggestions and select it from there.

I leave some articles that may help you

1 Like