Jump to content

counting based on field contents

This topic is 7453 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?




Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 weeks later...


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?


Link to comment
Share on other sites

This topic is 7453 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.