4 Replies Latest reply on Aug 28, 2011 5:20 AM by TraceyLillis

    Populating field data from one table to another

    TraceyLillis

      Title

      Populating field data from one table to another

      Post

      Please forgive me if this has already been posted (I have searched) and redirect me to the appropriate post if it exists. 

      I have 2 tables.  Accounts and Contacts.

      Accounts has address fields and company name.  I want the address details to be automatically populated in the Contacts table once I select a company name. I already have _Id_account in the Contacts table and vice versa.  They are linked in the relationship graph TOC, however I cannot get it to work.

      Any help would be really appreciated. 

      Also I am a very new user to FMP. 

        • 1. Re: Populating field data from one table to another
          ninja

           A question on your question...

          If the contact is already linked to the account, and the account already has address info entered, why duplicate the address data?

          If you want to show the address for the contact, simply put the address fields from the Account table onto your layout based on the 'Contacts' table.

          There is usually no reason to double or triple store the same data...

          • 2. Re: Populating field data from one table to another
            TraceyLillis

            Ninja,

            I am not trying to duplicate the address data, I was trying to get the address to be pulled from the Accounts table so I didn't have to store separate information in the Contacts table.  Smile

            I got it to work by putting the address fields from the Account table onto my contacts layout.  It was so simple, I am not sure why I kept getting confused and trying to get the data from some other TOC's.

            Thanks for your help! 

             

            • 3. Re: Populating field data from one table to another
              LaRetta_1

              Hi Tracey,

              Why would an Account have an address and the Contact not?  Shouldn't an address be tied to a person?

              "I already have _Id_account in the Contacts table and vice versa. "

              What happens if the Contact gets another account?  Where are you going to put the second _Id_account number? 

              If a Contact can have more than one account then the contactID should be in the account table only.
              If an Account can have more than one Contact AS WELL, then it means you have a n:n (many-to-many) relationship and you need an intermediate join table which holds both IDs.

              I just want you to be aware of a potential structure issue; it is best to catch them early so you have less reworking to do.  :^)

              • 4. Re: Populating field data from one table to another
                TraceyLillis

                Hi LaRetta,

                Thanks for the heads up.  I was actually thinking of an intermediate join table containing year and term information, so this might cover this eventuality. 

                I also have 4 layouts for contacts, all using the Contacts table -

                1) Account contacts (we use it for facilities we use) - address is linked to the account because the contact works for this facility

                2) Employees of the facility/account - address is also linked as they work for the facility and no-one else

                3) Our employees - field uses their personal address details and not those of the facility/account

                4) Students - doesn't use the address fields anyway as another DB contains this information.

                Have a good day.  Smile