Jump to content

Designing a calculation field to count records


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

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7563 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.