4 Replies Latest reply on Jun 13, 2011 8:17 AM by AdamReed

    multiple linking tables between two tables

    AdamReed

      Title

      multiple linking tables between two tables

      Post

      I am still using FM 10 Pro, if that makes a difference.

      I have a table Person and a table Book, and I would like to define two types of many-to-many relationships: Author(s) of Book(s) and Agent(s) of Book(s).  Although it's debatable, I am assuming Authors and Agents are both people (represented in the Person table).

      In the Person table I have unique ID, person name, yes/no fields indicating whether the person is or is not an Author, Agent, Editor, etc., and calculated fields based on those yes/no fields which serves to populate value lists of "Authors" "Agents" and "Editors".  (There are other fields but they're not relevant here.)

      For my purposes only Authors and Agents are associated with Books (Editors are associated with Contracts, which is handled separately and working fine).

      The difficulty I'm having is that I would like to handle this all on one layout (based on the Book table).  This seems to work fine for Authors, but for Agents I needed to create another instance of the Book table, and I'm not able to get it to work.  I get the error "This operation cannot be performed because one or more required related records are not available and cannot be created."

      I've attached the relationships diagram of these tables.  Thanks for any help, and please let me know if you need more information.

      author_agent_link.png