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.
ExecuteSQL: A new way to count unique values in FileMaker 12
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?
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?
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.
Group A Total Sessions 15 Total Clients 3
Group B Total Session 6 Total Clients 2
A join table can be easier to work with than a return separated list.
But working from the relationship that you have:
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.
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
Correct. I had assumed that all members of a group where listed as part of that session.
So then is there a way to get the value that I need?
The sum the reciprocal method will work.
Are you using FileMaker 13?
Thanks Phil. Sum the Reciprocal worked and yes I am using FM13
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.
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:
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
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?
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.
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.