AnsweredAssumed Answered

Having a portal / summary / filter conundrum...

Question asked by motoko on Sep 23, 2018
Latest reply on Sep 23, 2018 by philmodjunk


Hi all

 

I'm having a problem trying to get the functionality I want with a portal and summaries. Allow me to explain...

 

THE AIM

 

I'm building a recipe database / shopping list generator. The aim is that from a set of recipes I add to the database, I can generate a random meal plan for the week and a shopping list for the ingredients. To that end I've developed the following set of tables and relationships:

 

Screen Shot 2018-09-23 at 13.28.53.png

 

The tables are:

 

Plans - A selection of recipes randomly generated

Recipes - A table of recipes

PlanRecipes - A join table between the two

Ingredients - A table of produce / potential ingredients

RecipeIngredients - A join table defining the amount of a specific measure of a specific ingredient used in a recipe

Measures - A table of different measures (ie cup, ounce etc.)

 

So a Plan is made up of 7 Recipes (joined by entries in PlanRecipes), and a Recipe is made of Ingredients (joined by entries in RecipeIngredients).

 

Ignoring the intricacies for the moment, my plan is reduce each RecipeIngredient entry down to a value that I can use to sum how many of each ingredient should go on the shopping list for each Plan (let's call it 'ingredientUnit' for the moment).

 

This is purely a personal project so the only client that it needs work for is me.

 

THE PROBLEM

 

Everything works pretty well until I get to the shopping list side of things. For each Plan I can create a portal based on RecipeIngredients that will allow me to display each entry related to that Plan and the corresponding ingredientUnit. Using a summary and a GetSummary calculation I can display the total number of ingredientUnits for each specific ingredient.

 

Screen Shot 2018-09-23 at 13.47.49.png

 

BUT ideally I'd like the portal to only display one total entry per Ingredient. I've tried various attempts at filtering duplicates in the portal but the result is always that the GetSummary calculations only sum the the entries visible in the portal.

 

Screen Shot 2018-09-23 at 13.50.17.png

 

I've also tried a portal based on the Ingredient table. That method displays a single line per each Ingredient in the plan without filters BUT then every attempt I've made at a sum of IngredientUnit will sum for all the related RecipeIngredient entries in the table, not just the ones related to the Recipes related to the Plan.

 

Damn.

 

I've read a lot of previous discussion about filtering and summarising portals but can't find anything involving this kind of circumstance. I've considered lots of ways to try and relate the RecipeIngredients directly to the Plan but that doesn't provide the flexibility I want (RecipeIngredients should be linked to recipes, not plans).

 

I'm considering an ExecuteSQL sum select but am having a hard time working the logic for it in my brain ("for each Ingredient related to this Plan, find all the RecipeIngredient entries related only to Recipes related to this Plan and sum the IngredientUnits"... uh?)

 

I'm sure there's a way to make the sums work some how but can't see it. Any suggestions?

 

Thanks in advance :-)

 

J

Outcomes