Can you join and group by tables with SQL expressions?

I have been enjoying honeycode for a project, but ran into my first blocker that I cannot work around.

I have two tables, listed below. i want to do something SQL-like to create an aggregated list of ingredients for a shopping list (IE, combine garlic from all recipes into one line item).

Is this possible in any way in honey code?

Select
meal_event_details.recipe_name,
recipe_ingredients.ingedient,
recipe_ingredients.unit,
SUM(recipe_ingredients.quantity)

FROM meal_event_details
LEFT JOIN recipe_ingredients ON meal_event_details.recipe_name = recipe_ingredients.recipe_name

GROUP BY
meal_event_details.recipe_name,
recipe_ingredients.ingedient,
recipe_ingredients.unit,

Table 1: recipe_ingredients:

recipe_name ingredient quantity unit
Tomatoes with sumac shallots and pine nuts shallot 70.00 g
Tomatoes with sumac shallots and pine nuts sumac 1.50 pinch
Tomatoes with sumac shallots and pine nuts white wine vinegar 2.00 tsp
Tomatoes with sumac shallots and pine nuts tomato 700.00 g
Tomatoes with sumac shallots and pine nuts olive oil 2.00 tbsp
Tomatoes with sumac shallots and pine nuts basil 15.00 g
Tomatoes with sumac shallots and pine nuts pine nut 25.00 g
spicy moroccan carrot salad carrot 2.00 lb
spicy moroccan carrot salad olive oil 0.33 cup
spicy moroccan carrot salad onion 1.00 ea
spicy moroccan carrot salad sugar 1.00 tsp
spicy moroccan carrot salad garlic 3.00 clove
spicy moroccan carrot salad green chili 2.00 ea
spicy moroccan carrot salad green onion 1.00 ea
spicy moroccan carrot salad ground clove 0.13 tsp
spicy moroccan carrot salad ground ginger 0.25 tsp
spicy moroccan carrot salad ground coriander 0.50 tsp
spicy moroccan carrot salad ground cinnamon 0.75 tsp
spicy moroccan carrot salad paprika - sweet 1.00 tsp
spicy moroccan carrot salad ground cumin 1.00 tsp
spicy moroccan carrot salad white wine vinegar 1.00 tbsp
spicy moroccan carrot salad preserved lemon 1.00 tbsp
spicy moroccan carrot salad cilantro 2.50 cup
spicy moroccan carrot salad greek yogurt 0.50 cup
spiced shepherd’s pie with lima bean crust olive oil 6.00 tbsp
spiced shepherd’s pie with lima bean crust garlic 3.00 clove
spiced shepherd’s pie with lima bean crust shallot 3.00 ea
spiced shepherd’s pie with lima bean crust ground beef 600.00 g
spiced shepherd’s pie with lima bean crust cumin seed 2.00 tsp
spiced shepherd’s pie with lima bean crust allspice 1.00 tbsp
spiced shepherd’s pie with lima bean crust lemon 2.00 ea
spiced shepherd’s pie with lima bean crust tomato paste 3.00 tbsp
spiced shepherd’s pie with lima bean crust harissa 3.00 tbsp
spiced shepherd’s pie with lima bean crust apricots 100.00 g
spiced shepherd’s pie with lima bean crust chicken stock 280.00 ml
spiced shepherd’s pie with lima bean crust white wine 220.00 ml
spiced shepherd’s pie with lima bean crust green olive 80.00 g
spiced shepherd’s pie with lima bean crust lima bean 670.00 g
spiced shepherd’s pie with lima bean crust tahini 70.00 g
smoky marinated feta garlic 10.00 clove
smoky marinated feta lemon 1.00 ea
smoky marinated feta bay leaf 4.00 ea
smoky marinated feta chipotle chili flake 2.50 tsp
smoky marinated feta olive oil 250.00 ml
smoky marinated feta sea salt 1.00 tsp
smoky marinated feta paprika 1.25 tsp
smoky marinated feta feta 360.00 g
Shatta tomato 90.00 g
Shatta fresno chili 180.00 g
Shatta kosher salt 0.50 tsp
Shatta apple cider vinegar 3.00 tbsp
Shatta olive oil 8.00 tbsp
honey roasted carrots with tahini yogurt honey 3.00 tbsp
honey roasted carrots with tahini yogurt olive oil 2.00 tbsp
honey roasted carrots with tahini yogurt coriander seed 1.00 tbsp
honey roasted carrots with tahini yogurt cumin seed 1.50 tsp
honey roasted carrots with tahini yogurt thyme sprig 3.00 ea
honey roasted carrots with tahini yogurt carrot 3.00 lb
honey roasted carrots with tahini yogurt cilantro 1.50 tsp
honey roasted carrots with tahini yogurt tahini 3.00 tbsp
honey roasted carrots with tahini yogurt greek yogurt 130.00 g
honey roasted carrots with tahini yogurt lemon juice 2.00 tbsp
honey roasted carrots with tahini yogurt garlic 1.00 clove

