Flattening an Array of Arrays

I am attempting to generate a Reference List of all Recipes in each Meal of a single Event.

I can build a Reference List of Reference Lists of each Recipe ((1,2,3),(1,3,5),(2,4,6)). See URL to data view below. In this view, the Ingredients widget is using “Select By: Meal Plan - AllRecipes” but what I’d like is for the Ingredients widget to “Select By: Events - AllRecipes”.

I think what I need to do is flatten the array of arrays into a single array of all recipes used in every meal plan for the selected Event. How can I do this? Or, maybe I’m approaching the problem the wrong way?

https://docs.getgrist.com/2eLFKkfM7NWE/Meal-Planning-working-copy/p/12 1

Hello! Happy to take a look. Could you double check the public access setting? I can’t access the document via link.

Sorry about that. I believe its set for public access now.

Just for clarity: What I’m trying to do is build a list of ingredients (and eventually a grocery list) of for all meals in a single event (where a single event includes multiple meals over multiple days: Day 1 Breakfast, Day 1 Lunch, Day 1 Dinner, Day 2 Breakfast, and so on).

What you see at the link will generate a list of ingredients for a single meal of a single event. You can see in the “AllRecipes” field of the Meal Plan widget, a formula to generate a Reference List of all recipes for that single meal. The Ingredients widget is then linked by “Select By: Meal Plan - AllRecipes”.

I’m not sure the best way to generate the list of ingredients for multiple meals during the single event. You can see where I added a formula in “AllRecipes” of the Events widget, but it results in a Reference List of Reference Lists and there is no option to link it to the Ingredients widget.

I’m curious about how grist devs would approach this, because I’ve dealt with similar situations a bit myself, but here’s one solution to your immediate question:

First, in the $AllRecipes column in TableMealPlan, I would clean up the [blank] items by changing the formula to the following:

dishes = list(($Entree, $Side1, $Side2, $Dessert, $Drink))
recipes = [x for x in dishes if x]
return recipes

Next, on the Events table, you could use the following formula for $AllRecipes:

meals = list(TableMealPlan.lookupRecords(Event=$id))
rs = list()
for x in meals:
return rs

That seems to work for me, and I can set that column to be a Reference List for TableRecipes showing column Name. Then setting the Ingredients widget to select by Events • All Recipes shows the ingredients for all of the recipes.

However, this does not account for a situation where you have the same recipe used in two meals (e.g. serving bacon at dinner in addition to breakfast on Trip A). Bacon will show up twice on the AllRecipes reference list for the Trip A event, but it will not update the amount or show up twice on the ingredients list because it is the same recipe. I tried putting together a solution for that using a couple summary tables: Meal Planning (working copy) (copy) - Grist

I imagine there may be a simpler way.