2 Replies Latest reply on Jul 26, 2011 2:13 PM by raybaudi

    Help with relationships between tables - changing connecting field

    YACAmeriCorps

      Title

      Help with relationships between tables - changing connecting field

      Post

      I have a really limited understanding of the complicated workings of Filemaker, but as a small nonprofit we can't afford professional help so I must bungle my way through. I apologize if this question is not well-worded or very simple.

      Right now we have a database with two related tables - Contacts and Contributions. The relationship is currently based on the "Contact Name" field, which has the significant problem of dis-associating Contacts and their multiple Contributions if the name is changed at all. Each contact also has a unique Contact ID number, that was assigned belatedly, after the database was constructed.

      My question is, how can I add the Contact ID number to the Contributions table, thus enabling me to change the relationship to being based on the number rather than the name? Right now the ID is displayed on the page, but when I change the relationship it no longer knows how to find that data.

      Screen_shot_2011-07-26_at_2.49.24_PM.png

        • 1. Re: Help with relationships between tables - changing connecting field
          philmodjunk

          First, I'd make a back up copy of the file. That way you can discard your current copy and try again if you mess up.

          Then, is Contact ID in contacts a serial number field? Do all records in Contacts have an ID?

          If not, update it to be an auto-entered serial number and use Replace Field Contents to assign each and every record a serial number. There's a serial numbers option you can select for Replace Field Contents that will assign numbers to all the fields and update the next serial value setting for the field all in go if you need to go this route.

          Now let's make sure that all contributions records have the correct ContactID matching to the Contact ID field in Contacts. It should be a simple number field--no auto-entered serial number in the Contributions table.

          Go to a contributions layout where you can find the Contributions::Contact ID field. (Go into layout mode and make sure that it's not Contacts::ContactID) Enter this field, select Show All Records, the use Replace Field Contents with the Calculation option to enter:

          contacts::Contact ID into each and every Contributions::ContactID.

          Finally return to Manage | Database | Relationships, double click the lines connecting Contributions to Contacts and change the match fields to be Contact ID on both sides.

          Inspect your database carefully before moving forward to make sure everything matches up correctly.

          • 2. Re: Help with relationships between tables - changing connecting field
            raybaudi

            If the Contacts::Full Name field contains unique values, you could try ( *** ON A COPY OF YOUR DB *** ) to:

            1) Set the Contributions::Contact ID to be a number field with the option of Auto-Enter a Looked-up value ( starting with table: Contributions, Lookup from related table: Contacts, Copy value from field: Contact ID

            2) Show all records of the Contributions table

            3) Put the cursor inside the field: Contributions::Contact Name

            4) From menu: Records >> Replace field contents...>> Replace with calculated result >> Specify >> Insert this into the calculation:

            Contact Name

            and push "Replace"

            Now, the Contributions::Contact ID, will contain the exact Contacts::Contact ID.
            Change the definition of the field Contributions::Contact ID to be a normal number field ( no more auto-enter a lookup ) and change the relationship.

            I repeat: do it *** ON A COPY OF YOUR DB ***  !!!