Denethor

Ender, thanks for all your help so far, but can you help me once again? Or anyone... The area code radius works but it always looks at the entire DB. How can it be made to only look in the records resulting from a find? or be used as a find criteria (i.e. within x miles of zip zzzzz) Now if I go into find and say show me all schools with a certain criteria it finds all those schools. Now I want say narrow that list down to only records that meet the above criteria and are within x miles of zipcode zzzzzz. thanks

Can you call scripts like that from inside FM or are you refferign to makign a web based front end to the DB? I was trying to do this all in FM and this is my first FM app so bear with me if questions seem dumb.

Is there an better way to do this for searching the whole US? After doing the import for just one state my FM DB is >400MB. The entire US would be HUGE!!! Like 20GB and the conversion would take weeks. I imagine the lookup on a file that large would not be quick either. Is there an extrenal service one could refernce somehow to get the Lat and Long for evey entry in your DB currently and then as new records are added in the future?

I found what was making my life a living hell... In my zip code table, Latitude and Longitude were defined as TEXT and not indexed. After fixing that it all started working for me. I was really getting frustrated because I had added a few feilds to you example and imported all my data into it and changed the lists to show what I wanted to see and all was working great. But when I would go back and do the eact same thing on my real tables it wouldn't do anything. Finally I went through every deatail of every feild until I found the only differnces. Thanks again for all your help. If I want to attempt your more exact method later by using the actual address, is there a thread on that already?

A contact is a school (show all schools in the area of the school your looking at currently). There should only ever be one person we ever care to contact at any given school that's why I didn't make a seperate teachers table and just left it as a name tied to the school. I did leave two adresses feilds because I figured there's the main address of the school and then that particular teacher could have another address or prefer to be contacted at home or something. Let me play with the table example you gave earlier dome more and see if I can't figure out how to adapt that to my setup. Thank you for all your help.

Take a look at my newest incarnation. The bounds box is generated fine, now there must be some way to use that data in that "similar" fashion. Take a look at the DB design and the calc feild called "Similar Area Key" Why does that return nothing? BPS_1.1.zip

This may sound dumb, but how do you make another occueance of a table and does it automatically stay updated when you update any other occurance?

Here's my Contact Manager mostly stolen from business productivity pack that is free on FM site. You might get script errors where I removed as much a possible to squeeze it down. Pull up school in anchorage (record #4 is one) Then down at the bottom click the "Related Contacts" tab, see how you can choose related City etc... If you click the city radio button, it will show all the schools in Anchorage. Idealy I would like to have another button called "Area" or something that would show all contacts with in range defined else where or have multiple buttons for 10,20,30 miles. Like I said I'm not going for acuracy just general area. BPS_1.0.zip

I'm going to try to take out most of the data to get it under the limit for this board and upload what I have

Thanks I really appreciate all the help. I'm still at a loss here, I'm missing the basic knowledge as to "why" this works and how to apply it to my data set. Why are there three zipcode and three school tables (base, All, & in Bounds)? What do they get populated with? And I want to show the School name not the zip in the results and it be clickable to bring up that schools info in the contact manager. I'd send you my tables so you could see what I'm doing, but they are almost 100MB.

