Trying to count related records matching a condition

[oakbridge]

Using FM6, I've got a calculation field that is attempting to count the number of related records that match a condition. The code I am using in the calculation field is:

Case(Client License Changes::date_submitted ≥ Date(7, 1, 2006), Count(Client License Changes::license_change_id))

I am trying to get the number of related records in the Client License Changes file that were submitted after July 1st 2006. Unfortunately the result continues to be equal to blank.

This is one of those things that I could swear I had working a week ago during the initial development and testing, but it fails to work now.

Can anyone shed some light on what I am doing wrong?

Thanks,

[John Mark Osborne]

The problem with your formula is the test portion of the Case statement. While Aggregate function (like Count) can look at all the records related to a master record, a simple reference to a related field only looks at the first related record. I think the easiest way to approach the problem would be to create a calculation called LicenseFilter in the Client License Changes file something like this:

Case(date_submitted ≥ Date(7, 1, 2006), 1)

Then, change your current formula in the other file to the following:

Sum(Client License Changes::LicenseFilter)

[oakbridge]

That appears to have solved my problem, thanks for the suggestion. One question, has something changed between FM6 and FM8 because when I took the same files and converted them to FM8 and ran it there, it appeared to work correctly.

Thanks again,

[John Mark Osborne]

The calculation you originally posted won't work in any version of FileMaker. Sorry.