Building an item list from multiple records

Ask for Help
1

A meal plan consists of meals during an event that takes place over a period of days (day-breakfast, day-lunch, day-dinner)
A meal consists of multiple recipes (entree, sides, etc)
A recipe consists of multiple ingredients

I’m trying to generate a list of ingredients needed for the event’s entire multi-day, multi-meal meal plan (ultimately, a shopping list)

I figured out how to build receipes from ingredients.

Currently, I have a single record for each meal and each meal record is associated with an event (a period of days)

I can get a list of ingredients for a single meal (day-meal), by using a Reference List of receipes for a single meal in a record for that day-meal. While that does generate a list of ingrediates for all receipes for that day-meal, using a Reference List in a single field won’t work for multiple meals over multiple days.

Any suggestions?

2

Hello @dresvert!

We have a template that gets close to what you are describing: Restaurant Custom Orders - Grist.

  • The top-left widget has orders. Select one to see the associated items.
  • The bottom-left widget has items for the selected order. There is a count of each item.
  • The bottom-right widget shows ingredients for the selected item. (These can be configured separately in the Menu Items page.)
  • The hardest part is the formula in the top-right widget which collects all ingredients from all items for the selected order. The output looks like this:
Asparagus — 6 lb
Black Pepper (3 tsp) — 0.5 oz
Butter (104 tbsp) — 52 oz
Eggs — 69 EA
Flour — 4.55 lb
Goat Cheese (16.5 cup) — 132 oz
Heavy Cream (3.5 cup) — 28 oz
Milk (15.5 cup) — 0.97 G
Mushrooms (7 cup) — 56 oz
Olive Oil (20 tbsp) — 10 oz
Onions — 1.75 lb
Parsley — 1.5 lb
Salt (15.5 tsp) — 2.58 oz

Constructing this output takes some work.

Firstly, the relational structure is used to construct some Reference Lists – like you mention, but they are constructed using formulas.

  • There is one to collect items for a Bill of Materials record (visible here), with the formula BOM_Items.lookupRecords(Order=$id).
  • Another collects ingredients for a Menu Item (visible here), with the formula Ingredients.lookupRecords(Menu_Item=$id). (BTW, this is a common type of lookup in Grist, described here: reverse lookups.)

Then there is a hidden Ingredients column in the Bill of Materials table that collects all the ingredients for the order, taking into account the quantity of each item. (You can see it in the Raw Data page here.) That formula is more involved, using some real Python to construct a Python dictionary that maps each product to total quantity needed:

amounts = {}
for item in $Items:
  for r in item.Item.Ingredients:
    amounts[r.Product] = amounts.get(r.Product, 0) + r.Quantity * item.Quantity
amounts

Using this, the formula for the final “Full List” focuses on formatting, particularly to give a useful summary in terms of units (and that perhaps doesn’t need to be as complicated in your case):

'\n'.join(sorted(
  "{}{} — {:g} {}".format(product.Name,
    ("" if product.Recipe_Unit == product.Pack_Unit else
     " ({:g} {})".format(quantity, product.Recipe_Unit)
    ),
    ROUND(quantity * product.Pack_Units_in_Recipe_Unit, 2), product.Pack_Unit,
  )
  for (product, quantity) in $Ingredients.items()
))

Admittedly this is complex. Maybe you can get what you need based on this template. But don’t hesitate to ask here if you need more help. If you build up using non-sensitive data, and can link-share your document, that would make it easier to suggest specific solutions.

3

Thanks. That appears to address many of eventual features I hope to add. Here’s what I have so far: Meal Planning - Grist](Meal Planning)

I’m not that familar with Python yet. What does {:g} refer to?

4

Generally, the syntax like "Hello {} {}!".format(x, y) is used to produce a formatted string, replacing the first {} with the value of x, and the second with the value of y.

Inside the curly braces, you can use a lot of options. The :g option (i.e. {:g}) formats a numerical value to a certain number of significant digits, 6 by default. In this case, its main role is to show numbers like 5 as just the string “5”, rather than “5.0”, which would be the default way for Python to format a number.

To read up on the many formatting options available in Python, this article is very long but thorough: A Guide to the Newer Python String Format Techniques – Real Python. And this is the official reference: string — Common string operations — Python 3.10.7 documentation.

1 Like
5

OK, Thank you. I think I’m making progress on this now.

Previsouly, I was able to use a Reference List for Entree to select multiple Receipes which, by using “Select By” “Entree” (the Reference List), generated a list of ingredients in those Recipes in the linked ingredients table view widget.

So, I added a new column (“All Recipes”) with a simple formula to generate a list of recipes for each meal(=$Entree, $Side1, $Side2), which seems like the same idea as a Reference List. Now, however, “Select By” “AllRecipes” is not a choice to link the ingredients table view widget.

https://docs.getgrist.com/4v9Vgv6YF9jj/Meal-Planning/p/12

No option to select “AllRecipes” column…
Screenshot 2022-09-25 194438

6

This post resolved my previous post:

7

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

I am able to build a Reference List of each Recipe in a single meal, and I can build a Reference List of Reference Lists of each Recipe (an array of arrays). See URL to data view below. In this view, the Ingredients widget is using “Select By: Meal Plan - AllRecipes”

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. 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