9 Replies Latest reply on Nov 29, 2011 4:43 PM by aklobby

    Relating records three tables away

    aklobby

      Title

      Relating records three tables away

      Post

      I have a database that has three parent tables joined by child tables. I would like to see information from a parent record in another parent record that is separated by one parent and two join tables. Is this possible? 

       

      group=parent        joined by group assignment        people in group = parent     joined by activity sponsors      activity= parent

       

      I want to see information from the group table on the activity table. I can easily display the information I want to see in a portal within the group table. I am hoping to combine information from the two tables into one letter I send out. 

      I am using Filemaker Pro 11

      Any help would be greatly appreciated. 

      Allison

        • 1. Re: Relating records three tables away
          LaRetta_1

          Hi Allison,

          Your request is unclear, I'm afraid.  It would work best if you can create a simple demo file with just a few records in it to illustrate your existing structure.  You cannot attach fp7s here so you will need to upload to a free site such as 4shared.com.

          • 2. Re: Relating records three tables away
            philmodjunk

            Not as I understand the relationships. It's not the number of relationships and table occurrences that's the problem, it's the fact that you have two many to many relationships between Groups and Activity.

            From a layout based on Activity, you could see any number of records from groups in a portal, but this list would not have any meaning here.

            Please describe the purpose for wanting to see fields from Group on an Activity layout.

            I think you have these relationships:

            Group::GroupID = GroupAssignment::GroupID
            People::PeopleID = GroupAssignment::PeopleID
            People::PeopleID = ActivitySponsors::PeopleID
            Activity::ActivityID = ActivitySponsors::ActivityID

            Please post any corrections to these relationships if I have misunderstood.

            • 3. Re: Relating records three tables away
              aklobby

              PhilModJunk, 

              You have the relationships correct. The reason I want to see the group on the activity layout is I want to produce a contact list for the people in the group based on the activity. I want to be able to say contact the following group members to discuss the above mentioned activity. 

              I think my problem is the activity sponsor table is getting in the way of the group assignments and the activity, but that table resolves my one to many relationship between People and Activity. 

              Is it possible to create a new table occurrance of activity and directly relate that to group? 

               

              Hope this helps. 

              Allison

              • 4. Re: Relating records three tables away
                philmodjunk

                You can certainly put a portal to Group on your activity layout, but you may find more groups listed than you expected. According to what you have posted here, a given Sponsor can be a member of more than one group. When that is the case, a group that has nothing to do with the activity may be listed just because the activity's sponsor is also a member of that second group.

                Just from what has been posted so far, I don't see any way to identify and exclude such groups. Perhaps that's not a concern here though. You may in fact want all groups for a given sponsor listed in this case.

                If, on the other hand, an activity is actually sponsored by a group instead of an individual, then your relationships need to be changed to link activities to sponsoring groups instead of sponsoring individuals.

                • 5. Re: Relating records three tables away
                  aklobby

                  Okay, I will work on changing the relationships. Is there a way to do a mail merge type thing with information from two unrelated tables on one document. That might solve my problem if I could do that. Otherwise I can send two different documents in an email to accomplish the task.

                  Thank you for your assistance.

                  Allison

                  • 6. Re: Relating records three tables away
                    philmodjunk

                    I don't see any "unrelated tables" in your database. Can you be a bit more specific?

                    Describe how you would set up those "two different documents" for emailing. I suspect that a change is needed for your relationships and with the right chagne, you won't have any unrelated tables to worry about here. Keep in mind that you can define multiple relationships between the same two tables by using different table occurrences for one or both of the related tables.

                    • 7. Re: Relating records three tables away
                      aklobby

                      Can you point me to a good tutorial on Table Occurrances? I am struggling a bit with getting those to work.I know I must be missing something easy.

                      The labor intensive way to send the two documents is in two separate emails. I would like to see if I could attach to records to an email and send in one email. I have not spent time trying to do that yet.

                      Thanks again,

                      Allison

                       

                       

                      • 8. Re: Relating records three tables away
                        philmodjunk

                        The labor intensive way to send the two documents is in two separate emails. I would like to see if I could attach to records to an email and send in one email. I have not spent time trying to do that yet.

                        I understood that you would have two separate sets of data sent in two different emails, but what I am asking is what would you list in these two emails and how would you pull up the data for each? (I don't think this will be necessary and if you explain how you would do it, I can probably suggest a relationship change that will make it unecessary.)

                        Here's a tutorial on table occurrences: Tutorial: What are Table Occurrences?

                        • 9. Re: Relating records three tables away
                          aklobby

                          Reconfiguring the relationships and adding multiple table occurrances worked great. 

                          Thank you for the guidance. 

                          Allison