"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."
Exactly how are you assigning an editor to a contract?
What layout and on what table occurrence is the layout based? If you are using a portal, on what table occurrence is it based?
I've tried a couple of things (and am of course open to suggestions), but currently am trying to assign the editor in layout based on the book table, but through a portal that displays records from the contract table.
I'm assuming that you have color coded your relationship graph so that table occurrences with the same data source table are the same color. That's how I do it anyway...
That explains the problem. The relationships you have defined won't work for that. It's a case of "can't get there from here" using a layout based on the Book Table occurrence (Upper green box in your graph).
Make a new table occurrence of Book and link it to Book Editor Link by Book ID. Use a layout based on this table occurrence and it should work just like you link authors to books.
Yes, the color codes are the table occurrences with the same data source table.
I thought that might be the case, but do you have thoughts about how would I assign editors to contracts (rather than books)? (A book may have many editors, but that is a relationship defined by the contract.)
That gives you this relationship:
A portal to Contract_Editor on a Contracts layout can be used to list and assign Editors. Put a portal to Contract_Editor on an editor layout and it will list all their contracts. In each case, you can add fields from the third table to the portal row to show additional information. Thus a portal on the Contracts layout could include the name field from the Editor field.
Whoa, I think I may have been really overcomplicating things...
I've just deleted the linking table, created a field "contract:editor id" and linked "person:person id" to "contract:editor id" and it seems to have worked. Does that sound right, or is likely to create problems I'm not foreseeing?
That allows for only one contract and one contract editor for each author. Couldn't an author have a different editor for every book they write?
Oh, yes, definitely. In fact they can have 10 different editors for the same book. These are essentially all many-to-many relationships except for editor and contract.
And how do you show that if:
"I've just deleted the linking table, created a field "contract:editor id" and linked "person:person id" to "contract:editor id"
I'm not sure. Just be clear, authors, agents and editors all have unique person ids. I thought since the editor to contract relationship was a one-to-many relationship this might work. A book can have many contracts, but a contract can only have one editor. I assumed a single editor id could be related to many contract ids. Is that not right?
Sorry for my inexperience...
Ideally I would like to be able to assign the editor on a layout based on the contract table. I'll keep working at this, too. I appreciate your help.
It's more a case my unfamiliarity with the book publishing biz...
I figured multiple editors would be listed on the same contract for a given book---making it a many to many relationship.
Since it's one editor to a contract record, you can just record the editor's ID in the contract record.
Thanks very much for your help, that's great news!