AnsweredAssumed Answered

Help with a complex relationship calculation

Question asked by shaishefer on Jun 19, 2017
Latest reply on Jun 28, 2017 by shaishefer

Hi All -


I'm a long time lurker/reader but I've run into an issue now that I know should be easy but can't wrap my head around.


I've got a tables of ingredients and their components for a recipe.  For example, ingredient A has 2 components (20% water and 80% flour) and ingredient B has 2 components (50% salt and 50% fat).  Now I want to create a recipe that uses those 2 ingredients and builds a complex ingredient list.


Using the same items above, let's say there is 20% of ingredient A and 80% of ingredient B in my recipe. What I would like to generate is a list of components which should come to (again using the values above) 16% water, 4% flour, 40% salt, and 40% fat. 


The relationship between ingredients and components is a direct one, as is the one for recipe, ingredient, and component.  All are related directly using their IDs.  When loading the final components on a portal on a recipe I can list them all out, see their ingredient breakdowns (for example 20% and 80% for ingredient A) but I cannot multiply successfully given their use in the recipe.


I am attaching a picture of my relationship graph, I use a "lot" table to connect to the ingredients because I store expiration dates and other information that that I pull when I make the recipe.


Any help is appreciated.