I'm having a problem trying to get the functionality I want with a portal and summaries. Allow me to explain...
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:
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.
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.
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.
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.
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 :-)