1 Reply Latest reply on May 25, 2012 12:36 AM by GuyStevens

    Table Relationship Trouble - I want to auto populate another table with existing data

    MichaelThale

      Title

      Table Relationship Trouble - I want to auto populate another table with existing data

      Post

      Hi everyone,

       

      First off, I am new to Filemaker and am using a trial version to determine if Filemaker will meet the goals flr my business.  I am not super tech savvy but understand the very basics of FMP.  

       

      So here is the scenario and I will be as specific as possible so that maybe one of you awesome developers can be AS SPECIFIC on giving me an answer :-) 

       

      Right now, I have a parent data base with two fields, Address & Field Rep.  The Address field is populated by an Microsoft Excel file that I import into FMP.  However,  I manually am entering each Field Rep's name into the database giving them an Address assignment.  My end goal is to be able to have a Child table which I will call Property Evaluation Checklist that will automatically be populated by the Address & Field Rep fields, which will then display an aray of evaluation fields.

      I can't get the Address & Field Rep fields from (Table 1)  to populate in my Property Evaluation Checklist (Table 2).

       

      I would appreciate any suggestions.

       

      Thanks in advance!

       

      Michael

      michael@ozlund.com

      relationship_problem-01.jpg

        • 1. Re: Table Relationship Trouble - I want to auto populate another table with existing data
          GuyStevens

          What you need is a way to tell table 2 what record in table 1 it is linked to.

          I hope you made ID fields in both your tables.(If you didn't, no problem, that can be fixed)

          An ID field is a number field set to auto enter a serial value.

          That way every record has a unique ID.

          So if every adress has a Unique ID. You can tell the table 2 the Unique ID you want to link to.

          In terms of tables you will get this:

          Table 1
          ID
          Address
          City
          Zip
          FieldRep

          Table 2
          ID
          AdressIdFk
          OtherFieldsYouMightWantToFillIn

          In the second table you have a field called: AdressIdFk. It's a number field and it's the place where you store the Id of the record of table 1 you want to link to.

          In other words, if you want to display "424 Browning Field Way  -  Hampton  -  30228   -   MBraze" In table 2 then you need to set the ID of this adress line. (Let's say it's "2") In the AdressIdFk field in table 2.

          You can do this manually, or you can use a dropdown value list, or you can put Table 2 as a portal on you layout of table 1.

           

          The only thing you are missing is is a relationship. Your Table two doesn't yet know what this value "2" means in it's AdressIdFk table.
          So you go to "File" - "Manage" - "Database" and you go to the relationships tab.

          You drag a line from the ID field of table one to the adressIdFk field in table 2. A line will appear with a [=] sign in the middle.

          Then on the layout of table 2 you add the fields (Adress, City, Zip, FieldRep)  of table 1.

          You don't create fields for this in table 2; Instead you use the fields from Table 1 to display this info on Table 2.

          I hope this make sense. It's really the basics of relational database design.

          This file demonstrates this as well:
          http://dl.dropbox.com/u/18099008/Demo_Files/TwoTablesLinked.fp7

          Have a look at it to see how it's done.