AnsweredAssumed Answered

complicated relationship

Question asked by AdamReed on Apr 15, 2011
Latest reply on Apr 18, 2011 by AdamReed


complicated relationship



I'm trying to assign authors, agents, and editors to books and contracts.  Authors and agents are assigned to books, editors are assigned to contracts, and contracts are only created after a book is created.

I have three relevant tables (and linking tables because these are many-to-many relationships):

- "person" "book" and "contract"

- there are also second instances of the book and contract tables.

Each of these tables contains a unique id field (person id, book id, and contract id), and then descriptive fields such as name, title, or territory, respectively.

I also have three linking tables:

- "book author" "book agent" and "book editor"

These linking tables contain the unique ids of the relevant tables, so "book author" contains "person:person id" and "book:book id", whereas "book editor" contains "person:person id" and "contract:contract id".

I'm using value lists to handle the different person types, based on a "type of person" field, then I'm displaying person id, and person name (that seems to be working correctly).

The authors are fine -- I can create an author, a book, and assign the author to a book.  The editors, however, aren't coming through.  I am able to create them on my layouts, but the linking table is not being populated.

(There is a relationship between book:book id and contract:contract id, as well.)

That is a general summary.  Any help would be greatly appreciated.

I've attached a screenshot of the tables and relationships case that's helpful, of course just let me know if more detailed information is needed.