Cached calc searches

[Pbeyrouty]

This is actually a continuation of another thread I had (http://fmforums.com/forum/showtopic.php?tid/201932). I am creating a new post because I have a better idea of what the problem is now and it seems better to restate it. I was looking in the wrong place on the other thread.

I have a calculation field that determines the distance between the zip code of an entry in our Candidate database and another zip of the user's choosing. This zip is entered in a global field. The calculation works perfectly, but searching doesn't. The first search on the calc field works fine. After that search, if you change the user chosen zip the search brings back strange results. What I have discovered is that Filemaker is still searching on the calculation from the first zip code, even though the calculation field shows a distance determined by the new zip.

I'm guessing that the calculations from the original search is being cached is some way, then referenced on later searches. Does anyone know a way to solve this? I have tried doing other searches in between that don't reference the field but that does no good.

[comment]

Can you post a simplified file showing the issue?

[Pbeyrouty]

I forgot to mention. This only happen when the file is on the server. I don't get the same problem when accessing it locally. So I don't know if that means it is a problem with FM Server or just some complication of Multi user mode.

I don't have time to make a file right now but will try to get one up later today.

[comment]

I forgot to mention. This only happen when the file is on the server.

Why not try it the other way round then: see if the attached gives you the same trouble when hosted.

FindUnstored.fp7.zip

[Pbeyrouty]

I tried that file and it worked no problem. I then made a small version of my own file and tried that on the server only to have the problem persist. In my file the global is acting as the reference field with another table so the problem must be there. Here is a copy of the file.

The second table is a list of zip codes and their corresponding city, state, longitude, and latitude. Since I had to pay for it I figured I shouldn't put the whole thing up. The file has all zips starting with 9, so the west coast states. Most of our candidates are in Oregon so it is probably easiest searching on those zips. I have been using 97301 and 97232. They are about 50 miles apart but both have some candidates in the area.

Cand.zip

[Vaughan]

When you say the problem only happens when the file is on the server, do you mean it on a shared volume or hosted with FileMaker Server?

If its hosted, what version of FMS is being used? (Please specify the version and patch level.)

[comment]

I cannot test this, but there are two things I would try:

1. Change the distance calculation to:

Let (

trigger = gZip

;

)

2. Run a Refresh Window [Flush cached join results] step between modifying the global and performing the find.

Which versions are we talking about, and are they all (Server and Clients) updated with the latest patches?

[Pbeyrouty]

It is hosted on Filemaker Server 10, fully updated. I am using Filemaker 10 Advanced on OSx 10.5, but the same problem occurs with Filemaker Pro 10 normal on 10.4

[Pbeyrouty]

I cannot test this, but there are two things I would try:

1. Change the distance calculation to:

Let (

trigger = gZip

;

)

2. Run a Refresh Window [Flush cached join results] step between modifying the global and performing the f

Unfortunately, this will not help as the global is being used to related to another table. It is not being used in the calculation directly. Those field that are being used are converted to variables.

Case(

not IsEmpty(Zip);

   Let ( [

      x=69.1*(ZipCodes::LATITUDE-ZipCodesGlobal::LATITUDE);

      y=69.1*(ZipCodes::LONGITUDE-ZipCodesGlobal::LONGITUDE)*Cos ( ZipCodesGlobal::LONGITUDE/57.3 )

      ];

   Sqrt ( x*x+y*y ) );



)

[comment]

the global is ... not being used in the calculation directly.

Yes, I am aware of that. But that's exactly what I am suggesting - make the calculation aware that it needs to recalculate when the global is modified:

Let (

trigger = gZip

;

Case(

not IsEmpty(Zip);

   Let ( [

      x=69.1*(ZipCodes::LATITUDE-ZipCodesGlobal::LATITUDE);

      y=69.1*(ZipCodes::LONGITUDE-ZipCodesGlobal::LONGITUDE)*Cos ( ZipCodesGlobal::LONGITUDE/57.3 )

      ];

   Sqrt ( x*x+y*y ) );



) 

)

Admittedly, it's a long shot because fields from a relationship that depends DIRECTLY on the global are being referenced too - but I think it's worth a try.

[Pbeyrouty]

That didn't work either. Good idea though. I am tempted to try reinstalling Server 9 just to see if that is the problem, but that is too big of a hassle at the moment. If anyone else is running it and wouldn't mind trying the file I would appreciate it.

[Pbeyrouty]

In case anyone is curious I submitted this as a bug to Filemaker. They told me told me to use a work around rather than search the calculation field directly.

http://forum-en.filemaker.com/fm/board/message?board.id=aut&thread.id=1680

[comment]

This won't cure your bug, but perhaps you should consider an alternative approach:

Instead of calculating the distance for each and every record, calculate the (approximate) boundaries of the required max. distance from the current location (in the form of MinLat, MaxLat, MinLon, MaxLon). Then you can define a relationship to include all records that fall somewhere inside those boundaries.

Depending on the required accuracy, you could either settle for the results of the relationship, or fine-tune by performing the looping script on the related set only.