Unstored calc fields based on global are being cached - bad!
Database hosted on FMS9, client is FMA9. Table 1 has a global field that is used to calculate values for 3 unstored calculation fields, by pulling values from a related table (Table 2). Together, those 4 fields are used in a relationship to Table 3. Further unstored calculated fields in Table 1 perform Average, Min, Max, StDev functions based on the related records in Table 3.
Everything works fine the 1st time - with 960 records in Table 1, and 20,160 in Table 3, finds are quick, and the "Processing Query" only pops up briefly. However, if I change the global field, I can see the calculated fields updated, but the finds are being performed on the cached data from the previous calculated values. Also, the 'Process Query' box doesn't appear.
It seems the only way to fix this is to go to 'Manage Database', double-click one of the fields in the table list, click 'OK', then 'OK' to save the changes. Somehow this must flush the cached data.
My questions are: where is the data being cached, since none of the fields in Table 1 are index or stored? Why doesn't the 'Refresh Window' or 'Flush Cache to Disk' commands work? Is this a bug, or is there a fix, even a workaround that I can have normal users perform?
Definitely a show-stopping problem.
Same problem with FMA10. Also, using 'Manage Database', to force an update/flush on one client fixes the problem on other clients.
One possible workaround: create a number/text field, no indexing, write a script to show all records and then replace the field contents of that 'junk' field with nothing.