Constrained Find on Unstored Calcs

[Peter Walker]

We have a scenario where we’re doing a constrained find against an unstored calculation field that is giving unexpected results.

FM9.0v3 and 10.0v1

Mac OS X 10.5.6 and WinXP

Files served on FMS9.03.325 on Win2003

There is a table called “Screenings” with a self join relationship, “Screenings_Screenings|AccessionCOD”. The relationship uses two fields:

- AccessionNumber = Accession Number

- COD = COD

There is a field named, “_cDuplicate” whose calculation reads:

Count ( Screenings_Screenings|AccessionCOD::ScreeningID ) > 1

ScreeningID is the Primary Key in the table. The unstored calculation returns a 1 if true and a zero if false. If we examine the records, the calculation is working as expected.

We have a process that performs a Find on a field named "Locked" (indexed, number, value 1 or blank) that results in a Found Set (say 1,000 records) and then performs a subsequent Find by setting the "_cDuplicate" field to "1" and using "Constrain Found Set". When we do this, the process returns the current found set instead of the expected constrained set. We can verify that the Found Set of 1000 records does contain a subset of records where "_cDuplicate" = 1 but the constrain doesn't find them :-(

We have been able to rule out:

- Corrupt data (good clone, create new data, test again)

- Corrupt relationship (created a new one from scratch, test again)

- Corrupt _cDuplicate field (created a new one from scratch, test again)

- Corrupt file (took backup, save as compacted and tested again, recovered and tested again)

We should be able to the constrained find on the unstored calc field, right?

Peter Walker

[email protected]

[mr_vodka]

What happens if you do a manual find for the equivalent.

Enter Find Mode []

Set Field [ Locked; 1 ]

Set Field [ _cDuplicate; 1 ]

Perform Find []