1 Reply Latest reply on Aug 22, 2016 8:38 PM by philmodjunk

    Summarizing Related Records in a Many-to-Many Relationship

    apricity

      I have an existing database that tracks people working on different projects. Although the actual database is more complicated, I have made a graphic that only shows the relationship and basic information related to the area of the database I have a question about. Essentially, each person can work on multiple projects and each project can have multiple people work on them.

       

      Screen Shot 2016-08-22 at 12.00.20 PM.png

       

      The database is working great but I have been asked to show on each person's profile which other people they have worked with. Essentially I want to show Employee that are related to other Employees based on their work on the same project. Ideally on each Employee's profile, I want a table like this:

       

      Other Employees worked with:

      Employee NameProject Title(s)
      John Doe

      Project A

      Project C

      Jane Doe

      Project B

      Project C

      Project D

       

      I started learning FileMaker about 8 months ago so while I am not a complete novice, I am still very new. If someone could help me get started down the right track on this problem it would be greatly appreciated. Do I need to change my database structure to achieve this functionality? Would a portal be involved?

       

      Thank you so much.

        • 1. Re: Summarizing Related Records in a Many-to-Many Relationship
          philmodjunk

          Try setting up different summary reports on layouts based on your join table. Perform a find for a given project on it and you'll get a list of all employees linked to that project. Find all records and sort by project and you can get groups of records under a sub summary header with the project info and the people listed under it are the people linked to that project.

           

          And you can turn this around and perform a find or sort records for a person to get a list of projects linked to that person. This can be done on a different layout also based on the join table.