13 Replies Latest reply on Jun 28, 2017 11:14 AM by shaishefer

    Help with a complex relationship calculation


      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.

        • 1. Re: Help with a complex relationship calculation

          You might search on "BOM" in this forum. Another contributor just offered a free file on BOMs (Bills of Materials) where each material can also have a BOM. BOMs and recipes where the ingredients can also have their own recipe can be set up from the same techniques since a BOM is just a "recipe" for something that you can't eat.

          2 of 2 people found this helpful
          • 2. Re: Help with a complex relationship calculation

            Thanks - This is very useful!

            • 3. Re: Help with a complex relationship calculation

              Here's the link that I was thinking of:


              Another BOM Tree - Free


              I myself have a "recipes on iPhone" DB as I like to cook and it's also a "test bed" of UI design ideas for touch interfaces. In it, any given recipe record can also be used as an ingredient in another recipe. So if I put together a recipe for a pie, for example, I can list an ingredient called "pie crust", but an icon appears next to it so that if I choose to make the pie crust, I can tap that icon and pop up the recipe for pie crust. A back button becomes visible that will take me back to the recipe from which this item is a "sub recipe". There are no limits to how far I might "drill down" when working with the recipes as each "sub recipe" can have items that also have recipes.

              • 4. Re: Help with a complex relationship calculation

                I can grasp that concept - but I would like to see everything "blown up" on the master recipe record.  I thought it should be pretty straight forward but this concept seems to be messier than I thought. 


                My layout looks like this, a simple portal that lists the formulation ingredients with a field for their percent:


                Recipe Record

                - Ingredient Portal

                -- Ingredient A, 20%

                -- Ingredient B, 80%


                I have another portal that shows all the ingredient components as I described above.  There I can clearly list all of the sub components, and the correct % in their parent, so something like:


                - water, 20%

                - flour, 80%

                - salt, 50%

                - fat, 50%


                When I try to drag in the formulation ingredient percent into the second portal I notice only the % from the first portal record is shown.  Why does this happen?

                • 5. Re: Help with a complex relationship calculation

                  Impossible to answer without an explanation of your relationships, layout and portal setups.

                  • 6. Re: Help with a complex relationship calculation

                    Ok, some more information.


                    Layout is based on the "formulations" table, which has many components.  These are managed in a portal view.


                    When we make a recipe we ask the user to select an ingredient lot (an ingredient would be salt, with many lots that have different expiration dates).  This is ensure they are using only good (non-expired) materials when formulating.  The lot belongs to an ingredient.  An ingredient has many constituents, which is where we store the ingredient makeup I discussed before.


                    Again, the layout is built off the formulation record using table T07_FORMULATIONS, with the portal to select the components in T07a_formulations_COMPONENTS.  What I am looking to do is take a percent field in the T07a_formulations_COMPONENTS table and multiply that with a percent in T07a1a2.

                    • 7. Re: Help with a complex relationship calculation

                      Where I'm having trouble is that, from a "1000 yard view", the manufacturing process has two distinct tasks supported by two different data models:


                      The BOM or "Recipe". Also called mfg specifications, this documents the materials and quantities needed to produce 1 unit of product.


                      Production: this is where you document/plan how much product will be produced and manage the resulting changes in material and product inventory.


                      The first should only change when an approved change to your specs is implemented, but can document alternatIve materials--such as substituting corn syrup for granulated sugar.


                      The second can change with each production run and is where you deal with lots and expiration dates.


                      It may just be a misunderstanding on my part, but what I see here seems to mix the two together.

                      • 8. Re: Help with a complex relationship calculation

                        Thanks for the feedback - this is all part of the "recipe".  Production is completely separate from this.  This data is used for the manufacture of chemical ingredients, lotions and creams like you find in a drug store.  We're trying to simplify the ingredient list that appears on each bottle, and show the formulator what the list will be before they "commit" a recipe.  We work on % here, not a unit of measure because these recipes can be made in 1lb, 500lb, or 50,000lb batches. 


                        Nothing here touches my inventory, which is tracked in containers and their associated transactions.  Does this make sense?


                        Every ingredient belongs to a vendor, and has many lots, components, specifications, etc.  Every recipe/formulation has many ingredients which cascade to multiple components.

                        • 9. Re: Help with a complex relationship calculation

                          But "lots" and "expiration dates" have nothing to do with recipes or a BOM as that would make you change the recipe each time. That's where I have trouble following what you want to do here.


                          I'd use this data model for that "recipe", at least as a conceptual starting point:




                          Products and Materials would then be two occurrences of the same table as that allows a product from one process to be a material used to produce a different product. It also allows you to manage both material and product inventory from the context of a unified table where production causes a drop in material inventory and an increase in product inventory.


                          Please see the link that I provided earlier as that looks like an approach that handles the issue of components that each have their own BOM effectively.

                          • 10. Re: Help with a complex relationship calculation

                            Thanks Phil - I understand and agree with your logic - I will transition from the lot to the ingredient itself.


                            I just want to be clear though that the components are not self-referencing.  It's an industry specific nuance that is missing from my explanation.  An ingredient in our case has many INCI names, these are essentially names registered with the government that you see on your household products.  They would not be referenced twice with two occurrences, but the ingredient records they belong to would in the same was a BOM is built.


                            Even when I remove this part of the relationship I still have an issue showing the detailed components, where the first ingredient added to the formulation is the only percent shown in the related components.

                            • 11. Re: Help with a complex relationship calculation

                              I made a test in a small DB but cannot get the tables to relate properly.


                              Table Occurrences, Ingredient Layout, and Formulation Layout is below - I am trying to get the
                              "% of Ingredient in Formula" in the second column of the second portal to patch the percent in portal above for the respective ingredients.  I thought it should be pretty straight forward, but simply cannot get it to work.




                              Screen Shot 2017-06-19 at 8.53.19 PM.png

                              Screen Shot 2017-06-19 at 8.54.43 PM.png

                              Screen Shot 2017-06-19 at 8.56.31 PM.png

                              • 12. Re: Help with a complex relationship calculation

                                Even if a component is never a product, I'd still use one table for both in order to better manage inventory. A manufacturing plant I worked for had their inventory system set up that way for the same reason and they also did not manufacture any of their own materials. It meant that an inventory of all items in the factory and its warehouse came from a unified table where some items in the table were purchased from a vendor and other produced by their manufacturing lines.


                                But it doesn't have to be that way, that's simply a recommendation that would appear to simplify some of the typical tasks that are part of managing a manufacturing system.

                                An ingredient in our case has many INCI names, these are essentially names registered with the government that you see on your household products.

                                But in terms of setting up your "recipe" or BOM, an ingredient by any other name is still the same ingredient as far as your manufacturing process is concerned. You may need an additional related table to list the various forms a particular ingredient may take so that when you plan a manufacturing run, you can pick a specific item from the approved list, but it's still one ingredient on the list. You'd only make it a different ingredient if using ingredient A in place of ingredient B requires changes to production and/or QA process.


                                A key question comes to mind as I look at your data modeling:

                                When you set up a "recipe" or formulation, Should you list the components or the ingredients?


                                Seems to me that the relationships would be:


                                Formulation------<BOM>------Components-----<Ingredients    (----< stands for "one to many" )


                                This assumes that for a given formulation, you list each component needed to create that item in the BOM and then each component can be further broken down into ingredients.


                                As an example using a baking recipe as the paradigm. The formulation for an apple pie might list two components: Pie Crust and Apple Pie Filling. The first component would then Link to these ingredients: flour, shortening, salt and water. The second component might list Apples, Sugar, and Flour as ingredients. The full ingredients list for the Apple Pie would then be the union of these two lists of ingredients.


                                As a starting point, a portal to Ingredients on a Formulation based layout would produce such a combined list, but if an ingredient is used in more than one component, it will appear more than once in such a list, so you would probably need to take this a step further in order to produce a list where ingredients and (if required) quantities are properly merged/summed from such a set of records.

                                • 13. Re: Help with a complex relationship calculation

                                  Phil - I'm with you 100% - thats what I have setup. I went back and recreated the layout and the math came out ok.  Not sure what went wrong but glad it's working now.