Field Name FieldType Formula / Entry Option
person id Number Indexed, Serial Number on creation with Current Value: “x” Increment: “1”
FieldName FieldType Formula / Entry Option
book id Number Indexed, Serial Number on creation with Current Value: “y” Increment: “1”
Field Name Field Type Formula / Entry Option
person id Number Indexed
book id Number Indexed
person::person id related to link:person id (check: allow creation of records in this table (“link”) using this relationship)
person::book id related to link:book id (check: allow creation of records in this table (“link”) using this relationship)
layout table person:
insert a portal that shows records from “link” with the fields link::book id and book::booktitle
layout table book:
insert a portal that shows records from “link” with the fields link::person id and person::name
when you create a new record in one of the portals, just enter the correct id and it display also the name of the person or the title of the book (to facilitate the data entry, just create a drop down list)
Ah, thank you! This seems to have worked, however I now have a new problem...
The authors are being properly assigned, yet I've tried to add editors and agents (using the identical settings, as above, but with new instances of the BOOK table and link tables for author, agent, and editor), but when trying to enter an agent or editor on the BOOK layout, I receive the error message:
"This operation cannot be performed because one or more required related records are not available and cannot be created."
Is it a problem to have three records in the PERSON table associated with the same book record? I've experimented with several variations but can't seem to make it work.
That could happen because or in the relationship it is not checked the “allow creation of records in this table using this relationship” in the link table or because in the portal there is not the link id field but the related table id field.
When you enter a new record, this record has to be created in the link table, not in the others tables.
Hmm, I don't seem to be able to get this to work. I've checked several times that records, etc. are being created in the link table, and seem to be allowing creation of records correctly. I've posted it here, in case you wouldn't mind taking a look at this experiment?
In any case thanks very much for your help!
Everything is ok, but the fact that you have two tables in relationships for books:
Book and book agent.
In the layout “book”, which displays the records from “book”, there are two portals: one “author”, which is related to “person” (it works) and the other, “agent”, which is related to “book agent”, so you cannot see that records in that layout. You need a new layout that displays “book agent” records.
one possible solution is to use only one table to link and, in the portals, filter the records.
That is: the two portals are exactly the same, but the filters are:
filter to display authors: person::author ="yes"
filter to display agents: person::agent = "yes"
Ah, I see! I'm just using filemaker 10, and I understand that portal filtering is a feature of filemaker 11 -- is that right? Yet that does sound like a good way to simplify this problem. I will think about this a bit...
With FileMaker 10 and older versions, you can include a second pair of fields in a relationship to act as a "filter" for your portal.