4 Replies Latest reply on Sep 14, 2012 9:49 AM by AdamReed

    relationship question

    AdamReed

      Title

      relationship question

      Post

      I'm trying to design a layout which shows an author, their book, and then a list of editors the book has been submitted to, with the email addresses of the editors.

      I'm working with four tables (for purposes of this question): person, email, book, submission list.  There are two additional instances of the person table: person_author and person_editor.

      The table email is related to the person table via "email id" and "person id".

      Person_author and person_editor are related to book via linking tables (as both of these are many-to-many relationships).

      The table book is related to the submission list table via "book id".

      On a layout I would like to list the name of the author, the title of the book, and in a portal on that table the editors (there will be many) included in the submission list along with their email addresses.

      I'm currently able to get everything to work except the email addresses, which at present are unrelated to the submission list.  I have been able to get the author's email address to appear with each editor, although that's not particularly helpful.

      Thanks for any advice.

      Adam

        • 1. Re: relationship question
          philmodjunk

          The table email is related to the person table via "email id" and "person id".

          You should only need to link by person id.

          It seems like your submission list table would be the join table between author book and editor. You'd have two fields in submission List, BookID, EditorID a link to author ID via a join table to handle cases where a book has more than one author is likely needed and completes the link between an author and the editors to which the author's book(s) has/have been submitted.

          Then a layout based on Book can list the authors in one portal and a portal to submission list can list the editors to which the book was submitted. Fields from Person_editor can be included in the rows of the submission list portal. An occurrence of the emails table should be linked to Person_editor by PersonID and thus you should be able to include them in the portal as well.

          This assumes only one author for the book so it may not work when you have several authors for the same book.

          • 2. Re: relationship question
            AdamReed

                 Sorry, I'm having a hard time getting my head around this.  I've tried as you suggested, and it seems to be the right direction, but thought I had to expand on that and am now a bit lost.

                 Here's what I would like to do:

                 - create an editor

                 - create an author(s)

                 - create a book(s) (and assign the book to one or more authors)

                 - create a submission list for an author(s)' book or books

                 - assign editors to that submission list

                 I've uploaded an image of what I imagine is an overly complex relationship structure -- perhaps this will help?

                 Thanks, again.

            • 3. Re: relationship question
              philmodjunk

                   I see bits and pieces of what I have described, but not the complete "system" needed.

                   This is what I had in mind:

              Person_author-----<Author_Book>---------Book-------<Book_Editor>-------Person_Editor

                   Person_author::person id = Author_Book::author id
                   Book::Book id = Author_Book::Book id
                   Book::Book id = Book_Editor::Book ID
                   Person Editor::Person id = Book_Editor::Editor id

                   Table Occurrences in Blue are occurrences of the same Person table. Primary keys are listed on the left. Foreign keys are listed on the right.

                   If this notation is unfamiliar, see this thread: Common Forum Relationship and Field Notations Explained

                   Author_Book is a join table so that you can have multiple authors for the same book--something that seems to be happening more and more in some parts of the book world.

                   Book_Editor is the join table that serves as your "submission list" of books that have been submitted to a given editor and the join enables you to submit the same book to more than one editor and more than one book to the same editor.

                   You may find this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   (If you are using FileMaker 12, use Open from the File menu to open this file and convert it to .fmp12 format. The converted copy will work just fine in FileMaker 12.)

              • 4. Re: relationship question
                AdamReed

                     Thank you.

                     I've realized that at the root of my confusion was my thinking that I had to have a separate table "submission list" with a "submission list id" and fields associated with that table -- rather than just letting the database do the work and assemble the list based on the related editors.

                     This all seems to be working now, many thanks again.

                     Adam