Table 2: meal_event_details

event_date meal_type meal_component recipe_name
12/30/20 dinner main spiced shepherd’s pie with lima bean crust
12/30/20 dinner side honey roasted carrots with tahini yogurt

Hi @Matt-e8a4,

Welcome to the community, and thanks for your post! :honeybee: :honey_pot:

For this, I would suggest using the IN operator to check if an ingredient is already in the shopping list. If an ingredient already is, you can just add to the quantity of that ingredient.

The concept in Honeycode is using Filter formulas similar to below, and running a particular action for those returned rows:

=FILTER(IngredientsTable, "Ingredients[ingredient] IN ShoppingList[Ingredient]")

To show you how to do this in Honeycode, I created an app that achieves what you're asking for :slight_smile:

Here's how I set it up:

I have an EventMeals table with the meals that will be for a given event:

An Ingredients table, where I added two different recipes that had at least one of the same ingredients (garlic, in this case):

A Recipes table, which has the recipe name and a column that filters all of the ingredients:

And a ShoppingList table to aggregate all the ingredients for the added recipes:

In Builder I created a screen, where a user can select a recipe that they wish to add to a shopping list. The Recipe variable is a picklist where the user can select an item from the recipes table:

Below it I have two lists, one that will display the added recipes and the other that will display the ingredient shopping list (both filtered by the event date):

EventMeals list:

Shopping List:

I also created a variable that just has a filter of the shopping list for this event. You will see in the following steps how it can simplify formulas in your automation:

The Add button is where you'll add the automation that checks if the ingredients in an added recipe are already in the shopping list and take actions accordingly.

For the automation of the Add button, I have three actions:

The first action writes the event date and the recipe to the EventMeals table:

The second action checks each of the ingredients in the added recipe to see if it is already in the shopping list. This is done with a filter formula in the Run Options, that uses the IN function:

If the ingredient is in the shopping list, have the action overwrite the quantity value with the new value (added quantity from added recipe):

And finally, the third will only add the ingredients if they are not already in the shopping list. You will want to set up the Run Options to check that the ingredients are NOT in the shopping list yet:

And if the ingredients are not in the shopping list yet, the action will add a new row for each ingredient:

Screen Shot 2021-01-03 at 5.42.26 PM

Now you have an app that will allow you to add multiple recipes to an event, and the shopping list will aggregate the total quantity of each ingredient needed (one line for each ingredient):

This is also reflected in the table:

Let me know if this example helps and if you have any other questions around this. :honeybee: :slight_smile:

2 Likes

This is amazing - I was able to completely build this out in my larger/less simple app. I have 2 follow-up questions:

  1. I don't fulling understand the purpose of the "Recipes" table you made--- what purpose does it have and where is it used?

  2. If I wanted to add a Delete button to a single row of EventMeals (like delete 1 of 2 meals) --- how would I decrement the shopping list? I spent a few days struggling to make this work, but could not get it to work.

thanks again for this detailed example

Hi @Matt-e8a4,

Great! I'm happy you've been able to build your app :smiley: :star2:

For your questions:

1 ) I created a Recipes table to help organize and relate the data. Since the Ingredients table has multiple ingredients for the same recipe, the recipe column in the Ingredients table is a rowlink to the Recipes table to relate the data.

2 ) I'm happy to show you how to create a delete button :slight_smile: For your example, I created one which removes a recipe from an event and the ingredients from the shopping list:

In the EventMeals list, I added a Remove button. It has three different actions on it, which I'll expand on below:

The first action is an Overwrite that will subtract the ingredients of the recipe from the Shopping List. It filters for the ingredients of the recipe being removed from the ingredients table:

Screen Shot 2021-01-12 at 7.08.51 PM

The second action deletes ingredients from the shopping list, if the quantity is zero. This is done by filtering the event's shopping list for any ingredients that have 0 in the quantity and deleting the context row:

Lastly, the third action deletes the recipe from the EventMeals list. This is done by simply specifying to delete the context row, which is the equivalent of using THISROW():

Screen Shot 2021-01-12 at 7.09.36 PM

Now your app allows you to remove recipes from your event, which will also remove the ingredients from the shopping list :slight_smile:

I'll highlight that the order of these actions is important, as you need overwrite rows before deleting context rows.

Hope this helps! :honey_pot: :honeybee:

1 Like

This worked perfectly - adding [quantity])-[quantity] after the findrow was the key to this for me.

1 Like

Awesome :smiley: I'm glad it's working for you, @Matt-e8a4!

Let us know if you have any other building questions that come up, we're always happy to help :honeybee: