3 Replies Latest reply on Sep 18, 2010 2:37 PM by sunmoonstar.13

    Understanding FMP 11 relationships

    ChuckR

      Title

      Understanding FMP 11 relationships

      Post

      Greetings..... I am a recovering Access user.  I am having a issue using a relationship to create a view of data from 2 tables.

      I have a database covering a cookbook collection (550+ books) and selected recipes contained in them (10,500+ recipes)  I have included the relationship diagram

      Now the problem.  I am trying to create a layout that displays RecipeName, Catagory, and Page from the Recipes table along with the Title, Author_Pub, Format and BookID from the CookBook2 table.

      When the layout is displayed (all records shown), every book is listed, and only the 1st recipe available is included.  Where there aren't any recipes for a particular book the recipe detail is blank.   When a simple find is performed, the records found do not meet the find definition.

      The intent of this layout is to be able perform a find based on recipe content, category type etc, and get a display/printout showing recipes meeting that selection criteria.

      Can anyone give me an idea where to look to correct this problem?

      Thanks in advance for your help!

      Screen_shot_2010-09-17_at_7.26.52_PM.png

        • 1. Re: Understanding FMP 11 relationships
          sunmoonstar.13

          Based on your description, it sounds like you have a layout based on the Cookbooks 2 table and you've put fields from the Recipes table onto this layout. If this is the way you've set it up, then when all records are shown, you will see all the cookbook records (because that's the table the layout is based on) but you'll only see one related Recipe record for each book record. You need to do it the other way around: set up a layout based on the Recipes table and add the relevant fields from the Cookbooks 2 and Category table. Then, when all records are shown in this layout, you'll see all the Recipes records, and when you do a Find, you'll be searching in all the Recipes records, rather than the Cookbooks records.

          Another thing that would probably be useful for you to set up is this: go back to the Cookbooks layout and create a portal to the Recipes table. Then you'll be able to navigate through your Cookbook records and see each book's related Recipe records in the portal.

          Nick

          • 2. Re: Understanding FMP 11 relationships
            ChuckR

            Bingo!!!!!  That did it.  Thanks Nick.

            Thanks also for your portal suggestion.  I had figured that feature out, and had created  a data entry from with the recoipe portal.

            Thanks again!

            ChuckR

            • 3. Re: Understanding FMP 11 relationships
              sunmoonstar.13

              Glad to hear you've got it working.

              Something else strikes me, though. In your relationship graph, there are three tables which only contain one field. I'm guessing that Format is for "hardcover" or "softcover" books, Category is for "breakfast", "lunch", "dinner", "dessert", etc, and Author_Pub is for Author and Publisher. If my assumptions are correct, then I'm not sure why you need the extra tables. All you really need are the relevant fields in the Cookbooks 2 or Recipes tables and attach Value Lists to them via a drop-down list (or pop-up menu) to select the value. For instance, the Format field in the Cookbooks tables could have a drop-down list from which you select either "hardcover" or "softcover". No need for the extra table. Likewise with the Category field in the Recipes table.

              With the Author_Pub field, I assume you type in something like "Sarah Smith, Thames & Hudson" to indicate the author and publisher, but (from the point of view of good database design) I think it would be better to put the author and publisher into separate fields, each with a drop-down list (with a value list which is set up to use the values within the field). No need for the Author_Pub table, and it would make data entry much quicker.

              One other small thing: In your relationship graph, the field name "Catagory" is mis-spelled.

               

              Nick