Counting the number of occurances a value appears in a given field

[Ben Feingold]

I'm trying to determine the number of times a particular value appears in a given field. ie:

The field Students::BEHAVIOR_CODE will contain a number from 1 to 30. I want to create a field (RelatedDatabase::BEHAVIOR_17) in a related database that will look at BEHAVIOR_CODE and count the number of times 17 (for example) comes up. If it exists 50 times, I want BEHAVIOR_17 to return the value 50.

Any ideas? Am I making sense?

[Raybaudi]

ValueCount ( FilterValue ( List ( Students::BEHAVIOR_CODE ) ; 17 ) )

[Ben Feingold]

Is FilterValue() a custom function? Nevermind, it's FilterValues(). Found it.

-Ben

Edited July 9, 2008 by Guest

[Ben Feingold]

Okay. So now here's a new twist:

In that same code, I only want it to count entries that have occurred within a set date range. I've created a field called InRange that returns a "1" if it is in the defined date range. Can I limit my count (or list) to only include entries where InRange=1 ?

- Ben

[comment]

It sounds like a problem that needs to be solved through tables and relationships, not calculations. Certainly, having 30 calculation fields, one for each code, is not a good idea.

Can you explain what is this behavior code and what is the related table that does the summaries?

[Ben Feingold]

Can you explain what is this behavior code and what is the related table that does the summaries?

I guess I should give a little background. This database was designed to track student behavior at a special ed school. In the last three years, with 60 students being tracked, we have collected over 40,000 entries in the [color:green]Behavior Record Database. Our [color:green]Students database focuses on student information like contact info, medical information, etc. These two databases are connected by the shared field "Student ID."

I am attempting to do two things:

1. Create a layout in the [color:green]Students database that tally's the number of each type of consequence (a behavior code; 1-30) for a given date range.

2. Use that data to create a pie graph with xmChart. Eventually I would like to create other charts with other data sets, but for now I figured I'd stick with something "simple" (lol) like a pie chart.

Edited July 10, 2008 by Guest
Elaboration

[comment]

The "native" Filemaker way to do this is to produce a report from the Behavior table, sub-summarized by student and by code. To limit the records by date range, you would perform a find before producing the report.

To get similar data "live" in Browse mode is a bit more difficult. The simplest way, I think, would be to add a table of Codes, that would be related to Behavior by Code. Next, you would define three global fields: gStudentID, gStartDate and gEndDate, all in the Codes table. Now you can create a new relationship between Codes and a new occurrence of Behavior as:

Codes:Code = Behavior 2::Code

AND

Codes:gStudentID = Behavior 2::StudentID

AND

Codes:gStartDate ≤ Behavior 2::Date

AND

Codes:gEndDate ≥ Behavior 2::Date

Now you can view and sum the related Behavior 2 records (for the selected student in the selected range) from a list view of Behavior - but in order to change the view to another student, you need to select their StudentID in the gStudentID field.

This could be smartened a bit by putting a portal to Codes on a layout of Student, showing all code records together with summary data for the selected student. But this will require scripted navigation of the Students table, because again the selected gStudentID in Codes must be synchronized to the currently viewed student in Students.

Another option is to use the so-called "Ugo method" to de-dupe the portal - you can find quite a few examples of that here in the forums.