Did you try to make this a number field that is calculated, instead of a directly calculated field?
(UNcheck: Do not replace existing value...in the field definition)
Thanks psijmons. To clarify, the field is an unstored calc so that it can work with a live tally of the related records.
ngi_ToOrder = ngi_Qty - ngi_OnOrder
ngi_Qty is a number field
ngi_OnOrder = count ( stockItems::_k_StockItemID )
Are all fields involved set to be indexed?
The fact that the issue creeps back in after a restart might be worrying.
Did you try to run File Recovery on a copy of your file? Does this pass or do you get a message that there are problems?
Thanks for your reply.
I did not do a recovery as the file does not report any errors on verification. I did a "Save a Copy" instead to re-create the tables and indices.
The fields I am searching on an unstrored calcs so they cannot be indexed. The stockItems::_k_StockItemID field which I am counting is indexed automatically as part of a relationship.
Recommend you check:
- that the relationships keys are all indexed on both ends
- that the calculations for the fields are written in the context of the base-table for the layout on which you are performing this find
- that the calcualtion themselves reference only fields from the Table Occurances (TO) used in the relationship per the TO graph
If these are all verified and it still is unreliable (sometimes right, sometimes wrong), then something may be wrong with the file's Indexes.
We're having a very similar problem. Occasionally scripted finds on unstorred calcs will return the wrong results. Actually the results it returns are correct, but incomplete (additional records should have been included the resulting found set).
Restarting the server appears to solve the problem temporarily. We usually get a week or so of good running before the issue comes back and a restart of the server is required. To work around this we have scheduled a weekly automatic reboot of the server.
However, yesterday (Monday) the problem showed up but it had only been about 26 hours since the most recent reboot. This morning another user complained about results being incorrect, so we are going to reboot it again tonight in hopes of getting things back to normal.
I'm concerned because we hadn't had this problem at all since scheduling the weekly reboot of the server - until yesterday.
(BTW - Unfortunately we cannot index/store the calc results that we are searching on because the calc references related tables, which means it cannot be storred/indexed).
Anyone else having this kind of problem or any suggestions would be most appreciated!
We are running FileMaker Server Advanced 11.
If Michael's still reading, please let us know what happened in the previous instance of this problem.
In addition to the things I recommended to Michael last year, check that the calcs are set to return the correct data type.
Also check the key fields between the related tables on which the calcs depend are matching data types.
Another issue may be indexing of some of the underlying fields used in the calcs. Reindexing may help.
The fact that a server restart seems to clear this is puzzling to me. Are the clients staying connected for long periods without the server disconnecting them? If so, it is conceivable that the caches on a client machine could be stale, or the cache setting on the server needs to be smaller, more frequent.
Thanks for the suggestions! We've checked the items you've mentioned and found no problems (prior to my posting here). One additional data point - if we take the solution off the server and run it locally things work just fine - this problem only arrises when hosted on Server. The fact that it works most of the time and occasionally stops working makes me think we have a server issue and the solution is likely not located within our database files themselves.
Another interesting thing - if we know that certain records should be returned by the scripted find but are not, I can find one of those records another way and observe the field I'm searching on does indeed contain the correct data. However, if I do a search and enter the value for that calc field as well as the serial number of one of the records known to exhibit the problem, FileMaker returns No Results Found (in an effort to isolate one particular record).
I read on another forum that uninstalling FileMaker Server and re-installing it has resolved this issue for some folks. I'm going to give that a try on Saturday, May 5 unless we find a better resolution to this issue before then.
Additionally, I've found various posts across the net regarding this issue (oddly most are not part of FM Dev Community). The conditions seems the same - searching on unstorred calc sometimes returns bad results, rebooting server restores capability for a limited period of time; searches work just fine and then they stop working even though no developer activity was going on in the system and the data set remained largely unchanged.
And let us know how your efforts result. It's always important to hear back when there is a solution found.