8 Replies Latest reply on Jan 11, 2011 11:47 AM by philmodjunk

    Relating two records on a table



      Relating two records on a table


      We use our database for standard constituency tracking for our arts organization.  We track records of individuals that take part in our programs (generally youth), donors, parents, etc. In particular, we have a camp every summer and we track both participants and their parents.  These are related through 'groups' that we create.  That way on a camper's record we can see who their relatives are and how they are related.

      I can't seem to find a way to relate two records to one another in the table view.  I want to be able to view a table in which I can add a column that displays the main relative to the record in question.  For example, if I were to query for campers in our past programs, I would also like to see who their parents are.  Is there a way to do this? 

        • 1. Re: Relating two records on a table

          Yes, but how are your currently tracking this? Need to know whether your current table definitions and relationships can be used for this or if you'll need a structural change first.

          These are related through 'groups' that we create

          Are these groups, records in a separate, related table? Or via some other way? If a related table, how do you relate a group record to your participants table?

          • 2. Re: Relating two records on a table

            These groups are recorded in a separate, but related, table.  They are made up of individual constituents.  Individual records are related to groups through group ID numbers.  So, individuals can be associated with a group through a reference number that relates them all together.

            • 3. Re: Relating two records on a table

              Does a "group" then represent a family?

              Can an individual be a member of more than one group? (Given modern families, this can happen even with families in some situations.)

              • 4. Re: Relating two records on a table

                It can represent a family, a professional affiliation, or whatever group that is useful for keeping track of specific constituents.  An individual can be included in several different groups.  

                • 5. Re: Relating two records on a table

                  As in, groups are just ways of grouping together constituents in whatever way we want.  We also have the ability to contextualize the relationship within groups by selecting from a drop down menu: Parent, Sibling, Child, etc. 

                  • 6. Re: Relating two records on a table

                    That makes this a many to many relationship. This is usually managed in FileMaker in this way:

                    Individuals----<Individual_Group>------Groups (>---- means many to one)

                    Individuals::IndividualID = Individual_Group::IndividualID
                    Groups::GroupID = Individual_Group::GroupID

                    You can define a field in Individual_Group to document an individual's relationship to that group, (Father, Mother, Child, President, Member... etc.)

                    You can then place a portal to Individual_Group on an Individual layout, adding fields from Groups to the portal as needed and you will get a list of all groups of which that individual is a record. Place this same portal on the Groups layout to list all individuals that are a member of this group.

                    • 7. Re: Relating two records on a table

                      Well, it seems our database is already fitted to do this.  On our contact management page, we have a tab called "related contacts" that acts as a portal to display all individuals within a group, and their relation.  

                      My initial query was referring to displaying all contacts on a table (that can be later exported into an excel file) that includes a column dedicated to displaying relatives, if that individual is associated with a group.  I can't seem to be able to make a column that displays this information. 

                      • 8. Re: Relating two records on a table

                        Question: Which group for a given individual? Since an individual can be a member of more than one group. How do you determine which group to list in this column?

                        This sounds like something you can put together with a List function in a calculation field, but I'm not totally clear on what you want to see here.

                        List ( RelatedTable::Field ) can produce a list of values drawn from all the related records, so this could list the members of a group.

                        Substitute ( List ( RelatedTable::Field ) ; ¶ ; ", " ) can turn that list into a single line with each value separated by a comma instead of a return--which may serve to produce the list you need for export to Excel.