getting related records total

[mikeytt]

Hopefully someone can help me with this.

I have two tables (Property and Report) related by id number. So one Property has many Reports.

Reports have a creation date field

On the the Property layout, I want to display the number of reports which fit a date range. I have date_start and date_end global fields on the Property layout/table for this purpose.

On the REPORTS table I have the following calculation called MATCH_DATE_RANGE =

If ( creation_date ≥ PROPERTY::date_start and creation_date ≤ PROPERTY::date_end ; "1" ; "" )

So if the creation date falls within the dates specified on the PROPERTY table the result is 1.

I then have a self join based on id in the REPORT table

Finally, in the PROPERTY table i have a calculation called MATCHES =

sum ( REPORTS_SELFJ::MATCH_DATE_RANGE )

This returns the number of reports for any given property that match the date range.

This works fine on a mac with FM 9, but does not work on a PC on using FM 8. The MATCHES field does not update when the dates are changed. It only updates if I go into FILE > MANAGE > DATABASE.

The calc fields are NOT indexed. Going back one record and then forward also does not update. Tried clearing cache and that has no effect.

Am i doing something fundamentally wrong, or should I be using another method to do this?

I've attached an example file, but as i mentioned, it work on a mac with fm9, but not on a pc with fm8 (where it is destined to be used!)

Many thanks

Example.fp7.zip

[comment]

How about this way:

Example2.zip

[mikeytt]

Fantastic - exactly what i was after. Thank you ever so much.