4 Replies Latest reply on Feb 5, 2012 2:39 PM by Brae

    How to set up table relation btwn people

    Brae

      Title

      How to set up table relation btwn people & group activities

      Post

      I created a database and need to fix table relationships that are not working the way I want. Newbie here. This should be simple but I am lost.

      The database keeps records of people and in which training activities they have participated. I have a table, People, and a table Training Activities.  

      On the form view for data entry on a people layout, I have a portal to show the list of training activities and a field called Participation Status to indicate whether each person participated in each training activity. I have a separate layout with a table view for entering the list of activities by date and description.

      Based on the way I set up the table relationships, this is just partly working. The problem is that the records of activities are not showing up in the portal rows. 

      (If it's easier to answer my question by looking at the file or a screenshot, I can share this privately.)

        • 1. Re: How to set up table relation btwn people & group activities
          philmodjunk

          Seems like you need a third table, Participation, so you can have one table that lists each person, one table that lists each training activity and a third table that links a Person record to an Activity record to log that person's participation in a given activity.

          If so, this is a typical many to many relationship and you can set it up like this:

          Personnel----<Participation>----TrainingActivities

          Personnel::PersonnelID = Participation::PersonnelID
          TrainingActivities::ActivityID = Participation::ActivityID

          WIth this set up a portal to participation on the Personnel layout will list all training activities in which that person participated. A portal to Participation on the TrainingActivities layout, on the other hand, will list all personnel that participated.

          You may find this demo file on many to many relationships helpful:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: How to set up table relation btwn people & group activities
            Brae

            Makes sense, but it's still not working. Thanks for the demo file Phil, however differences from that example to what I am doing make it difficult for me to make the translation.

            I created the 3rd table for the many to many relationship. Then pointed the portal to that 3rd table. But the portal just shows blank fields, no data (i.e, no list of training activities). 

            • 3. Re: How to set up table relation btwn people & group activities
              philmodjunk

              In your case, you can picture Personnel as contacts and Training Activities as Events.

              The portal to the join table will only list training activities if you create a record in that portal's table (the join table) and link it to an activity. Take a look at the basic set up layout in the demo file. Create a new record and then click in the first row of the empty portal and select a value from the drop down list. This creates a record in the join table linking an event with a contact.

              • 4. Re: How to set up table relation btwn people & group activities
                Brae

                OK and thanks again Phil, I think I can take it from here.