Thank you for your post.
Remember... global fields are local for each session. Therefore, any entries you make into a global field will be different than someone else who is using the same file. This could cause a lot of the slowness. Instead, you may want to issue a Replace into a text field. This would force the calculation across all records, and the search would be quicker.
In any case, I am happy to see your file. I have sent you a private message (top of this screen - right side - X Messages) with instructions where to send the file.
Thank you for sending in your database file. I was able to understand what you are trying to accomplish.
Any calculation field that involves a global field will be unstored. On a local machine, this isn't a problem, but when put on a server, where each user has a local global variable, the results are unexpected. For your case, since you are using FileMaker Pro 10, you can take advantage of script triggers. This is what I did:
1. In the Candidates table, I created "g_distance" - a global Number field. This field will be used to enter the distance you want to search for. In the example you sent me, you were searching for "<15". In this field, just enter 15.
2. Create the following script, "Distance Trigger":
# check to make sure the value isn't negative
If [ IsEmpty (Candidates:: g_distance) or Candidates:: g_distance < 0 ]
Exit Script 
# make sure all records are available and point to the first record
Show All Records
Go to Record/Request/Page [ First ]
# initialize a flag/variable to keep track of the last record
Set Variable [ $exit ; "0" ]
# enter loop
# check to see if record is outside the distance. If so, omit the record
If [ Candidates:: g_distance < Candidates:: c_Distance ]
# check to see if the flag/variable was set for the last record. If so, exit loop
Exit Loop If [ $exit = "1" ]
# check to see if the current record is the last record. If so, set the flag/variable
If [ Get ( FoundCount ) = Get ( RecordNumber ) ]
Set Variable [ $exit ; Value: "1" ]
# else, record is within the requested distance, so go to the next record
Go to Record/Request/Page [ Next; Exit after last ]
# check to see if zero records are within the required distance
If [ Get ( FoundCount ) = 0 ]
Show Custom Dialog [ "No Records Found" ]
Show All Records
# the pointer is at the last record, so go to the first record
Go to Record/Request/Page [First]
3. In Layout mode, click once on the g_distance field, pull down the Format menu and select "Set Script Triggers..."
4. Put a check mark next to "OnObjectSave", and select the script "Distance Trigger". Click OK.
Go to Browse Mode and enter a Zip along with a distance.
Let me know if you need clarification for any of the above steps.
Thanks for the recommendation. I will use this work around. Do you know if this issue is something that Filemaker is working on fixing or should I do a similar work around for all similar calculation fields in the future?
I have no information about the status of this issue. In the mean time, if you are searching on any field that is a calculation based on global fields, then use a similar script as above.
I was wondering if the problem I have is related to the one in this thread or I should start a new thread. This is my problem:I have two related tables: Items(ID,...) and Transactions(ItemID, Date, Quantity). I want to compute the total quantity of transactions of an item before a specific date. So I add a global in Items (gEndDate) and add another relationship Transactions_OnDate where ID=ItemID AND gEndDate >=DateI then add a calculated field in Items Qty_OnDate = Sum(Transactions_OnDate::Quantity)Everything works perfectly. I see correct records in both portals. I am also able to search in the calculated field Qty_OnDate.
BUT when I move the database to server, portals and records display correct amounts but when I search for Qty_OnDate >0 after changing global gEndDate I get the wrong results: I get results of previous search, as if the Qty_OnDate has not updated even though I see correct results. If I make any changes to a record, then Qty_OnDate is updated properly and that record appears correctly after find.
Is this manifestation of the same problem? Can you suggest a workaround for this case?