8 Replies Latest reply on Mar 22, 2011 2:15 PM by philmodjunk

    beginner question about many-to-many relationships

    AdamReed

      Title

      beginner question about many-to-many relationships

      Post

      Apologies for what I'm sure is a very basic question...

      I'm trying to keep track of authors of books.  One person may be the author of many books, and some books will have more than one author -- so as I understand it this is a many-to-many relationship.

      I have a PERSON table with the fields:

      - person id

      - name

      - author (yes/no)

      - author name (a calculated field which copies name if author = yes)

      I have a BOOK table:

      - book id

      - title

      I have a linking table:

      - author id

      - person id (looked up from PERSON table)

      - book id (looked up from BOOK table)

      This is all working fine from the PERSON layout -- I am able to create a person and a book through a portal and have the appropriate authorship values assigned in the linking table, etc.

      What I'm not able to do is create another portal on the BOOK layout enabling me to add a coauthor or change the author if a mistake was made.  Any changes do appear on the BOOK layout, but not the linking table.

      Thanks very much for any ideas...

        • 1. Re: beginner question about many-to-many relationships
          GianandreaGattinoni

          Tableperson

          Field Name     FieldType      Formula / Entry Option

          person id          Number         Indexed, Serial Number on creation with Current Value: “x” Increment: “1”

          name                Text

          Table books

          FieldName      FieldType      Formula / Entry Option

          book  id            Number         Indexed, Serial Number on creation with Current Value: “y” Increment: “1”

          booktitle           Text

          Table link

          Field Name     Field Type     Formula / Entry Option

          person id          Number         Indexed

          book  id            Number         Indexed

          Relationships

          person::person id related to link:person id (check: allow creation of records in this table (“link”) using this relationship)

          person::book id related to link:book id (check: allow creation of records in this table (“link”) using this relationship)

          layout table person:

          insert a portal that shows records from “link” with the fields link::book id and book::booktitle

          layout table book:

          insert a portal that shows records from “link” with the fields link::person id and person::name

          when you create a new record in one of the portals, just enter the correct id and it display also the name of the person or the title of the book (to facilitate the data entry, just create a drop down list)

          • 2. Re: beginner question about many-to-many relationships
            AdamReed

            Ah, thank you!  This seems to have worked, however I now have a new problem...

            The authors are being properly assigned, yet I've tried to add editors and agents (using the identical settings, as above, but with new instances of the BOOK table and link tables for author, agent, and editor), but when trying to enter an agent or editor on the BOOK layout, I receive the error message:

            "This operation cannot be performed because one or more required related records are not available and cannot be created."

            Is it a problem to have three records in the PERSON table associated with the same book record?  I've experimented with several variations but can't seem to make it work.

            • 3. Re: beginner question about many-to-many relationships
              GianandreaGattinoni

              That could happen because or in the relationship it is not checked the “allow creation of records in this table using this relationship” in the link table or because in the portal there is not the link id field but the related table id field.

              When you enter a new record, this record has to be created in the link table, not in the others tables.

              • 4. Re: beginner question about many-to-many relationships
                AdamReed

                Hmm, I don't seem to be able to get this to work.  I've checked several times that records, etc. are being created in the link table, and seem to be allowing creation of records correctly.  I've posted it here, in case you wouldn't mind taking a look at this experiment?

                http://www.motre.org/books.fp7.zip

                In any case thanks very much for your help!

                • 5. Re: beginner question about many-to-many relationships
                  GianandreaGattinoni

                  Everything is ok, but the fact that you have two tables in relationships for books:

                  Book and book agent.

                  In the layout “book”, which displays the records from “book”, there are two portals: one “author”, which is related to “person” (it works) and the other, “agent”, which is related to “book agent”, so you cannot see that records in that layout. You need a new layout that displays “book agent” records.

                  • 6. Re: beginner question about many-to-many relationships
                    GianandreaGattinoni

                    one possible solution is to use only one table to link and, in the portals, filter the records.

                    That is: the two portals are exactly the same, but the filters are:

                    filter to display authors: person::author ="yes"

                    filter to display agents: person::agent = "yes"

                    • 7. Re: beginner question about many-to-many relationships
                      AdamReed

                      Ah, I see!  I'm just using filemaker 10, and I understand that portal filtering is a feature of filemaker 11 -- is that right?  Yet that does sound like a good way to simplify this problem.  I will think about this a bit...

                      • 8. Re: beginner question about many-to-many relationships
                        philmodjunk

                        With FileMaker 10 and older versions, you can include a second pair of fields in a relationship to act as a "filter" for your portal.