3 Replies Latest reply on Sep 12, 2011 12:12 PM by philmodjunk

    Keeping Many to Many Relationship Integrity



      Keeping Many to Many Relationship Integrity



      I am using FM Pro 6 (still) and ran into an issue with a new database design.

      I have the following files and Designs:

      File A; with primary key A

      File B; with primary key B

      File AB; with foreign keys A and B

      I have built all of the necessary relationships.

      In File A I have a form with a Portal view of the relationship between A and AB.

      If I enter data all is good unless I go to File B and change data.  The data doesn't seem to update in File AB.


      Any ideas?  THank you in advance!

        • 1. Re: Keeping Many to Many Relationship Integrity

          What kind of data are you changing?

          Using an example without A and B references will make this much easier to follow.

          Frankly, changing date in File B should not change data in File AB, the same data should not be stored in both files/tables in the first place--so this suggests that you need to change the design of your database to eliminate the duplicate data storage.

          • 2. Re: Keeping Many to Many Relationship Integrity

            OK.  More details:

            This is a DataBase for use for my family's home school.

            Table A is our Daily Log, where we have information arouind the Day (whole or half day, total hours taught, etc.)

            Table B is our Resources, where we have information around the Resources we could use (Resource Name, Type, Subjects, etc.)

            Table AB is the join table betwen the two, where we collect infromation about how much time we used each resource for that day or maybe what chapter we read, etc.

            I have made the Primary Keys of both A & B as foreign keys in AB.  I have used a auto created, incrementing Number field as this primary key.

            On the portal I have the field Resource Name with a value list pop-up from the Resource Name field in Table B.  The primary key from B is not populted in AB.  So if I change the Resource Name In B it doesn't reflect back in AB and therefore not in the Portal in A.

            • 3. Re: Keeping Many to Many Relationship Integrity

              You should have only two fields in AB unless you need to add additional fields unique to the pairing of a specific record in A with a resource record in B. The name of the resource should not be a field that is defined in AB, only in B.

              You should use a drop down of Resource ID's and Names on the foreign key field in AB. To show the Resource Name field in AB, you should include the related field from B inside the portal to AB or format the value list on this ID field as a Pop up menu and specify only that the second field be visible. (Not sure that is possible in FileMaker 6, but that's how we'd do it (Showing only the second field) with today's version.)