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.
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?
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::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.)
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.