counting each group.

fmp 6.0U, winxp - I'm blanking on the solution.

I have a database with multiple records of mentors and their various mentees. I have a new record for each time they enter info about their mentee. To make it simple lets say there are 5 different mentors, and they each have 10-20 mentees. In a database that has 100's of records, how do I get a count of how many mentors entered info on their mentees?

The end result should be "5" since that is how many different mentors entered info through the year.

I'm messing up count, sum, summary, and sub-summary somehow to no avail.
--
"... respect, all good works are not done by only good folk. For here, at the end of all things, we shall do what needs to be done."
--till next time, consul -x- <>

Re: counting each group.

and thus ~consul inscribed ...
> fmp 6.0U, winxp - I'm blanking on the solution.
> I have a database with multiple records of mentors and their various
> mentees. I have a new record for each time they enter info about their
> mentee. To make it simple lets say there are 5 different mentors, and
> they each have 10-20 mentees. In a database that has 100's of records,
> how do I get a count of how many mentors entered info on their mentees?
> The end result should be "5" since that is how many different mentors
> entered info through the year.
> I'm messing up count, sum, summary, and sub-summary somehow to no avail.

Or, one other thing that may work for me is to just have an increasing incremental number next to their name that is sub-summary sorted by Mentor. Then I can just look at the last name and read that number.
--
"... respect, all good works are not done by only good folk. For here, at the end of all things, we shall do what needs to be done."
--till next time, consul -x- <>

Re: counting each group.

~consul wrote:
> and thus ~consul inscribed ...
>> fmp 6.0U, winxp - I'm blanking on the solution.
>> I have a database with multiple records of mentors and their various
>> mentees. I have a new record for each time they enter info about their
>> mentee. To make it simple lets say there are 5 different mentors, and
>> they each have 10-20 mentees. In a database that has 100's of records,
>> how do I get a count of how many mentors entered info on their mentees?
>> The end result should be "5" since that is how many different mentors
>> entered info through the year.
>> I'm messing up count, sum, summary, and sub-summary somehow to no avail.
>
> Or, one other thing that may work for me is to just have an increasing
> incremental number next to their name that is sub-summary sorted by
> Mentor. Then I can just look at the last name and read that number.


There maybe other methods, but I like this one:

"Calculating Total Number Of Unique Values In A File"
http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/endu ser/std_adp.php?p_faqid=1295

Marc

Re: counting each group.

In article , ~consul
wrote:

> fmp 6.0U, winxp - I'm blanking on the solution.
>
> I have a database with multiple records of mentors and their various mentees.
> I have a new record for each time they enter info about their mentee. To make
> it simple lets say there are 5 different mentors, and they each have 10-20
> mentees. In a database that has 100's of records, how do I get a count of how
> many mentors entered info on their mentees?
>
> The end result should be "5" since that is how many different mentors entered
> info through the year.
>
> I'm messing up count, sum, summary, and sub-summary somehow to no avail.

It will depend on how you have set-up the database and how / where you
want that "count" to appear.

If you only have one Table with those "100's of records" then a simple
calculation or Summary field won't work. A calculation normally only
works across the individual record it's in (the exception is when using
some functions across a Relationship or when using the Status / Get or
Design functions). A Summary field only works across every record in
the Found Set.

The simplest approach is to obviously perform a manual Find for each
Mentor's name in turn and write down on a piece of paper how many
record are Found. This may well achieve what you need.


If you actually want the "count" on-screen or printed on a report, then
there are various ways to achieve it, but the easiest way is to use the
"Value List Trick" - create a Value List and then count how many values
appear in the list.

BUT, this trick works across the entire database, not just the records
in the current Found Set. If you need it for just the Found Set of
records, then you'll need a different method.

First define a new Value List (from the File menu) called "Mentor
List". This Value List needs to obtain its values from the MentorName
field. The way FileMaker works means that the Value List will only
contain one of each unique name (so be careful of typos in the mentor's
names!).

Now you can create a Calculation field that "counts" the number of
values in the Value List using a couple of functions. The
ValueListItems function combines the individual values into a block of
text, with each value separated by a return character. You can then use
the PatternCount to work out how many return characters there are - add
1 to this (because the last value has no return character) and you have
a count of the mentor names.
eg.
MentorCount Calculation, Number Result, Unstored
= PatternCount(
ValueListItems(dbname, "Mentor List"), "{ret}") + 1

where:
- dbname is replaced by the actual name of your database file.
and - {ret} is replaced by the backwards 'P' return symbol that
appears on one of the buttons in the Define Calculation window.

If your database's records have data in the Mentor Name field like:

Fred Flintstone
Yogi Bear
Papa Smurf
Yogi Bear
Yogi Bear
Papa Smurf

then the ValueListItems function would retrieve the Value List (sorted
alphabetically) as:

Fred Flintstone{ret}
Papa Smurf{ret}
Yogi Bear

Then the PatternCount will count the {ret} return characters in this
list, giving a result of 2, to which you are adding 1 (to include "Yogi
Bear"), which gives the final result of 3 - the number of unique mentor
names.

Put this Calculation field on any Layout (perhaps in the Sub-summary or
Grand Summary of a report Layout) and you're all done. :o)


Notes:
1. You can see the Value List working by creating a Text field which
you put on a Layout and format it as a Pop-up Menu using the
"Mentor List" Value List.
2. You can see the ValueListItems function working by creating another
Calculation field defined as just ValueListItems (dbname, "Mentor
List") with a Text result (just remember to stretch the field tall
enough to display multiple names).

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)