9 Replies Latest reply on Dec 10, 2015 10:09 AM by MauriceG

    Master Details Portals

    MauriceG

      Hello All,

      I’m using FM 14 and I need help to resolve a relationships issue.

      I’m creating a recipe solution in which I have, amongst others, the following tables:

       

      Recipes Table, which stores a list of recipes.

      Dates Table, which stores the dates on which each recipe was cooked.

      A ‘’shell’’ table, that I use only as the basis for a layout on which I put a (recipe) portal that displays all recipes from the Recipes table.

       

      I also want to put on that shell table layout a (dates) portal based on the Dates table to display the dates on which the recipe was cooked. The idea is to have a button at the end of each row on the Recipe portal that I would click to display the dates that particular recipe was cooked. From my research on this forum, I understand I need new TOs and some global field in one of the tables and a script but I am unable to adapt to my situation what I have found on this forum. Would anyone be kind enough to explain.


      Thanks.

        • 1. Re: Master Details Portals
          jbroderick57

          If I understand what you're wanting to do, I think one of the difficulties might be that you are making a table for dates when that more likely might be something tied to something like a Recipe Event table.

           

          I think you can accomplish what you want to show by having a table for Recipes and then having a table for Recipe Event or Date_Recipe_Cooked, for example.

           

          The Recipe table has the name of the recipe in this simple example.

           

          The Recipe Event table has the date and the name of the recipe.  You could show all the recipes as you have described, in List View for the Recipe table on a separate layout. 

           

          To the right of the recipe name, you could have a popover that contains the portal for the Recipe Event table that only contains the date field.  This will show what dates the recipe was cooked based on your entry in the Recipe Event table.

           

          If you wanted to revisit the particular date, you could make the date field a button which would Go To Related Record and then be able to choose the date to see any details you may have added in your table or layout that you want to refer back to.

           

          Hope this helps.

          • 2. Re: Master Details Portals
            MauriceG

            Thanks for your ideas,

             

            I have already tried the List view option with the popover, but it seems like a popover cannot contain a portal. One other reason I'd prefer to keep my current approach is that it allows me to use that recipe layout to display recipes by Categories (Main courses, Deserts, etc.), and then by sub-categories (chicken, fish, brownies, etc.). So right now, I have two recipe portals on my layout: one that displays Categories, and another that display sub-categories.

             

            My plan is to have, to the right of each of these two portals, other portals with more info once I can make the date portal work and display the rignt info, that is when I can figure out the appropriate relationships required to do that. This is really what I'm looking for. But I'm doing one little piece at a time.

             

            Any idea about the relationships I need?

            • 3. Re: Master Details Portals
              jbrown

              Correct me if I'm wrong.

              You have Layout A from TO Shell that is used to show all the recipes you have in your recipes table (in a portal).

               

              You want to be able to click on a row and show (in another portal) the dates on which those recipes were made.

               

              I assume you have a unique ID (primary key) for each recipe in the recipe table, and a foreign key in the dates table to link the recipe and the dates together. IS that true?

               

              If so . . .

               

              What you need is a global field in this Shell table that holds the ID of the recipe. This field, call it RecipeID_g, is used in a relationship to the recipe dates table in a new TO. Create a new portal for this recipe date TO.

              When you click on a portal row's button, that script grabs the ID from the portal row and puts it in this new field, RecipeID_g. The relationship between the shell and the recipes dates table should become valid at that moment and the dates portal should fill in with the dates on which this recipe was made.

               

              I'll put together a quick demo file.

              • 4. Re: Master Details Portals
                jbroderick57

                Hi Maurice,

                 

                For what it's worth, you can definitely put a portal in a popover, although maybe that doesn't do all you need.

                 

                I think Jeremy is more in line with what your original question was.

                 

                I might also suggest that since you will be displaying categories and sub-categories, some helpful information would be to search for "Dwindling Value Lists" in the post by PhilModJunk.

                 

                Also, he has some great information and tables that cover all aspects of value lists on his FaceBook page for Caulkins Consulting, specifically Adventures in Filmmaking #1 and #2 - awesome learning resources.

                • 5. Re: Master Details Portals
                  MauriceG

                  Thanks a lot Jeremy, All your assumptions are right, and so your proposal works beautifuly.  And thanks for the demo file. But tell me, what is the purpose of the One_g field?

                  • 6. Re: Master Details Portals
                    jbrown

                    HI.

                    The purpose of the One_g field is to show ALL the recipes. That's just a calculation of 1, and that field is used to relate to the ID of the Recipe field. Using the Cartesian join, the X, I have enabled the portal to see all the recipes.

                     

                    I'm glad it worked for your.

                    • 7. Re: Master Details Portals
                      MauriceG

                      Hello Jeremy, and thanks for your latest explanation.

                       

                      There are two issues that I still have to resolve to make this recipe database completely operational (and up to my wife’s requirements) May I ask for a bit of help if you have the time and patience.

                       

                      I’ve created a table called MENU that contains 21 fields (one for each meal of the week) plus a RecipeID field. Based on that table I created a layout with the 21 fields and the ‘’name’’ of each field beside them.

                       

                      Back to the main portal on my dashboard: on each row of that portal there is the RecipeName field and the RecipeSubCategory field (for instance, the subcategory would be Fish if the recipe name is cod). I’ve put a button on the portal row called M (for Menu). This button first brings me to the Menu layout, and I have written a script that is intended to insert the Recipe name that appears on the portal row from which I clicked the M button into the meal field I select on the menu layout. Except it does not work properly: what gets inserted in the menu layout meal field is always the recipe name on the first row of the dashboard portal, no matter which dashboard portal row I click the M button from. Surely the issue again must be wrong relationships.

                       

                      The second issue I have to resolve is I’d like to be able to filter the records in the Dashboard’s main portal by subcategory. To use the example above, if I click on the subcategory field on a row where that field contains Fish, that should display only the records in which the subcategory is Fish. I know it’s feasible but I have no clue as to how to do it. Relationships again?

                       

                      Thanks, and if you don’t have time to help, I’ll understand.


                      Thanks again.

                      • 8. Re: Master Details Portals
                        jbrown

                        Hello. Here are my responses:

                        I’ve created a table called MENU that contains 21 fields (one for each meal of the week) plus a RecipeID field. Based on that table I created a layout with the 21 fields and the ‘’name’’ of each field beside them.

                        You may want to reconsider this. It is FINE for what you're trying to do, but it isn't good db practice. Whenever you start to have fields that are duplicates of each other (Monday1, Monday2, Monday3, Tuesday1,Tuesday2, Tuesday3, for example) these really should be put into another table. "Meals" is another entity. I'd create a table called "Weeks" and a table called "Meals" and relate them together. I'd have one record per week and related meal records to that week. These would be shown in a portal.  But as I said, your method is 'okay'. We'll stick with your method for a moment.

                         

                        Back to the main portal on my dashboard: on each row of that portal there is the RecipeName field and the RecipeSubCategory field (for instance, the subcategory would be Fish if the recipe name is cod). I’ve put a button on the portal row called M (for Menu). This button first brings me to the Menu layout, and I have written a script that is intended to insert the Recipe name that appears on the portal row from which I clicked the M button into the meal field I select on the menu layout. Except it does not work properly: what gets inserted in the menu layout meal field is always the recipe name on the first row of the dashboard portal, no matter which dashboard portal row I click the M button from. Surely the issue again must be wrong relationships.

                        I assume the database is putting the name of the first recipe in a field because of your relationships. I don't think this is a relationship issue. What I'd do is capture the name of the recipe in a variable in your script. Then use the value in that variable to set the field you clicked on.  That M button should have a Set Variable Script step. 

                        The second issue I have to resolve is I’d like to be able to filter the records in the Dashboard’s main portal by subcategory. To use the example above, if I click on the subcategory field on a row where that field contains Fish, that should display only the records in which the subcategory is Fish. I know it’s feasible but I have no clue as to how to do it. Relationships again?

                        You can certainly do this. here is one way: Dynamically Filtering Filtered Portals - Soliant Consulting 

                        There are other methods. The FileMaker Training Series (I believe the advanced one) explains how to filter portals. The above method has the filter on the portal. The other way is to filter at the relationship. You'd create a global field in your dashboard table and use that to relate to the Table Occurrence that is used in the portal.


                        I'll revise my demo to include these ideas.

                        • 9. Re: Master Details Portals
                          MauriceG

                          Thanks Jeremy for your advice and suggestions. I'll be looking forward to your revised demo, and in the meantime, I'll try to implement your suggestions on a copy of my current file.