14 Replies Latest reply on Mar 19, 2014 3:54 PM by 0614775139

    SubSummary problem

    0614775139

      Title

      SubSummary problem

      Post

           I am having a problem trying to get a report to work as I want it.

           I have a table of Groups and a table of Sessions. A Client is a member of a Group and they attend Group sessions.

           I have created a report based on the Sessions table which breaks by GroupID and Client ID which shows each Client and their total number of sessions. e.g.

           Group A         Total Session 15

                   John     3

                   Mary     5

                   Peter    7

           This works fine.  But I also want to show how many individual clients attended these sessions. In the above example it would be 3 but I can't seem to figure it out.

            

           Any suggestions anyone?

        • 1. Re: SubSummary problem
          philmodjunk

               A related table of clients would make it easier to produce that count.

               Otherwise, there are several methods that can be used including but not limited to:

               Sum the Reciprocal: How to count the number of unique occurences in field.

               and

               ExecuteSQL: A new way to count unique values in FileMaker 12

          • 2. Re: SubSummary problem
            0614775139

                 Thanks Phil,

                 I will try the reciprocal method in a few days when I am back at that client's office.

                 You mentioned that if I had a related table of Clients it would make it easier. I actually do have one. How would I do this using this table?

                  

            • 3. Re: SubSummary problem
              philmodjunk

                   Be aware that the sum the reciprocal method is a bit strange. Many have trouble getting it to work do to all the "moving parts" that all have to be put in place just right.

                   Using a table of clients to get the number of clients in each group will depend on how you assign clients to groups. Can a client be a member of more than one group or can they only be assigned to a single group? What is the relationship that you have set up to link clients to groups?

              • 4. Re: SubSummary problem
                0614775139

                     The Clients Table has a field GroupID. Clients can be members of more than 1 group.  

                     They are assigned by adding the ID of the Group to the Client's GroupID field.

                     So if a Client is a member of 2 Groups the GroupID field for that Client would have both IDs in there separated by a return creating a multikey:

                     From Groups to Clients the relationship is based on the GroupsID to the Client's multikey GroupID.

                     The Sessions table also has a GroupID and a ClientID field. In the example for Group A below there would be 15 Session records.

                     So what I want is the number of unique Clients for each Group not for all Groups.

                     e.g.

                     Group A   Total Sessions 15        Total Clients 3

                            John    3

                            Mary    5

                            Peter   7

                     Group B   Total Session 6     Total Clients 2

                            John    2

                            Sam    4

                • 5. Re: SubSummary problem
                  philmodjunk

                       A join table can be easier to work with than a return separated list.

                       But working from the relationship that you have:

                       Clients>-------<Groups

                       Clients::GroupID (list) = Groups::GroupID

                       You can define a field in Groups as:

                       Count (Clients::GroupID )

                       to compute the number of clients in each Group. With that field defined, in Groups, you can include it in your summary report to show the number of clients in each group.

                  • 6. Re: SubSummary problem
                    0614775139

                         Thanks for your replies Phil but I don't think that will give me what I want as your solution is independent of the sessions.  Your suggestion will give me the number of clients in each group.  

                         What I need is the number of clients in each group for the sessions I have in my found set. That is why I based the report on the Sessions table.

                         So if I am analysing a period of time like the last 3 months sessions I need to know how many individual clients attended those sessions.

                         e.g. Group A has 40 clients as members

                         If i find the last 3 moths sessions it may be that only 15 of those members attended the sessions so I want the answer 15 not 40.

                         I can get the report to show me the list of the clients that attended the sessions like in my example. All I need to do is total this number

                    • 7. Re: SubSummary problem
                      philmodjunk

                           Correct. I had assumed that all members of a group where listed as part of that session.

                      • 8. Re: SubSummary problem
                        0614775139

                             So then is there a way to get the value that I need?

                        • 9. Re: SubSummary problem
                          philmodjunk

                               The sum the reciprocal method will work.

                               Are you using FileMaker 13?

                          • 10. Re: SubSummary problem
                            0614775139

                                 Thanks Phil.  Sum the Reciprocal worked and yes I am using FM13

                            • 11. Re: SubSummary problem
                              philmodjunk

                                   With FileMaker 13, it's possible to set up a relationship that is found set based to get the relationship based "counts" to only count related records that are also in your found set. Since you have the other method working, it's probably not worth it to go the other route, but there's a new summary field type that returns a list of values from all records in your found set and GetSummary can return the list of values for just a sub summary group in your report.

                                   Thus, a calculation field using get summary could generate a list of primary key values and if you used that calculation field as a match field, you could get the needed counts.

                              • 12. Re: SubSummary problem
                                0614775139

                                     I thought I would try this method as well but can't get it to work.

                                      

                                     I have defined a summary  field sClientIDs which is a List of ClientIDs. Works.

                                     It generates a list like this:

                                     1456

                                     1456

                                     2345

                                     4539

                                     4539

                                     4539

                                     I have defined  a calc field cClientIDs which is GetSummary ( sClientIDs ; ClientID) but this doesn't work, it simply returns a list of the first Client ID

                                     1456

                                     1456

                                      

                                     I have both these fields on the SubSummary part that is based on the GroupID sort.

                                     I would have thought that these two fields should produce the same list?

                                • 13. Re: SubSummary problem
                                  philmodjunk

                                       The second parameter, the "break" field has to be the same break field that you specified for the corresponding sub summary layout part. This is the "when sorted by field". From your original post, that would seem to be some kind of Group name or ID field.

                                  • 14. Re: SubSummary problem
                                    0614775139

                                         I actually did try that but it returned an empty list.  The SubSummary part is based on a sort on related field which gives me the group name.

                                         So I thought I would create a new field GroupName in the base table (Sessions) to auto-enter the GroupName and changed the part and sort to be based on the local field.  It worked.

                                         So there seems to be an issue with this when the part it is based on a related field.

                                         Thanks again for your help. Have learnt a lot from this.