7 Replies Latest reply on Sep 14, 2010 10:21 AM by FentonJones

    how can I make the relationships I establish more specific..



      how can I make the relationships I establish more specific..


      Hi all, I'm a newbie and have been having some issues with filemaker. Basically I work at a production company and my boss would like me to create a database where she can type in the writer and the screenplays/books he's written. So I set up relationships where if you type in an author you've already inserted into the database, his work will automatically fill in and all I have to do is type in the new piece of work. Sometimes however there is more than one writer so I have been encountering the following problem:

      Let’s say in one record I add Mark Edmund and Erik Rode as co-writers for THE FIT and then in a separate record I add Mark Edmund as the only writer for YOU AND ME.. Filemaker automatically adds YOU AND ME to the “Mark Edmund and Erik Rode” record because it’s connecting it to the name “Mark Edmund” which is obviously problematic. I initially had it so the "last name" and "first name" were two separate fields, but then I changed it so that "last name, first name" go into the same field in hopes of eliminating this problem, but it looks like filemaker needs to recognize only one of the names to autofill. How do I make it so that ALL the names have to match in order to auto fill? Can I make the relationship more specific? Any help would be GREATLY appreciated.. I am so lost right now..

        • 1. Re: how can I make the relationships I establish more specific..

          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.

          • 2. Re: how can I make the relationships I establish more specific..

            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.


            • 3. Re: how can I make the relationships I establish more specific..

              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.

              • 4. Re: how can I make the relationships I establish more specific..

                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).

                • 5. Re: how can I make the relationships I establish more specific..

                  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?

                  • 6. Re: how can I make the relationships I establish more specific..

                    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.

                    • 7. Re: how can I make the relationships I establish more specific..

                      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.