I don't care that much about acuracy as it's more of a I want to see schools that are in like suburbs of major cities, the actual distance I could less how acurate it is. I was just trying to allow a defined distance from x. I had a table of zips,lats, and longs from a prior project that I wrote in ASP script for web page. it looks like this:(I've filled in variables with test for example) sqlstr = "SELECT Longitude,Latitude FROM LIVE_ZipCodes WHERE ZipCode='07020'" set ZRS = db.execute(sqlstr) [color:red]executes the above SQL command where the zipcode is filled in from a user supplied variable if not ZRS.eof then [color:red]Make sure the zip supplied is in the table miles = 50 [color:red]again supplied by the user in a variable usually lon = ZRS.fields("Longitude") [color:red]Read the Lon that matches the input zip center lat = ZRS.fields("Latitude") [color:red]Read the Lat that matches the input zip center [color:red]The next four lines compute a box around the zip center. lon1 = lon - (miles * 0.008092753623188405797101449275362) lon2 = lon + (miles * 0.008092753623188405797101449275362) lat1 = lat + (miles * 0.013830769230769230769230769230769) lat2 = lat - (miles * 0.013830769230769230769230769230769) rangestr = "Latitude <= " & lat1 & " AND Latitude >= " & lat2 & " AND Longitude >= " & lon1 & " AND Longitude <= " & lon2 findstr = findstr & "(City IN (SELECT City FROM LIVE_ZipCodes WHERE " & rangestr & "))" sqlstr = "SELECT * FROM " & dbprefix & "School WHERE Status = " & ACTIVE & " AND " & findstr else searchprompt = "Could not identify zip code " & request("Zip") sqlstr = "SELECT * FROM " & dbprefix & "School WHERE ID IS NULL" end if [color:red]So after computing the farthest lat and lon in four direction from the center, I run another query to show all records that fall in betweeen those ranges. works like a champ, I just can't figure out how to do this in FileMaker as I've used it for all of 1 day now.

Is your name from "Ender's Game" by Orson Scott Card? I met him, he lived not far from me and I've read the whole series of books. It was a long time ago though, must have been 10 years at least.

I want the user to go into the find screen and type in a zipcode and a radius in miles and have it return all records that match. Trying to find all schools with in x miles of y zip code. Could this all be done in a calc feild and used as similar. I was looking at the "productivity Solution" example that came with FM8. They have a tab called "related Contacts" on that tab you can click a radio button for Name, City, or Company. I'd like to add another option "AREA" and have it pop up little box that asks the distance to search and returns all contacts with in x miles of the curent zip. Here are the parts of the original post I don't quite follow... 1. [color:gray]Best way is to create a separate file with this list (3 fields, ZipCode, Latitude, Longitude). Did that and related ZIP to the Zip in my contacts table and I can show the Lat and Lon for each record. 2.[color:gray]Once that is done, create two calc fields in the main file, Longitude & Latitude, which return the numbers from the separate file based on the ZipCode entered. OK done that was pretty easy I guess as the relationship takes care of the ZIP match I simply put =Lat in the Lat feild and =Long in the Long feild. 3.[color:gray]Next, create a Global ZipCodeSearch field. OK, easy enough I guess, made a new field in my contacts table called GlobalZipCodeSearch and went in to options and made it a global (one instance). 4.[color:gray]Then a couple of global fields, returning the Lat. & Long. for the global ZipCode. Did same as above gLat and gLong, but can't figure how to make them display that lat/long for Global zip 5.[color:gray]Finally, a calc field which returns the square root of the sum of the squares of the differences between the global Lat/Long fields and the individual Lat/Long fields (ah, Pythagoras!), i.e.: Sqrt (((gLat - Lat) * (gLat - Lat)) + ((gLong - Long) * (gLong - Long))) OK, I made a field called AreaRadius, chose Calc as the type, and put that function in. It does the math on the Lat and Long but since the gLat and gLong aren't being populated then it doesn't mean much. 6. [color:gray]Now do the search for any record where this final calc field is below a certain number. How is this final step accomplished? Again sorry for being such a noob

OK, I'm a complete noob when comes to FileMaker on the Mac but I have lots of PC SQL experience. How in detail, please, do I do these steps. Where is the user defining the radius (ie 100 miles) No where in that math do I see that. I have my contact management screen created. I have all the data in the database. I have a table that has LAT LONG ZIP CITY STATE COUNTY (if any body wants it let me know) I just don't understand how all this works. I've done this same thing in ASP script with two SQL queries and similar math to above (except I cheated and used a bounding box instead of a true radius) and it works great, but for the life of me I can't figure out FM8. Maybe its because I'm not a native Mac user. But I need to get this working soon. Thanks for any help