3 Replies Latest reply on Oct 14, 2013 8:12 AM by RickWhitelaw

    link to other data in another table



      link to other data in another table


      I am creating a solution for a school project that calculates girls' middle names.  I have two tables one for first name and one for middle name, each table has an auto enter serial "NameID" Field and a field for the name.  how to link so that a particular MIDDLE NAME can be associated with more than one firsr name?  so if i search for first name Amber it brings back every middle name linked to Amber as tge first name.  but if i search for Samantha as first name it only brings back those names linked to the first name samantha?  also how to impliment a "thumbing" system, so the more times a particular middle name is "verified" as correct the closer to the top of the list it shows by one "point" if a name is thumbed down its popularity in relation to that first name is decresed by one.  like googles pagerank but for girls middle names.  the database is to illustract database concepts and how easy data management can be with a database in place.  my friends are doind DBs for movies, weatger data, and so on and using other database platforms, i chose filemaker pro advanced 10 to show that it is easier than the others, i'm just hung up on some of tge finer points of this one.

        • 1. Re: link to other data in another table

               The  Parent table . . . Let's assume for the moment that this is First Name, should have an auto entered serial id. The child table, let's call it middle name, should have a foreign key that is NOT an auto entered serial. Create a layout based on the parent table. Place a field, let's say Middle Name, from the Middle Name table on the parent layout. Allow creation of records in the child table from the parent table in the relationship graph. When you enter a middle name FileMaker will automatically create the foreign key in the child table and the first and middle names will be related.

          • 2. Re: link to other data in another table

                 Ok, at first it looks like a one to many relationship, but thinking further it is probably more like a many-to-many relationship.  If it is one-to-many Rick Whitelaw's approach will work easily.

                 If it is many-to-many There are two ways to handle this.

                 1.) In the middle name table have the NAMEID foriegn key field for the first names have a delimited (return separated) list of keys for the first name.  Then there has to be ways to set up that list.

                 2.) More common way of many-to-many tables is to have 3 table.  One for the first name, one for the middle name, and the other to connect the two.  The other table will have both foreign keys of the first and middle name table.  With that join table, you can also add the popularity points in it that would be unique to that first and middle name combination.

            • 3. Re: link to other data in another table

                   The simplest way is a one to one relationship. One middle name for one first name. Of course some people will have the same first name and some will have the same middle name.  To find all people with the middle name "Jill" you perform a find on the middle name field. Ditto for finding first names.