For example, I have a table that houses data on legal service providers around the country and another table to register calls from a hotline.
I want to include a report link on the Call Log Form that includes a list of legal service providers near the caller. I have a separate table called Zip Codes that includes all the country's zip codes and corresponding Lat and Long points. The report should take the zip code given by the caller and filter out all legal service providers that are not within a 15 mile radius.
Is this possible? I've been wracking my brains trying to figure this out. Please Help!
The rest of the answer lies in getting the Record which has the hot line calls to be "in focus" so that the report of legal services can calculate the distance.
One simple way to do that if there are few concurrent users is to set up a table called Focus Hot Line call with one record and a field to record the current call. That table would have exactly one record in it which would be record ID#1.
The on the Hot line record, press a button to populate that special record with the focus hot line call record ID. That record will then be made a child to the hot line call and lookup it's lat long. I assume that you have some way to have the Hot line call also have a lat long.
Then lookup that hot line call lat long down to every Legal services record based on a reference field to that table based on a numeric formula of 1.
Then use the pythagoras formula in the link above
var number OriginLat = [Zip Code - Zip Latitude];
var number OriginLong = [Zip Code - Zip Longitude];
var number DestLat = [Latitiude];
var number DestLong = [Longitude];
var number cosphim = 0.767911;
var number radius = 3963.1676;
radius (* (($OriginLat - $DestLat)^2 + ($cosphim * ($OriginLong - $DestLong))^2)^0.5)
Select on the Focus Hot Line record select a Hot Line Call. Edit the Relationship to pull in the lat and long to the Focus Hot Line call Record.
Now on the Legal Service Providers table (LSP) make a formula field callee [Link to Focus Hot Line Call] with a formula of 1, which just happens to be the record ID of our Focus Hot Line call record. Make a Relationship where 1 Focus Hot Line Record is related to Many (in fact all) LSPs based on the field [Link to focus hot line call].
Lookup the lat long from the Focus Hot Line call record down to (all) your LSPs.
Wake up Pythagorus from his 2,510 year post mortem sleep and put him to work!
Lastly, Put the Report Ljnk field from the relationship between Focus Hot Line call and LSPs on the Focus Hot Line Call Record and make a report sorted by distance.
my plane is late ... so you will in fact also get part B.
You can make a formula url field on the hot line call to edit the focus record and display it, so its a one click process.
The formula would be something like
urlroot() & "db/" & [_DBID .. of the focus hot line call table] & "?act=API_EditRecord&rid=1&_fid_xx=[Record ID#]
where xx is the field ID# of the [related hot line call].