6 Replies Latest reply on Jun 2, 2012 12:26 PM by GuyStevens

    unexpected relationship



      unexpected relationship


      I discovered by chance that there seems to be an unexpected relationship in my database -- this has to do with relating people (authors) to books.

      I have a "person" table in which all people have records.  I have a second instance of the "person" table called "person author" in which I relate people to books (in a "book" table).

      What's strange is that the "person author" to "book" relationship seems fine.  The ids are all correct, if I display the author of the book the right record is displayed, etc. -- no apparent problems.

      However, if I display the "person" (not the "person author") related to the book I get what seems to be a completely random person record.  Moreso, when I deleted one of these records, the next sequential record became related to the book.  (I've just put "first name" fields on the layout -- from both "person" and "person author" to watch this happen.  "Person author" is correct and remains so, "person" is not.)

      My suspicion is that this was caused by one of two scripts designed to search people and books.  The scripts are identical except that they search different tables -- I've attached one.  "Search action" is the name of two calculated fields (in both the "person" and "book" tables), and I noticed that the "search action" contents matched the erroneous records -- so that a title and innappropriate person were both listed in the "search action" field.  In the "person" table "search action is "first name" and "last name", in books it's "title" and "first name" and "last name".  So I think the books script is the problem.

      Any general ideas about why this might be happening, how I can remove this data, and where I should look for the relationship?


      Many thanks.

        • 1. Re: unexpected relationship

          I'm not seing any script attached. If you want to show an image, then you first need to upload it somewhere on the internet and use the image's URL.

          If you use Dropbox (www.dropbox.com) you can just put the image in your "puplic" folder and "copy the public link" and paste it here or insert as image.

          You say you have the table Person Author related to the table book. But then you say:

          However, if I display the "person" (not the "person author") related to the book

          So is your table person also related to the book table?

          Moreso, when I deleted one of these records, the next sequential record became related to the book

          That all sounds a little funky to me. how did you make your relationships?

          What kinds of fields do you have linked to each other?

          Do you have ID fields that are number fields with an auto enter serial number?

          Do you use those as the basis for your relationship?

          Do yo have a People Id Foreign Key field in your books table?

          Here is a demo file that shows patients linked to admissions.

          It's kind of the same structure you should be having:


          • 2. Re: unexpected relationship

            and where I should look for the relationship?

            I assume you made the relationship yourself, so you'll know this. But in case you didn't:

            Go to "File" - "Manage" - "Database" and click on the third tab "Relationships"

            There you can see how your tables are related.

            • 3. Re: unexpected relationship

              Sorry, tried to upload the script as a pdf -- this should work.

              The "person" table as "person id", the "book table" has "book id".  "Person id" and "book id" are only related through the "person author" instance of the "person" table and the "book" table.  These are auto-enter serial numbers, yes.

              • 4. Re: unexpected relationship

                You can't link the ID of the People table to the ID of the Book table.

                That's an impossible relationship.

                Let's see how Filemaker deals with ID's and relationships:

                Every record in the people table get's a numbered ID, so does every book.

                So you have a person with ID 1, one with ID 2, one with ID 3, etc.

                You also have a book with ID 1, ine with ID 2, etc.

                Now what if you want to link book number 2 to People number 3.

                Because one person can be linked to many books, but a book can only be linked to one person (I assume) But if that's not the case that's another situation.

                You would have to store the People ID "3" somewhere in the book table. Somewhere in the record of book "2"

                That's why, in the book table, you cerate a "Foreign ID field" That's a simple number field where you storethe ID of the person you want this book to be linked to.

                And then you create a relationship like you see in the image below:


                The patients table is your people table and the admissions table is your books table.

                If you look at the demo file I posted you can see this at work.

                Then about your script:

                You go to a search layout, but which table is this search layout based on? It says search but what kind of relationships does this search table have with the other tables?

                Then you do a "Show all records" but if you do a find right after, you don't need to show all records.

                Then you set a field in your Person table. Again, how is this related to the search table?

                Then you go again to the layout you are already on, that's not neccesary.

                • 5. Re: unexpected relationship

                  Sorry, I just had minor surgery and my pain medication is making me a little spacey.

                  I do have a linking table between "person author" and "book id" (this is a many-to-many relationship, as people can write more than one book, and a single book can have multiple authors).  "person::person id" is related to "person book link::person id"; "person book link::book id" is related to "book::book id".

                  I'll think more about this, but perhaps you could answer one question -- I've looked through the tables at all of my relationship ids, and can't for the life of me find the relationship between "person" and "book".  Is it possible this relationship was created by the script and then indexed, and thus is only sort of cached instead of actually existing as viewable related records?  Or is that nonsense, and I just haven't found it yet?

                  • 6. Re: unexpected relationship

                    Aha, so then you have a Join table.

                    That's another story.

                    This file is a typical example of a join table.


                    can't for the life of me find the relationship between "person" and "book"

                    There is no relationship between person and book.

                    There doesnt need to be. Or atleast, that would be a bad idea.

                    We will assume you are going to a book record and then adding one or multiple authors.

                    What you need to do for that to work is create a portal on the book layout. The layout based on the table books.

                    That portal needs to show records from the table "person book link"

                    It only needs to show the "PersonId" field from the Person Book Link table.

                    Now you won't be able to create records untill you "allow the creation of records via this relationship"

                    You need to do this in the relationship dialogue by double clicking the relationship between "Person Book Link" and "Books"

                    There, on the Person Book Link side of the relationship you check th box for "allow the creation of records via this relationship".

                    That means you can create records in the Person Book link table from the books table.

                    Then, back on your layout, in your portal you set your PersonId field to be a dropdown that gets it's values from the Person table. With the Person ID in the first field and the Persons Name (you could make a calculation field that calculates the full name if you have firstname and lastname in seperate fields) in the second field. You could even choose to "only show fields from second field".

                    There, you should be all set.

                    Now I don't know how your old setup is so I don't know if you'll have to redo some stuff or not.