1 2 3 Previous Next 30 Replies Latest reply on Nov 3, 2010 10:14 AM by Sketter

    Help with a summary report

    Sketter

      Title

      Help with a summary report

      Post

      Hi, so I have been trying to get a summary report to group records based on a "leader" and "Member" criteria.

      To give a bit of a history, here is a link from a previous post Help with go to record button (if you scroll towards the bottom, the last few by Phill is the structure I'm using)

      So I have used a sub summary report that Phill has illustrated in his tutorial in other circumstances that worked great.

      But in this situation, I don't know where exactly my problem is.
      I can't get the grouping correct.

      I attached an image to show my relationships and my TO.

      (For the image, I have two tables that are Contact Management with TO of Contact Management, Members, Leaders, and coworkersinfo.

      And second  Table C_Storage, with TO of TMembers, TLeaders, and CoWorkers)

      What i'm trying to do is get a report that sorts the records into groups based on who their leaders are.

      img968.png

        • 1. Re: Help with a summary report
          philmodjunk

          Is this what you want?

          Leader: John Smith

          Members:
          Jane Doe
          Jim Johnson
          Fred Flintstone

          Leader Jane Smith

          Members:
          //and so forth

          If I remember the details of your project correctly, a given person can be a member of more than one team. If that were not the case, you could base a summary report on TMembers and pull data from TLeaders to place in a Sub Summary part at the head of each team.

          I know of two options, neither of which are ideal, but can be made to work.

          A. Don't use a summary report, but instead make a list type report based on TLeaders with a portal listing the team members. Make the portal many extra rows tall and set it to slide up/resize enclosing part. If you don't make the portal tall enough, some members will be missing on the larger teams.

          B. Create a report table and import the names for each team into this table one team at a time. This table can then serve as the basis for your summary report.

          • 2. Re: Help with a summary report
            Sketter

            "If I remember the details of your project correctly, a given person can  be a member of more than one team. If that were not the case, you could  base a summary report on TMembers and pull data from TLeaders to place  in a Sub Summary part at the head of each team."

            What problems could happen if i attempt this format?  If person has more then one lead, they will only show up in one list?

            • 3. Re: Help with a summary report
              philmodjunk

              Among other problems. For that method to work, you need  separate record for each person listed in the report body which is where option B comes in as a possible solution.

              • 4. Re: Help with a summary report
                philmodjunk

                Hmm, I think I've been misinterpreting your relaationship graph.

                Tmembers is a join table, correct?

                If so, a summary report based on that join table should work even with one person being a member of multiple teams as the requisite records will indeed be present in this table.

                • 5. Re: Help with a summary report
                  Sketter

                  Here is a rough presentation of how it looks.

                  http://img534.imageshack.us/f/grouppi.jpg/

                  In each record there is a tab with these tables.

                  If a person is a leader, we just start adding names from the Followed By.

                  If the person follows someone, we add who their leader is in the Committee Leader

                  The grouping, allows none group leaders to view who is in their group. (this isn't applicable when under Committee leader since it already shows up their followers in Followed By)

                  Attempting to do the report with TMembers as the layout table, and then using Tleaders:Cleaders in the sub summary part, sorting by CLeader, and then sorting the records by TLeaders:CLeaders, doesn't seem to work.

                  http://img651.imageshack.us/f/group2nc.jpg/

                  I seem to getting a list of leaders.

                  • 6. Re: Help with a summary report
                    philmodjunk

                    Instead of Tleaders, I'd take the data from Contact Management to show the leader data in the sub summary part as TMembers::CLeader links directly to it. Then fields from Members can be placed in the body to display the name of each team member.

                    • 7. Re: Help with a summary report
                      Sketter

                      So that my layout table is TMembers

                      Part sort TMembers:CLeaders

                      Part fields from Members

                      Sort Records TMember:CLeader

                      Body Fields Members?

                      This setup seems to be working.  It is however adding the leader twice, once in leader, and once in the grouping.and names are that are in two groups works.

                      (names on right are the leaders)

                      http://img257.imageshack.us/f/group3n.jpg/

                      Is there a way to filter out leaders in the groups?

                      • 8. Re: Help with a summary report
                        philmodjunk

                        Seems like you have the basic idea in place. Are team leaders listed as a member of their own team? That would explain their names appearing twice.

                        Looks like you'll either need to perform a find that omits team leaders from the Tmembers table or you'll need to remove the Team Leader name from the sub summary part of the report.

                        Here's a simple way to set up a find that omits the team leaders from the body of the report:

                        Define a calculation field, cLeaderFlag, in TMembers as: CLeader = CMember

                        This will return 1 (True) for the leaders and 0 (False) for the members.

                        Now perform a find on this layout, specifying 0 in the cLeaderFlag field.

                        • 9. Re: Help with a summary report
                          Sketter

                          Just for the record, TMembers and TLeaders using the same Table. (C_Storage)

                          So i would be adding cLeaderFlag in C_Storage..

                          Just saying this just i case..

                          Update:

                          I think you ment it to work if the IDs where the same?

                          http://img833.imageshack.us/f/group4v.jpg/

                          Should TMembers and TLeaders be in their own separated Tables?  Does that make a difference in all this?

                          • 10. Re: Help with a summary report
                            philmodjunk

                            A combined table will work, you just have to perform a find to filter out the leader records.

                            Yes, if the two ID fields are identical, then the expression should return 1 (true). From your screen shot, that isn't the case but I can't tell from a screen shot why it isn't.

                            • 11. Re: Help with a summary report
                              Sketter

                              Ok i misinterpreted the report.

                              In that screens shot, Ruth and Maureen both fall under Johanna, represented by the 4055 I had Maureen next to 4055 cause i was using the wrong field data.  I've corrected that now, and the CLeader names are not showing up twice.

                              http://img412.imageshack.us/f/group5e.jpg/

                              Thanks so much Phil!!

                              • 12. Re: Help with a summary report
                                Sketter

                                Now just for tracking sakes, is there a way to sniff through records that are not associated to any group? This is for just in cause some people fall through the cracks and were not assigned a group.

                                I'm assuming this must be done in the Contact Management table, i'm just not sure how I would perform the find.

                                • 13. Re: Help with a summary report
                                  philmodjunk

                                  You can perform finds that search for the presence or absence of a related record.

                                  From Contact Management, you can put an * in the Tleaders::CMember field, select the omit option in the status area and perform the find.

                                  This can also be done in a script.

                                  Enter Find Mode[]
                                  Set Field [TLeaders::CMember ; "*"]
                                  Omit Record
                                  Set Error Capture [on]
                                  Perform Find[]

                                  • 14. Re: Help with a summary report
                                    Sketter

                                    That search works thanks!

                                    One very last thing.

                                    (referring to http://img534.imageshack.us/f/grouppi.jpg/ )

                                    When clicking the on CLeaader or CMember drop down list, i get a list of every record in the contact management table. I was wanting to set up a filter (unfamiliar with how that works) so that so i can have it only show the department that the contact is from.

                                    So for example if I click on CMember, i get everyone from Department A B and C. That person is from department A, how can i get that drop-down only to show people within department A?

                                    1 2 3 Previous Next