Basically, you need a join table, BookAuthors, between Books <-> BookAuthors <-> Authors
The relationship would be (Books) Book_ID to Book_ID (BookAuthors), then Author_ID to Author_ID (Authors)
These are bi-directional relationships (with stored fields), so you can see/use from either side. A portal on either of the main tables to BookAuthors would allow you to add an (existing) Author to a Book, or a (existing) Book to an Author.
The names would be viewed "thru" BookAuthors. The relationships to BookAuthors (from each side) would have [x] Allow creation of related records (likely also [x] Delete related records, but first confirm you have the relationship correct). These two checkboxes would be on the join side only [important, especially for Delete].
This way you can easily have multiple authors for a book (and multiple books for an author).
You really should use IDs, not names, for two reasons. One is that nothing will get disconnected if you change the name slightly later. The second reason is that it is a little silly to use names in a join table, since names are so much longer, less reliable than IDs, and may have duplicates (two people with the same name is not uncommon).
An ID in the parent tables (Books and Authors in this case) is a unique auto-entered serial number, which is an Option for a Field definition (or it could be a UUID). In the join table, AuthorBooks, the two ID fields would NOT be auto-enter, just plain fields.
Yes, it is a little more trouble to set up. But it is standard practice; your design will never work correctly without it. Its one limitation is that it becomes a little more difficult to create an Author record directly from a Book, or visa versa. But this can be fairly easily done with a short script.
I realize this is a bit much to learn all at once. But since it is the only right way to do it....
I made a little example file. It also has special values lists set up so it can handle duplicate Author or Book names. If you are willing to say that there are NEVER duplicates, then I can modify the file to remove that extra stuff.
Thanks so much for that - it is more complicated than I had hoped. Is there anyway to make it so that we see the names instead of the "ID" number? The way I have it is we search by writer and then each writer(s) has a pop up value list of their screenplays/books. It would be a lot more convenient if we could see the writers names and the names of their screenplays as opposed to having to click on the value list to see it.
Yes, there is a way to see only the names. But first you have to answer the question, "Can there EVER be duplicate names?" Because if you show only the name, leaving the ID out of the visible drop-down (or pop-up) you are saying "No, there will NEVER be a duplicate name!" If you leave the ID out, it will show ONLY the 1st of a duplicate name (therefore only the 1st's ID), making it impossible to choose the 2nd of a duplicate pair of names.
A Value List which uses values from a field uses the hidden "index" of the field. That makes it (and relationships) much faster; it is a basic feature of a relational database. Indexes are always sorted alphabetically, and they never have duplicates.
Since you're dealing with people, duplicate names are always a possibility. Books, I don't know. Likely rare, but impossible?
The 2nd method, in the 2nd portal (to the right) of the Authors layout, is a good compromise. You still show the ID while they're choosing the person. Yes, the ID is still there, but the list is sorted by the Name, and this method can still handle duplicates. Once you make the choice, only the name shows in the portal, as it's just a regular (related) field, covering the ID field (which I sent clear to the back of the layout, in order to allow alternating color for the portal rows; so really, the opaque portal itself is hiding it; it is a little weird, as it is behind the portal, but it is still "in" the portal; back and front position doesn't matter, location matters).
Well, I'm thinking I if there is a duplicate name (which probably wont be very likely, but is possible) I can add a number "2" to it, no?
Is it possbile that a Book's name might be changed mid project? If so, that is just as big a problem as duplicate names and is also avoided by using Book IDs.
I would agree with Phil. You should use IDs. In later versions of FileMaker (since 5.5? 6?) it has been possible to hide the IDs from a drop-down or popup list, by using [x] Show only values from the 2nd field, in the Value List setup.
But then you become responsible for dealing with duplicates, by validating fields so that it cannot happen (otherwise it will).
It is not difficult to do that for Book names. However you would have to add something to one of the names to make it otherwise. Even an extra space after the name will work. But that would make it hard to tell the difference.
Author names requires more setup. Because a "name" is made up of parts. So you must Validate the combination. Which means it should be an auto-enter Text field, by calculation, with [ ] Do not replace "off" (not a Calculation field). I added Middle, Prefix & Suffix fields. If you put something different in any of those, the Full Name will no longer be a duplicate, and everything works fine.