7 Replies Latest reply on Mar 14, 2013 11:28 AM by philmodjunk

    Two Table Relationship

    YukioKina

      Title

      Two Table Relationship

      Post

           Greetings all,

           I'm new to FMP and so far feel like I'm doing fairly well with it.  I've been stuck on figuring out relationships and portals and lookups and all that stuff and I'm hoping someone might be gracious enough to help straighten this out with me.  I've done searching through the forums, knowledge base, Google and YouTube but not really able to make too much sense of it, especially in relating exactly to my need.

           I have a database with two tables; events & contacts.  In both tables I have fields like first name, last name, home phone, mobile phone, etc. and what I'd like is to be able to enter client info into the events table and either continue manually entering the info, or if a record for them exists in contacts, have it automatically pull up their info and load it into the applicable fields in the events table.  In my searching, I've found that there are apparently a couple of ways to do this?  One of which will maintain a link between the info so that if I change a contact's info in the contacts table, it will reflect in the events table.  That would be my preferred method.  I'm just really not sure how to go about creating the relationships for it to do this.  I'm familiar with the relationships window, just not really sure how to create those relationships.

           Thanks to all who took the time to read this, and an advance thank you to anyone who responds!

        • 1. Re: Two Table Relationship
          philmodjunk

               First you have to have tables and relationships in place that work for what you need.

               Am I correct that a contact can participate in many events and that an event needs to be linked to many contacts?

               If so, you have a many to many relationship and you need something like this:

               Contacts-----<Contact_Event>------Events

               Contacts::__pkContactID = Contact_Event::_fkContactID
               Events::__pkEventID = Contact_Event::_fkEventID

               For an explanation of my notation, see the first post of: Common Forum Relationship and Field Notations Explained

               If that is what you have, you may want to examine this demo file for ideas:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you are using FileMaker 12, use Open from the File menu to open this file and get a converted copy you can examine and test in FileMaker 12.

          • 2. Re: Two Table Relationship
            YukioKina

                 Thanks for such a quick reply!  However I am quite baffled, hopefully I'm interpreting right:

                  

                 

                      Am I correct that a contact can participate in many events and that an event needs to be linked to many contacts?

                 Yes, this is correct.

                 

                      If so, you have a many to many relationship and you need something like this:

                 

                      Contacts-----<Contact_Event>------Events

                 

                      Contacts::__pkContactID = Contact_Event::_fkContactID
                      Events::__pkEventID = Contact_Event::_fkEventID

                 

                      For an explanation of my notation, see the first post of: Common Forum Relationship and Field Notations Explained

                 So if I'm reading you correct...I'm required to create a third table to link the contacts and events tables?  

                 

                      If that is what you have, you may want to examine this demo file for ideas:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras/.fp7

                 I appreciate the demo file, but in looking at the relationships, I'm not much clearer on the field relations.

                  

            • 3. Re: Two Table Relationship
              philmodjunk
                   

                        So if I'm reading you correct...I'm required to create a third table to link the contacts and events tables?

                   Yes

                   WIthout the third table you won't be able to work with the data to link the same contact to differeent events and the same event to different contacts.

                   Say you have these two events:

                   __pkEventID     Name
                   1                       House Party
                   2                       Wedding

                   And these two contacts:

                   __pkContactID    Name
                   23                       John Smith
                   34                       Fred Jones

                   To link John Smith to House Party, you would create a record in the join table with these values:

                   _fkContactID      _fkEventID
                   23                      1

                   To link Fred to the Wedding, you would create:
                   _fkContactID      _fkEventID
                   34                       2

                   The portals you see on the layouts in the demo file are portals to the join table. Selecting a value from the drop down list on a blank row in the portal creates a new record in the portal and enters the needed ID numbers into the two _fk fields to build a link between an event record and a contact record.

              • 4. Re: Two Table Relationship
                YukioKina

                     PhilMod, thank you very much for the help you had given me previously.  I was not clear on how portals and relationships worked and so while your responses were extremely helpful, I was not properly equiped to understand them.

                     I recently went through a series of online training courses for FMP12 and have a better grasp of many of the FMP concepts.  Because of this, I have managed to link contents from Table A into Table B using related fields and portals.

                     What I find my problem now to be is that using related fields to display a record from Table A in Table B only allows for one record to be displayed.  That is, if I have two sets of "First Name, Last Name, Phone, Address, etc." fields in Table B, choosing a record from Table A in the first field of the first set will populate the second set with the exact same info.

                     Whereas if I use portals, I can display one, two or twenty records from Table A in Table B, however each record displayed does not retain a unique identifier.  That is, I cannot have three clients shown in Table B and have each one as Client 1Client 2 and Client 3.  So for example, if I wanted to take the last names of the three clients I have shown in Table B and combine them in one field, there isn't a way for me to do so because each client's last name belongs to the exact same "::Table A Last Name" field.

                     Am I mistaken on something here?  Is there a way to uniquely identify each record in the portal, or to have multiple sets of the exact same related fields showing different records in the related table?

                • 5. Re: Two Table Relationship
                  philmodjunk

                       Can you explain in more detail what you are trying to do and why?

                       You can certainly pull date from one field from multiple related records, but knowning why this is desirable is very important.

                       And while my example file uses portals as they are the simplest way for a new developer to set up something for working with groups of related records, there are also alternative methods that can be useful.

                  • 6. Re: Two Table Relationship
                    YukioKina

                         ● Table CONTACTS holds multiple records

                         ○ each record has fields like FIRST NAME, LAST NAME, ADDRESS, PHONE, etc.

                         ● Table WEDDINGS is used to create records that contain info about one particular event/wedding

                         ○ each record has fields containing the wedding info

                         ○ each record also needs to display info about two contacts

                         ■ i.e., WEDDINGS contains two sets of identical fields (CONTACT 1 FIRST NAMECONTACT 1 LAST NAME...CONTACT 2 FIRST NAMECONTACT 2 LAST NAME...) to display two separate records from CONTACTS

                         ● In WEDDINGS, there are also three neccessary fields: BRIDE field which combines CONTACT 1 FIRST NAME and CONTACT 1 LAST NAME, GROOM field which combines CONTACT 2 FIRST NAME and CONTACT 2 LAST NAME, and EVENT NAME field which combines CONTACT 1 LAST NAME and CONTACT 2 LAST NAME

                         ○ I need the BRIDE and GROOM fields because I used the full name of the two contacts to insert into a contract (in CONTRACTS table) and invoice (INVOICE table) which are both automatically created for each new record in WEDDINGS

                         ○ I need the EVENT NAME field for identifying and sorting purposes used in other areas of the database

                         I hope that adequately explains my goals.  Again, I appreciate your time and help with this.

                    • 7. Re: Two Table Relationship
                      philmodjunk

                           Try this approach:

                           Add a field, named Role, to Contact_Event. Set it up with a value list. For the purpose of this example, it can have three values: Bride, Groom and Guest, but additional values for Role (Best Man, Matron of Honor, ... etc) are also possible.

                           You can then  use either a filtered portal or an additional relationship to a different occurrence of Contact_Event to show the contact information for a contact with the specified role.