As a relative beginner in Filemaker, I thought I did my homework, as I've split up my many to many relationship by using a joint table. But I just can't have it working and after days of searching, I still have no clue what I'm doing wrong.
I'm making a book database for my personal bibliophile library, as I couldn't find an existing solution tailored to bibliophiles. As such I have 3 tables:
- Books, with a primary key called ID (uuid)
- Contributors, with a primary key called ID (uuid), and two foreign keys: ID_Books and ID_Contributors
- Book Contributors, with a primary key ID (uuid)
I've attached a relationship picture (Screen Shot 1.png).
As explained in the Filemaker help files about many to many relationships, I've then created both relationships between:
- Books and Book Contributors: see Screen Shot 2.pgn, allowing deletion and creation of related records in Book Contributors.
- Book Contributors and Contributors: see Screen Shot 3.png, not allowing deletion or creation, as the contributors shouldn't be deleted or created when the joint relationship would be broken (author removed from a book for example).
I've then created a portal in books, showing all records from Book Contributors, with fields ::Role and ::Book Contributor (Screen Shot 4.png). The ::Book Contributor field is a drop-down with value list using values from the field Full Name (Screen Shot 5.png). That works fine too.
But, although I have been able to select one or multiple contributors for a book, and consequently Filemaker created each time a new entry in the Book Contributors table (the joint table), the primary ID is created, the foreign key ID_Books is also created (so that relationship is ok), the Book Contributor name is also filled, role too, but the ID_Contributors field is not ! (see Screen Shot 6.png)
As such the relationship is not complete.
So, why is the ID_Contributors field not filled in, or why is the relationship not made? What am I doing wrong, and how to solved this ?
PS: The thing I do not understand is that I was able to have all this work when I was in the Contributors table making relating the Contributors::not using the ID_Contributors field as the relationship field, but when relating Book Contributors::Book Contributor with Contributors::Full Name. Then everything worked fine, but I realized this wasn't a solution because a simple change of the Full Name in the Contributors table would mean the relationship with the Books would be gone.
I'm really desperate, so if somebody of you Filemaker guru's is willing to spend some time on this issue, I would be very grateful, and if desired, also willing to pay to solve the issue.
Thanks a lot,
Bruno van Branden.