2 Replies Latest reply on Nov 7, 2011 9:09 AM by Brackintosh

    Pull specific values from a related table to a column in a report



      Pull specific values from a related table to a column in a report


      I'm sure this has been asked many times before, but I was unable to find the right keywords to get an answer. I'm hoping that someone can point me in the right direction.

      I have a Project table that has related join table that matches up employees with the role that they're performing for that Project. Something like this:

                       Projects  ------- TeamRole ------- Employees

      There is a Role field in the TeamRole table and the information is displayed and entered from a portal on the project screen. Now i've been asked to produce a report that lists some roles as a column in a list report. So I need to be able ro report on a project and have the employees that are Project Manager, Developer, Interactive Designer and Quality Assurance for that project appear in a column on the report. Like this:

          Project Name   Description   Date Started    Project Manager    Deveoper   Interactive Designer   QA

      How is the best way to do this? I was able to put a filtered portal in the row and see the team members on the report that way. But it doesn't feel like it is the proper way to do this. I would rather be able to have a calculated field or a relationship on the graph that will let me "grab" the actual value. I'm sure I'll be needing to use the information later on as development continues.


      Thanks for any help.

        • 1. Re: Pull specific values from a related table to a column in a report

          I'm guessing that you have this relationship:

          Projects  -------<TeamRole>------- Employees

          So that TeamRole is a "Join" table listing the team of employees assigned to a given project and documenting their role on the team for that specific project...

          I suggest to stick with the filtered portal approach with a filter expression set for each such one row portal. The alternatve you describe can be used, but requires defining special calculation fields and added table occurrences so that you can set up "role specific" relationships for each column. That's how we used to do this (and still do when working with older versions) before portal filtering was added in the release of FileMaker 11.

          Here's how you would set up just the column for Project Manager:

          Define a calculation field in projects: constProjectManager. Make "Project Manager" the sole term in its calculation.

          Use the duplicate button (two green plus signs) in Manage | Database | Relationships to make a copy of TeamRole.

          Set up this added relationship:

          Projects::ProjectID = ProjManagerTeamRole::ProjectID AND
          Projects::constProjectManager = ProjManagerTeamRole::Role

          Define a calculation field, cMemberName in TeamRole to evaluate from the context of team role as Employees::Name

          Now you can add ProjManagerTeamRole::cMemberName to your projects report layout to list the project manager. You'll need to add a const field and an additional relationship for each such column on the report. See why a filtered portal is easier? (You can hide the boundaries of your portal so that it looks like any other field on your layout.)

          • 2. Re: Pull specific values from a related table to a column in a report

            Thanks so much PhilModJunk! 

            I wanted to make sure I was doing things the right way and wasn't sure the filtered portal approach was correct. Now i can easily finish the report.