8 Replies Latest reply on Jul 20, 2017 6:42 AM by Magnus Fransson

    Find a related record




           I new at FM.


           I have all my database working and related, but now a need to generate a document based on the field in the last table on the related chain and display the records at the first table of the related chain.

           How can i get this records and save on a separated table ?





        • 1. Re: Find a related record

          Welcome andre! It would be most helpful if you post a screenshot of your Relationship Graph showing your "related chain". Screenshot(s) of your layout from where you want to display fields (related or from the table occurrence upon which the layout is based), may also help.


          Thank you,


          • 2. Re: Find a related record



                 Thanks for your time. Screen Shot 2017-07-19 at 18.04.05.png

            Screen Shot 2017-07-19 at 18.08.14.png




            I'm mechanical engineer and this is for may wife restaurante. In the first image is the layout of the menu of the day (TABLE CARDAPIO) where we choose the recipes (table FICHA TECNICA) that will be use in that day.

            So I need to generate a list of products to shop for that day of cook production based on the ingredients (table INSUMOS BASE)   that are used in the recipe ( FICHA TECNICA table) and then copy to the buying table (table PEDIDOS), so we can order all we need for that day based on the menu of the day.

            All is working. I just need to undertand how to do this research and how to copy to the table PEDIDOS.


            Sorry for my English, i'm from brasil.


            Best regards,



            • 3. Re: Find a related record

              Do you have a single record in Cardapios for each day's menu? One record = one menu?


              Do you list the individual menu items in FT Cardapios?


              Do you have one record in FIchas Tecnica for each menu item or one record in this table for each recipe ingredient?


              Example: If "Vanilla Ice cream is put on the menu. Is that one record in FT Cardapios? And would you have one record in FT Cardapios for "Vanilla Ice Cream" or would you have one record for "milk", another for "eggs", another for sugar.... (In this example, you are making the ice cream, not purchasing it ready made...)

              • 4. Re: Find a related record

                Each Cardápio is a record for a single day menu.

                Each FT Cardapios is a related record of Ficha Tecnica and I use in the filtered portal in Cardapios to make ease to find the recipes.  "Carne Bovina" is for red meat recipes, "Carne Suina"if  for  pig  meat based recipes   and so on...


                Each Ficha tecnica is a record for each single and unique recipe.

                • 5. Re: Find a related record

                  It helps that I cook as a hobby and have my own recipe's solution on my iPhone.


                  Am I correct that you want a list of recipe ingredients from which to create a shopping list, not a list of menu items. In other words, using the "Ice cream" example from earlier, you want a list of milk, eggs, sugar, etc. not a list with "vanilla ice cream".


                  If so, then in which table do you list the recipe ingredients? And do you have one record in that table for each ingredient with the name of the ingredient and the quantity needed to make that dish? And does this one table list all ingredients for all recipes?

                  • 6. Re: Find a related record



                    The ingredients are listed in the INSUMOS BASE table ( primary ingredients table ). Yes i want a list o ingredients to go to the "supermarket". Yes a have a record for each ingredient in this table. The quantity is given in the FICHA TECNICA ( recipe table )  inside the portal insumos FT table where i store all the ingredients and quantity for each recipe.

                    • 7. Re: Find a related record

                      I have two statements from you that do not appear to agree with each other. It's probably a translation issue and I apologize for the extra questions that this makes necessary.


                      Each Ficha tecnica is a record for each single and unique recipe.

                      The quantity is given in the FICHA TECNICA


                      If a record in Ficha tecnica represents a single recipe, then I do not see how that table can list the quantities for each ingredient. A related table linked to Ficha tecnica is what I would expect to list quantities. I'm going to now show how I would set up these tables for this purpose and produce this list and you can compare it to your tables and relationships to see if you can make this work:


                      Menus----<Menu_Items>-------Recipes------<Recipe_Ingredients>------Ingredients (---< means "one to many")

                      Menus::__pkMenuID = Menu_Items::_fkMenuID

                      Recipes::__pkRecipeID = Menu_Items::_fkMenuID

                      Recipes::__pkRecipeID = Recipe_Ingredients::_fkRecipeID

                      Ingredients::__pkIngredientID = Recipe_Ingredients::_fkIngredientID


                      The __pk fields would auto-enter a unique ID such as a serial number or UUID, the _fk would be either number (serial) or text (UUID) fields that match to the __pk fields. With such tables and relationships, I'd use Go To Related Records from Menus to Recipe_Ingredients to get a found set of all such records for all the items on that menu. I'd sort the records by Ingredient ID such that I get a group of records for each ingredient. I'd use a summary report where I've removed the body, but added a sub summary part "when sorted by" ingredient ID to get a report that lists one row for each ingredient. A summary field could add up the quantities for each ingredient.


                      The final part is trickier: That list would give you what you needed if you only made the quantity required for a single instance of each recipe. If your menu calls for a specific number of each dish on the menu (30 steaks, 30 salads....), then I would also define a global field into which I'd copy that number so that I could set up a calculation field in the Recipe_Ingredients table that multiplies the amount totaled up by the summary field by this number:


                      gTotalDishes * GetSummary ( sStotalIngredients ; _fkIngredientID )


                      And this is the calculation field that I'd put into the sub summary part to use for checking against inventory and determining how much needs to be purchased to prepare for a day's menu.


                      And yes, an inventory management system could be used to compute not the above number, but the above number minus the amount on hand, but this is enough to pack into one response at a time.


                      I'm now off to take the family to see a Civil War battle site so this is my last post for a while.

                      • 8. Re: Find a related record
                        Magnus Fransson

                        Hi André,

                        philmodjunk wrote:

                        Menus----<Menu_Items>-------Recipes------<Recipe_Ingredients>------Ingredients (---< means "one to many")

                        Seeing the structure that philmodjunk suggests, I see a resemblance with a Nutrition Database that I assisted with, a couple month ago.

                        Feel free to see if there is something in there, that could be of assistance to you.


                        With best regards Magnus Fransson.

                        2 of 2 people found this helpful