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.