Set checkbox to true if current row's values are present in another table

Hello all! I have a Meal Plan table containing info about meals and ingredients required to cook them, and another table with all the ‘Ingredients on hand’. I am just trying to activate a checkbox in the Meal Plan table, if all the ingredients required for a meal are also present in the ‘Ingredients on hand’ table.

I believe this should be a simple formula, but mine does not seem to be working as expected. Any help appreciated, thank you!!

Hi @Abhijit_Mahishi

I recreated your data with :

  • a list of ingredient and a list of meal. in ingredient list i choose in which meal i can use this ingredient. Then with lookup, you have a list of required ingredient in your Meal on Plan
  • I have your ingredients on hand table, which are the available ingredient at home. With lookups it gives you the meal where you can use it

  • Start to create a column “available ingredients” in your meal on plan table to check which available ingredients you have is linked with the current meal

  • Then you just have to compare the column “Required ingredient” and “available ingredient” and set it as checkbox, in the example below i’m ready to cook a sandwich !

Is that ok for you ?

Q.

1 Like

Hi @Abhijit_Mahishi and Welcome to the Community :partying_face: !

The suggestion from @Quentin_Morel is very thoughtful :raised_hands: and I’m not sure if this will work but, just in case :innocent:

Ingredients.Filter([On hand item]).ContainsAll(thisRow.Ingredients)

Assuming you have a table called Ingredients with a checkbox field called [On hand item] (from the screenshot you shared) the formula should take the table Ingredients and ask the Filter() to look for and keep the rows where the CurrentValue.[On hand item] is true (so where the [On hand item] has been checked). This should return a list of rows from the table Ingredients which you can then compare to the list of ingredients in thisRow.Ingredients with ContainsAll() (if it returns true, the checkbox will checked and false otherwise)

As you’re trying to add this formula to a checkbox and a checkbox can only take 2 values (either true or false), you don’t need the if() … It’s implied :blush:

This is really a simple shot in the dark and might not work :innocent:

2 Likes

I think what you need to do is to just convert everything into text before filtering by adding .totext() snippet.

So the formula will end up become: If ([Ingredients].[On hand item?].totext().ContainsAll([thisRow].[Ingredients].totext()), true, false)

I’m sorry but I have to say that this would not work either :innocent:

And here’s why :

From the screenshot shared Ingredients.[On hand item?] returns a list of boolean values (from Coda visual cues/hints at least, it’s apparently a checkbox field)… whereas thisRow.[Ingredients] returns a list of rows from a table…

So you would end up with comparing true, false, true, true, true... to Yellow onion, Tomato paste, Chili powder, ... as text values

But, one way to get the formula to work (as you can only compare apples to apples in Coda to get your formula to work as expected) is to compare a list of rows to another list of rows or a list of text values to another list of text values :blush: … which can be done either by filtering the table Ingredients and find the rows where the field CurrentValue.[On hand item] is True which returns a list of rows and then compare that list to the list of rows in thisRow.Ingredients

And this would probably look like something like this :

Ingredients.Filter([On hand item]).ContainsAll(thisRow.Ingredients)

Or, if you want to compare a list of text values to a list of text values, in this case, you would still need to filter the table Ingredients and find the rows where the field [On hand item] is true then dereference the text field used to store the name of the ingredients (in this case I assume (as it’s all I can do :blush: ) it’s been simply called Ingredient and is probably the Display column of the table Ingredients) … and the same can be done for thisRow.Ingredients :blush:

You don’t need the ToText() if the text value is already stored somewhere in the table, all you need to do is to gather it where it should :blush:

This would probably look like something like this :

Ingredients.Filter([On hand item]).Ingredient.ContainsAll(thisRow.Ingredients.Ingredient)

And as the whole ContainsAll() formula returns either true or false (depending on if the condition is met or not), you don’t need the If() … The checkbox will take the value of what ContainsAll() returns :blush:

Just to illustrate, here’s a quick sample …

There are 2 tables … Items storing items and indicating if an item is on hand or not and the table Table where I would like to know if it my “tests” can be done or not depending if I have all the items on hand or not … and which ones might be missing :blush:

The formula in [Can be done - 1] compares a list of rows to another list rows

Items.Filter([On Hand]).ContainsAll(thisRow.[Items - Needed])

And the the formula in [Can be done - 2] compares a list of text values to another list of text values

Items.Filter([On Hand]).Item.ContainsAll(thisRow.[Items - Needed].Item)

I really really really hope you won’t take any of this in a bad way but I had the feeling that maybe some explanations were needed here :blush: .

1 Like

Thanks @Pch for this masterclass. My solution was very simple… but seems to work anyway :eyes: :stuck_out_tongue:

1 Like

Lol thanks for the explanation. Yes i indeed missed that the ‘on hand item’ was actually checkboxes. Which is very weird now that I think of it. Why comparing checkboxes to string/references.

1 Like

Oh, I never thought it wouldn’t :wink:

Lol, sorry again :sweat_smile: … But in the end, I thought it would just complement my previous reply :blush: (which I don’t know, could still be useful to someone else :woman_shrugging: …)

From what’s I’ve seen since I’ve joined the Community and I’m pretty sure that I’ve done it myself when I began to use Coda, mixing up data types and comparing oranges to apples is just one of the most common mistake :blush: … It just happens :blush:

Thank you all so very much for the quick responses. I will get around to trying these over the weekend, and respond to each of your solutions!

Sincere apologies if my post was not clear enough - I am trying to compare ingredients column for a row of Meal plan, with the AVAILABLE ingredients in the TABLE named ‘ingredients on hand’.

So I am not trying to compare strings to a checkbox, the idea is to check whether the ingredients needed for a recipe are currently available in the ingredients on hand. The intention is simply to check what meals I can cook on any given day, based on all the stuff in my pantry!

Once again, thank you all for your assistance and do let me know if this clarification helps!

Hi @Abhijit_Mahishi, with all the solutions explored I’m sure you’ll find your hapiness :wink: Let us know !

1 Like

The thing is that your screenshot (and the visual cues Coda give us regarding the data types of what we’re using in a formula) is explaining why your formula doesn’t work as expected :innocent: : you’re comparing a list of checkboxes to a list of rows in this formula …
(Hence the confusion :blush: and different suggestions)

Now, something such as this might just work (depending on your table [Ingredients on hand]) :blush:

[Ingredients on hand].ContainsAll(thisRow.Ingredients)

1 Like

Well, this looked like the simplest option to try out and I’m so glad it worked, super cool!!!

Thank you all for helping out - this really seems like an awesome community and I look forward to contributing! :slight_smile:

1 Like

Glad to know you found a way to solve the issue you had @Abhijit_Mahishi :raised_hands: !

And yes, don’t hesitate to come back… for anything :wink: !

1 Like

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