5 Replies Latest reply on May 31, 2011 2:59 PM by KateRancourt

    Related Tables problem - duplicating data



      Related Tables problem - duplicating data


      I'm new to FMP and have been playing around with my database for a couple of weeks. I'm having major issues with the relationships aspect. 

      Basically, I'm doing a research study and need to have a bunch of different tables related through ID, and I will need certain information pulled from related tables and added to others.  I know there is something wrong with my relationships graph, just not sure what.  The relationship is partially working, but in other ways it isn't. 

      I want the 'Contact Info' table to be the #1 "storage table" for the ID number of each participant. When I enter data there, as expected, everything is fine. When I then go to the "Phenotype" table to enter data, a few things happen:

      1) When I enter the ID number of a participant, it isn't pulling data from the fields I want in the Contact Info table. I have added related fields onto the Phenotype layout that are set up to be displaying information from the Contact Info table (e.g. name_last, name_first, DOB, address), but it isn't happening. So really, I have to double-enter data that I already have stored in the Contact Info table.

      2) When I add this data in the Phenotype table, it's creating a new record in the Contact Info table, so I end up having 2 records of the same person there.  But when I delete one of those records from the Contact Info table, it deletes them BOTH.  So the relationship is kind of working in that respect?

      Hopelessly lost on how to fix my issue. The ID numbers are unique for each participant, so I'm not sure why FMP isn't able to pull information based solely on that related field. Any further solutions to fix my graph would be appreciated.


        • 1. Re: Related Tables problem - duplicating data

          Judging by your screen shot you need to go back and revisit your relationships. The graph as I see it shows that you are relating the tables from one primary key to anothey, or maybe one foriegn key to another. Each table should have a primary key that is an auto enter serial number generated by filemaker. When relating one table to another the relationship should be the primary key of the parent (auto serial) to a foriegn key field in the child table. The foriegn key field is merely a field that will contain the seriel number of the record in the parent table that a given record in the child table is related to. This creates a one to many relationship.

          For example.

          Demographic would have
          ID ( Primary Demographics Key )

          Contact Info would have
          ID ( Primary Contact Key, this is unique on each contact record )
          DemographicID ( Foriegn key field that holds the ID number of the Demographic the contact is related to )

          The realtionship would be Demographics::ID ---< Contact Info::DemographicID

          Your graph shows all your relationships as many to many. I suspect that none of the ID fields are auto enter serial numbers. Everything in Filemaker is dependant on properly formatted relationships. It's the structural foundation of your database. Until you have your relationship structure correct, nothing will work as it should.

          • 2. Re: Related Tables problem - duplicating data

            If creating a new Phenotype record is also creating a new contact record, check the design of your layout where you do this. Something isn't set up right as creating a new record in one table does not create a new record in another table automatically. There are ways, however to define your relationships so that entering data in a portal automatically creates a new related record in the portal--so that may be a clue.

            • 3. Re: Related Tables problem - duplicating data

              Thank you for your responses. Ron, I tried your suggestions with just a few of the tables and it seems to be working.

              I am still having the issue of duplicate records in the contact table any time I enter data in another table. I should say that the duplicates aren't complete - the only piece that is duplicating is the primary demographics key that I'm using to relate tables. So if I enter an individual in the demographics table (say, serial ID 1) and then enter their data in the phenotype and contact table, it will create 3 records in the demographics table - 1 with complete information, and the other 2 with only the serial ID of 1. But again, if I delete one of these records, it deletes them all.

              I do not wish to create a portal, and I don't think that is how I have it set up, but maybe I'm wrong.  In the phenotype table, I have it set up so that the fields last name, first name, and address are displaying data from the contact info table, and the serial ID from the demographics table.  In the demographics table, I have it set up so that the field DOB is displaying data from the phenotype table. In the contact info table, there is an ID field displaying the serial ID from the demographics table as well.

              • 4. Re: Related Tables problem - duplicating data

                Please describe the design of the layout where you enter this data and exactly how you entered it.

                Also, have you enabled the "delete related records" option in any of these relationships?

                • 5. Re: Related Tables problem - duplicating data

                  I figured it out! For my ID fields in the other tables, I was displaying the serial ID from the demographics table and this was adding new records in the demographics table.