That is, I need help designing a calculation field to count records that match multiple field criteria

Example of what I am trying to do:

I have a summer camp registration database

I have records that contain, among other things, the following data for each camper:

Sex of the camper

Session attending (July or August)

Grade of the camper

If I want to design a calculation field that gives me a count of (the 4th Grade)( boys) (who will be in camp in August), how can I do that?

In other words I want to design a calculation field that will give me a count of the records that match these multiple (3) criteria

Should I stop here? Or should I say that in Excel I have used formulas containing nesting "if" statements to count the number of "records" in a spreadsheet table that match multiple criteria over a few columns. Here is a sample of such an Excel formula.

=SUM(IF(($L$2:$L$338=$M$350)*($M$2:$M$338=O353),1,0))

This formula was used in the calculations related to the responses to a long and complex community survey. It counted for me the number of rows (records) in the responses where a person who had lived in the community for a certain amount of time, ($L$2:$L$338=$M$350) was within a specific age range ($M$2:$M$338=O353),1,0)

Can I build a similar formula into a calculation field in FileMaker that will count records that match multiple field criteria? A calculation that will count up the number of records in the database that match 4th grade, that match boys and that match campers registered for the August session so that I can see at a glance the number of 4th Grade boys who will be in camp in August?

chaversteve

Summary fields might be able to do something similar. Use calculated fields to do the equivalent of your Excel IF statements.

However, it might be faster and easier to script a search and count the number of records found. Not as elegant though.

For speed for the user I would use a relationship -

Create three global fields, either in the same file or another,

G_Gender, G_Session,G_Grade.

Create three calcs, one a concatenation of the three new globals.

One a concatenation of the Gender ,session and grade in your registration db and

A RecordIsOne field ie. a calc with "1" as the calculation and therefore fixed result.

Create a new relationship between the two calcs.

Add a calc Sum(RelationshipName::RecordIsOne)

By entering your search criteria in the 3 globals you should get the record match result.

This will only give a result when all three criteria are used but you could make the gender ,session and grade concatenation a multiline field to allow matches when one or more of the search criteria is empty