5 Replies Latest reply on Feb 21, 2012 2:41 PM by philmodjunk

    Relating records in same table



      Relating records in same table


      Hi all.

      I just started using FileMaker Pro today so please bear with me.

      I have a table called Contacts and I'm trying to add the ability to relate one contact to another.   I've already duplicated the table but can't seem to get any further than that.  Is it better to add a portal or a lookup to handle this?

      I should add that I've tried both and can't get either to work.  When I type in "Tom" to find the "Tom Smith" record, nothing happens at all.  I think I am probably missing something easy.


        • 1. Re: Relating records in same table

          How you need to link contacts to each other is a key detail to work out first. Will such links be one to one or one to many? (Contact only links to one other record that only links back to this one, or does the Link need to be to many other contact records...)

          If you are not sure, describe how and why you want to link these contact records and that will likely enable us to tell you what you have here.

          "I've already duplicated the table"

          Hope that was by clicking the duplicate button (two green plus signs) in manage | Database | relationships. If you clicked duplicate on the tables tab, please delete that duplicate table.

          The duplicate button on the relationships tab, doesnt' actually duplicate a table. It duplicates a "label" we call a "table occurrence" so you can get two boxes on the relationships tab that refer to the same table.

          • 2. Re: Relating records in same table

            Ideally it would be a many to many relationship.

            I believe I used the button to the left of the button.  I've attached a screen shot of how my relationships look.


            • 3. Re: Relating records in same table

              Many to many relationships need a join table. Create it on the tables tab and use the field tabs to define fields for it.

              Contact----<Contact_Contact 2>-----Contact 2

              Contact::K_ID_Contact = Contact_Contact 2::K_ID_Contact
              Contact 2::K_ID_Contact = Contact_Contact 2::K_ID_Contact 2

              A portal to contact_contact can be placed on a layout based on contact and it can be used to list all contact records from contact 2 that are linked to the current record on your Contact layout. You can define additional fields in contact_Contact 2 for documenting that specific link--such as the kind of relationship between the two contact records.

              Here's a many to many demo file. It's not a self join like you have here, but should still give you a good idea of how to set this up if you take a look at the "basic setup" layout. It then goes on to illustrate more sophisticated layouts with methods for keeping  the user from selecting the same pair of records to link more than once. http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              • 4. Re: Relating records in same table

                 What if I just wanted to do a one to many relationship?

                • 5. Re: Relating records in same table

                  Since it's a self join, a one to many relationship really is a many to many relationship--which is why I never asked about many to many in my original response.

                  The exception to that would be if once you added "Harry" as part of John's group, Harry does not need to be linked to anyone else but John.