3 Replies Latest reply on Jan 18, 2013 2:37 PM by comment

    Reporting with joins, on a subset of one table

    lkeyes

      Hi... I've got a file of a members table, a committees table and a join table called CommitteeMembers. (diagram attached) I've been able to base a report on CommitteMembers which gives me all of the committees with all of their members. That seeems to work fine..

       

      There are two data-entry layouts: one for Committess and one for Members. (The join table doesn't have a layout, at least not yet!)

       

       

      Now, the users have asked to be able to choose a particular committee or subset of the committees, using a Find. The natural thing for them to do is to go to the Committees layout, and perform a find on the committee name. (Example..... a user wants members of all Diocesan Council committees from all districts, so they search for 'Diocesan Council" and get six committees:

       

      Diocesan Council Mission District 1

      Diocesan Council Mission District 2

      Diocesan Council Mission District 3

      Diocesan Council Mission District 4

      Diocesan Council Mission District 5

      Diocesan Council Mission Ex Officio Members

       

      However, since the report has to be based on the join table (to show both the committees and the members), I'm stymied as to how to tell the report that I just want the six committees that the user found when searching the committees table. I keep thinkning that there must be standard approach for this? Ideas?

        • 1. Re: Reporting with joins, on a subset of one table
          ch0c0halic

          Use "Go To 'All' Related records - in the Join table" from your committee table. Then do your standard Join Table report.

          • 2. Re: Reporting with joins, on a subset of one table
            Stephen Huston

            You can also do the committee search, then perform a GTRR to the related Members table at a Member list layout, using the members-based layout for the report.

             

            That has the limitation of being a bit harder to display if you need a sorted list by Committee where some members may be on multiple committees. In that case you will need a list report layout for the join table so that a single member can be listed under multiple committees in the report.

            • 3. Re: Reporting with joins, on a subset of one table
              comment

              lkeyes wrote:

               

              The natural thing for them to do is to go to the Committees layout, and perform a find on the committee name. 

               

              You could also do the find on the related committee name, while you are in the CommitteeMembers join table. This would find all join records related to the 6 commitees in your example.

               

              Alternatively, find the matching committees, then perform Go to Related Record [ CommitteeMembers; Match found set ]. However, this will be slower. IMHO.

               

               

              lkeyes wrote:

               

              (The join table doesn't have a layout, at least not yet!)

               

              How do you produce your report, then?

               

              Message was edited by: Michael Horak