Count records in relation that match...

[bkgray]

I have a one-to-many relationship from TableA (the one) and TableB (the many), matched on fields "teacherName" and "reportDate". A portal displays the correct set of records.

I want to include a set of calculated fields in TableA that contains the number of records from TableB in the relationship that contain a specific value in field "classPeriod".

I can easily count all records with the formula: Count ( TableB::teacherName )

But I can't figure out how to count how many of the records in TableB in the current relationship have field classPeriod = "1".

You advice will be appreciated.

bkg

[Breezer]

try using this calcultion.

Sum (TableB::ClassPeriod)

[T-Square]

Breezer--

That will only work if ClassPeriod is only 1; it will give an erroneous answer when there are entries for ClassPeriod > 1.

I would use a separate table occurrence (TableB-2) and relationship (TableA::Gmatch<->TableB-2::ClassPeriod) as a secondary match field, with a global field (Gmatch) in TableA for the match. Then Count(TableB-2::ID) would give the number of matching records.

[bkgray]

The additional relation using an extra field match works as you suggested.

When I re-read my initial posted question, I discoverer that I did not mention that I need to do this same calculation for a total of 9 classPeriod values. I guess I will script a loop to change the field to each of the required values, do the calculation and field updates, and then move on to the next one.

Thanks for the advice. I'm fairly new to FM8, and I keep forgetting about the power of additional views and relationships on the same tables.

I sure would like to see a function that looks like: CountIf(Table:field, Table:field = "X").

[bruceR]

Easily done with a custom function. I've done that with a version of GetRows but I haven't posted the new version yet. See http://www.briandunning.com/filemaker-custom-functions/results.php?keyword=getrows for the existing version.

[comment]

See the demo I have posted here.

[bkgray]

Thanks to both BruceR and comment for your examples.

We are at the end of the term right now, so it will take a few days before I can work on this again.

bkg