counting based on field contents

[leland]

I've come back to FM after years of web work using MySql and PHP and can't figure out how to do something that should be dead simple (and I could have done in 5 minutes in mysql and php). But I've been away from FM for so long, I've no idea how to go about it!

I've set up a student database at the school where I just started working. I've got a main menu nav page setup with links to the other layouts. What I'd like to do is show a count of students by grade level on the main menu page. Something like:

# of 6th graders = 31

and so on through 12th grade.

Can't use a simple calculation field because it only works with the current record. Perhaps there's some simple combination of summary and calculation fields?

Thanks.

Leland

http://www.renarts.org

[Fenton]

There is an "Aggregate" section (drop-down) in the functions, containing functions that Count and Sum fields. What is not readily apparent (because these functions were, I believe, in place before FileMaker was relational) is that they can do this for a single field in all records targeted by a relationship.

If you want to show a count of different grades you would first need some "hard-coded" calculation fields for the left-hand side of the relationship; simple ones with results of 1, 2, 3, ..., whatever you use to differentiate the grades.

Each one of these would be the left-hand side of a relationship, all of them pointed to the same "Grade" field. Then you would need a series of calculation fields to count each. Ex.

Count (relationship1::studentID), Count (relationship2::studentID), etc..

Yes it's a bit tedious. But that's what you'd need to show each total in Browse mode. Now, if you only want to show the total for a "chosen" grade, then you only need 1 field and 1 relationship; a global field on the left side, containing whatever number you type (or choose from drop-down list, or click on a radio button).

If you were only wanting to print this, then a Summary field in a sorted Subsummary part would do the trick, with no calculation fields or relationships needed.

If adding a bunch of fields (looks like about 24 fields, 12 relationships; not counting kindergarten, which could be 0?) seems messy, you could pull your "menu" into its own little file, and add them there. The hard-coded fields and relationships don't have to be in the same file as the data.

[joseppic]

Hi,

I am interested in printing subtotals at the end of a report so that in the scenario above, all grades and their totals are placed at the end of a report which details students.

I am using your 'Summary field in a sorted Subsummary part' but the summary result is at the end of each sorted group rather than placing all summaries at the botton of the report.

Is what I require possible doing it this way?

Thanks.