Distance search returns different response based on acct privileges

[Pbeyrouty]

I have encountered a very strange bug and have no idea what is causing it. I just setup a distance search option for my company's candidate database. This way our recruiters can search for candidates within a certain number of miles of a zip code. To accomplish this I have one table that has all of the zip codes and their corresponding longitude and latitude for the country. In the candidates table I added a global field for the zip code being searched on. There is also a calculation field that figures out the distance between the candidate's zip and the search zip (if they have a zip code entered). The search is then done with a simple c_distance =< #miles_for _search(ie 15, 25, etc).

The calculation field works just fine. When I do the search from my acct(full access acct) it works. When I do it from any other account it doesn't. I have checked the permissions and all of the other privilege sets have access to the ZipCodes table and the candidates table. I even set the script that normally does the search to run with full privileges. The results never match up. For instance, instead of the 105 results I should get(and do on my acct) for people within 15 miles of a zip I get 2534 people. All of these people seem to be within 20 and 55 miles of the zip. I tried apple+R to see what the search criteria looks like and they are exactly the same on both accounts.

Has anyone run into something like this before?

[bcooney]

Can we see the calc?

I imagine there's a relationship btw Candidates and Zip by gZip that brings back the lat and long to your calc in Candidates.

So, you see the c_distance result next to each Candidate rec in a list view? Is the result of the calc a number?

[Pbeyrouty]

Yes on all counts. There is a relationship between ZipCodes and Candidates for the candidate's zip, and one for the search zip. The calculation result is a number and displays fine in list view no matter which privilege set I'm using.

[Pbeyrouty]

Sorry, missed the part where you asked to see the calc. The weird thing is that the calc works fine. This is the calculation defining c_distance. I found it on the internet somewhere or other.

Case(

c_CandZip<100000 and c_CandZip>0 and not IsEmpty(Zip);

   Let ( [

      x=69.1*(Candidates_ZipCand::LATITUDE-Candidates_ZipSearch::LATITUDE);

      y=69.1*(Candidates_ZipCand::LONGITUDE-Candidates_ZipSearch::LONGITUDE)*Cos ( Candidates_ZipSearch::LATITUDE/57.3 )];

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



)

[bcooney]

So, something's up. Describe the search. Post the script? Manually just find in the c_distance field in the Candidate list view.

[Pbeyrouty]

Without using the script, thus cutting out all other search terms, I enter <=(less than or equal to) and 15 in the radius. With the same zip the two accounts still get two different results. The full access account give me a list of people where the c_distance is less than 15, all others give me some other range.

One thing I just noticed, when I did this originally I had some stuff in another field. The other accounts gave me the results I mention where the distance was between 20 and 50 or so instead of 15. When I did it this time everything that came back says a distance of 6767.92... In reality these are all records with zip codes that are messed up. Either with spaces and punctuation or Canadian zips, which have letters.

[bcooney]

"<=(less than or equal to) and 15 in the radius"

I'm not following. c_distance is the calc field. Why aren't you finding in this field for <=15? Obviously you also need to populate the global so that the calc resolves.

Also, please confirm that

Candidates_ZipSearch

is using the global zip to relate to the Zip table, correct?

Edited March 7, 2009 by Guest

[Pbeyrouty]

Sorry I misspoke, typed, whatever. I did put that in the calculation field. I set up a search page with a radius field that is used by the script, but the script just drops it in the calc field. I did this manually on the last attempts the way you suggested

[Pbeyrouty]

Sorry, it's busy here at work and I once again didn't read your whole post the first time. Yes, Candidates_ZipSearch is using a global field that is located in the Candidates table

[bcooney]

I'm stumped. You can see the calc in list view using any account and given the same gZip, it calcs the same for any account, but finds (manually, without a script) result in different found counts. I don't know what else to suggest. Post a clone...

[Pbeyrouty]

Yeah, this is really frustrating and completely baffling. Thanks anyways!

[Pbeyrouty]

I am beginning to think the problem must be with the global field. I just noticed that when I try doing the same searches, only on a copy that is local to my computer and not on the server, everything works fine for all accounts. I'm guessing there is something screwy going on with the global field that is determined by operating in a multi-user session. I'm not sure how to deal with this but if it gives anyone else any ideas I would love to hear them.