Itemise every combination of a select list

[marcusqwj]

Hi everyone.

Let’s say I have a ‘Sample Recipe’ table with a Recipe and Ingredients List (Select List), I can create a ‘Sample Ingredients’ table by making select list as a new table, which make each unique ingredient a row.

I’d like to know if I can create a third table (Sample Macros) where every non-unique instance of ingredient is a different row (in this case there should be 4 rows - Flour - Apple Pie, Flour - Muffin, Apple - Apple Pie and Egg -Muffin)

My constraint is that I want to only add new ingredients from the ‘Sample Recipe’'s Ingredients column. A new row should be automatically added into the Macros table.

Thank you!

[Pch]

Hi @marcusqwj ,

@marcusqwj

I’d like to know if I can create a third table (Sample Macros) where every non-unique instance of ingredient is a different row (in this case there should be 4 rows - Flour - Apple Pie, Flour - Muffin, Apple - Apple Pie and Egg -Muffin)

I can tell you that yes, this is feasible but … what is the use exactly of the Macros table ?

[marcusqwj]

Hi again @Pch

I am using the table to put in different quantities. Let’s say 100g of flour for the Apple Pie Recipe, and 150g of flour for the Muffin Recipe

This table is also for me to multiply different values for calories, carbohydrate, proteins, fats etc, from the Ingredients Table, which are summed up in the Recipe Table

[Pch]

Ok so, this took me some time because there’s something bothering me a little leaving me unsatisfied .

But, here it comes …

So, in the sample below, you’ll see that I added 3 columns to the table [Sample Recipes] .

The 1st one is a button that will add a row in the table [Sample Macros] for each ingredient mentioned in the column next to the button (called [Sample Macros - To be Added]) .

The 3rd column I added (called [Sample Macros - Added]) is there to display all the ingredients for each recipe already in the table [Sample Macros] and is used to create the list of ingredients that still need to be added in the 2nd column ([Sample Macros - To be Added]) .

This could probably be replaced (using WithName()) and everything could be done in [Sample Macros - To be Added] but I’ve found it helpful while testing to have this field as a visual aid

Now, I know I could’ve used solely the LookUp field [Sample Ingredients] (in the [Sample Recipes] table) to add the desired rows in the [Sample Macros] table but I chose not to because errors can happen …

Let me explain :

The [Sample Macros - To be Added] column is a multi-select LookUp field linking the table [Sample Macros] to the [Sample Recipes] where you want to be able to create new ingredients using the multi-select LookUp [Sample Ingredients] .

And what this field does is compare the list ingredients for each recipe in the table [Sample Recipes] (in your field [Sample Ingredients]) to each ingredient for each recipe in the table [Sample Macros] and only display the ingredients for each recipe which are NOT in the [Sample Macros] table already

This means that if None of the ingredients for a recipe are present in the [Sample Macros] for this recipe, the button, once pressed will add all the ingredients in that field (1 Row = 1 Ingredient) to [Sample Macros].
If All the ingredients are already present… the field will be empty and the button disabled .

But if you want to just add an ingredient to a recipe already in [Sample Macros] (e.g.: because you forgot it), the button will be enable again and just add that ingredient for that recipe in [Sample Macros] .

So the formula in [Sample Macros - Added] is this one :

[Sample Macros].Filter([Sample Recipes].Recipe.Contains(thisRow.Recipe))

Which compares the recipes in[Sample Macros] to thisRow.Recipe and returns the list of ingredients already in [Sample Macros] (as [Sample Ingredients] is the Display column of the table [Sample Macros] )

And the formula in [Sample Macros - To be Added] is this one :

thisRow.[Sample Ingredients].Filter(
  CurrentValue.Ingredient.Contains(
   thisRow.[Sample Macros - Added].[Sample Ingredients].Ingredient
    ).Not()
  )

And what it does is :

It takes the values in thisRow.[Sample Ingredients] (which is a list of ingredients) and compares it to the value of thisRow.[Sample Macros - Added] but returns only the missing ones (using Not()) .

Now, for the Button :

Here’s the Action formula :

thisRow.[Sample Macros - To be Added].FormulaMap(
  RunActions(
    AddRow(
      [Sample Macros],
      [Sample Macros].[Sample Recipes],
      thisRow.Recipe,
      [Sample Macros].[Sample Ingredients],CurrentValue
    )
  )
) 

And what it does is :

It takes the list of ingredients in thisRow.[Sample Macros - To be Added] (where each ingredient in that list is stored as CurrentValue) and for each ingredient in that list (FormulaMap()) it adds a row in the table [Sample Macros] and the corresponding recipe .

There’s also a Disable if formula which simply counts the number of ingredients to be added and disable the button if thisRow.[Sample Macros - To be Added].Count() = 0

In this sample just below, I’ve voluntarily left just the ingredient Egg for the Muffin recipe to be added to [Sample Macros] so you could see a little bit more why I’ve chosen this complicated road (sorry )…

I’ve added those "functionalities " to the table [Sample Recipes] as you mentioned that you wanted to add ingredients only from there … So I thought that create rows in the [Sample Macros] would be more convenient this way as once you add ingredients for a recipe, you should just need to click that button once done …

But, if you want the creation of the desired rows in [Sample Macros] to be automatic (potentially delayed a little but automatic nonetheless ) you can also create an automation that will push that button for you

Here’s the same setup but with a Row Changed automation

When

If

Then

So when there’s a change in the column Sample Ingredients (of the table [Sample Recipes 2]) and only if the number count of the ingredients in the row returned by the Step 1 in the column[Sample Macros - To be Added] is not equal to 0, then it will push the button in that row (which should be enabled).

You could add another action to to notify you when the automation pushed the button too … There’s no visual aid to know when an automation’s running except by seeing the result and as there can be some delays between the moment you make necessary change to trigger the automation and the automation acting on it, it not always easy to know what’s happening …

You could also create a time based automation, if it’s not too urgent for you to have the rows added where you want them to be added

I’ve also played around a little with another way to create and move those ingredients and recipes around where instead of using the Macros table as an ending point (which I called here [Ingredients & Recipes], you could use it as the entry point to create both Ingredients and Recipes but that would change your actual setup …

I think it might just be easier … but it’s just an idea …

Sorry for the length … It wasn’t easy to explain all this …
But I still hope this helps

[marcusqwj]

@Pch You continue to amaze me with your solutions!

The added and to be added columns make a lot of sense to avoid duplication and I’m once again blown away with the fact that your solution can work manually or automatically.

I’ve managed to create the exact setup using from your embed documents. I’ll be using the automation route to build my recipes

I can’t wait to share my meal plan doc once I get it working.

[Pch]

No problem @marcusqwj !
I’m very glad to know you were able to make it work (Despite the very long post )