Unstored calc field search unreliable if file hosted by server
11.0v5 (bug people noticed it in earlier version also), maybe 12 didn't test
Operating system version
Mac OS X 10.8.3
Description of the issue
This file is to illustrate a bug on unstored calc search.
On client there's no problem the "Normal script" will work ok. If the file is hosted on FMS 11v5 search result will be unreliable : that's to say that regardles of the changes of the Global key field, the founcount will be the same. But upon restarting that erroneously "constant" founcount may vary (it'd be either the correct foundcout of one of the prevoius search).
So locally there's no problem, only if hosted on server which leads us to the Knowledge base article
"How Functions Evaluate Differently on the Host Versus the Client in FileMaker Pro"
Answer ID: 6996
Conditions that prevent those finds from being performed on the host include:
… An unstored calculation that uses a related field that is from a table in a different file.
This is a bad limitation in my opinion, because it deefats the point of that optimization for many solutions BUT from it we can get our workaround.
The workaround is this : include in your unstored calc a filed from another file to force Filemaker to do the search on client by preventing it to do it on host
So there's no problem in the calc, only on the search done server side
Please note the terrible speed degradation of the workaround (from 9 sec to 25 on my setup), because of course teh search is done on client.
File is unprotected if Login / pass asked : Admin / leave blank
Steps to reproduce the problem
File is here
How to use the file :
0. Host the files on Filemaker server 11 (11v5 also)
1. Change the Global ky field with the pop-up menu
2. Press "Normal Script" look at found count
3. Press "Woraround Script" look at found count. It may be different.
Correct found count are for Global key value
3 - > 1931
4 -> 19
5- > 18
Wrong foundset if using normal script
What the script does is to do a search where the calc field is ≠ 1
So you'll see using the normal script on server when you get not correct found count, that amongts the foundset, instead of just 0 you'll find records with 1.
in unstored calcs you may search, make sure you call a related field that's in another file to force FMS not to perform the search on the server but on the client.
But this is slower, unpractical to change all the calcs in the solution, and this bug can cause terrible consequences