5 Replies Latest reply on Apr 7, 2013 6:22 PM by RickWhitelaw

    Combining records from multiple tables into one layout

    ZachAlexander

      Title

      Combining records from multiple tables into one layout

      Post

           Hi all,

           I'm pretty new to Filemaker, and I've mostly been figuring out how it works (it's pretty intuitive), but I've just hit a snag.

           The organization I work for has an entity called the Corporation, which consists of people from three categories: (1) representatives appointed by external organizations, (2) officers of the organization's Board, and (3) the chairs of the standing committees of the organization's Board. 

           Each of those things is represented by its own table -- a CorporationRepresentatives table, a BoardOfficers table, and a CommitteeMembership table, which has a field for "chair". (All of these tables have start and end date fields, and are related to records in the People table.)

           Here's what I'd like to be able to do: design a layout with a "Date" field at the top, and upon entering a date, everyone who was a member of the Corporation on that date (for whichever of the 3 reasons above) will display below.

           Ideally this would be organized into categories; a section called "External representatives", a section called "Officers of the Board", and a section called "Board Committee Chairs". 

           Perhaps there's an easy solution, but I've been Filemakering all day and my brain is a bit fried. Much appreciation in advance for your expert advice.

           Thanks,

           Zach

        • 1. Re: Combining records from multiple tables into one layout
          philmodjunk

               The main problem is that you have data from 3 different tables. Unless you can combine the data in a single table, you can't use a list view to list all of the records. You could, however, define three portals for listing the records from each group. If there is a consistent number of people listed from each table, that might work fairly well for you.

          • 2. Re: Combining records from multiple tables into one layout
            ZachAlexander

                 Thanks PhilModJunk.

                 My previous approach was, in fact, to have a single CorporationMembership table, with fields that would incorporate the other information (appointingOrganization, boardOffice, boardCmteChair for example), but this duplicates information in the other tables, of which at least the latter two seem necessary as tables.

                 Perhaps there is a way to set up the CorporationMembership table such that it automatically copies relevant data from the other tables, so we don't have to rely on manual (and error-prone) data entry? I haven't learned about lookups (if that's the term) yet, because I got the impression you want to avoid using them if possible.

            • 3. Re: Combining records from multiple tables into one layout
              philmodjunk

                   What you have, I think, is a basic "many to many" relationship. A given person can be a member of one or more groups--either at the same time or at different times over the years. Yet each group, of course, lists multiple members.

                   You can thus setup these Relationships:

                   People----<People_Group>-------Groups

                   People::__pkPeopleID = People_Group::_fkPeopleID
                   Groups::__GroupID = People_Group::_fkGroupID

                   A portal to People_Group on a People layout can be used to assign a given person to multiple Groups. A portal to People_Group on a Groups layout can be used to assign multiple people to that group. The only fields that you would define in People_Group are the match fields and any additional fields needed to record information specific to that person's membership of that specific group. That might include a field to identify the time period for which they are a member and any specific role they may have (such as "Chairperson") as a member of that group.

                   Your report then, can be created on a layout based on People_Group.

                   Here's a download link for a demo file that illustrates different options for working with a Many to Many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Combining records from multiple tables into one layout
                JohnEriksson

                     I would try a different approach. I try to keep things as simple as possible. As they are all people, I would haev only one table called just people. No relations, and checkboxes for which positions one person have.

                     If the name of the position might change in the future, have another table with position names and ID, make a value list based on this table where it saves only the ID but shows the name.

                • 5. Re: Combining records from multiple tables into one layout
                  RickWhitelaw

                       I would use Phil's approach. In the end you'll find it simpler to track when a person ceases to be a member of a given group or moves to another by tracking dates etc. as he said, it's a classic many to many situation that requires the "middle" table.