How can I set a report link filter based on zip code or Latitude and Longitude radius?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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!

Photo of Lucas

Lucas

  • 0 Points

Posted 4 years ago

  • 0
  • 1
The heart of the answer is here, and includes a healthy debate as to if the world is flat or some foolish people believe its round, which would make your problem unsolvable using native QuickBase. Since all problems can be solved with QuickBase, the earth must be flat.



https://quickbase-community.intuit.com/questions/899420-is-there-a-formula-within-quickbase-that-wou...

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




Photo of Lucas

Lucas

  • 0 Points
I tried using the formulas available in the link you posted, but QB keeps giving me a syntax error and I've tried everything to fix it! Is there something wrong here?:

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)
Photo of Lucas

Lucas

  • 0 Points
Figured it out! I noticed my Lat and Long fields were text fields so I just had to add a ToNumber function.
Photo of Lucas

Lucas

  • 0 Points
So I'm still having trouble figuring out the components of the Focus Hotline Table. Can we call them Table A and B? I've run into trouble with the button to populate the focus record with the actual call log record id#. What field does the URL formula rid have to match with here seeing as there is only 1 record in the Focus table.
Photo of Lucas

Lucas

  • 0 Points
Hey Mark, I've gotten all of it to work except the last part! I can't seem to get how to have the Lat and Long pull into the Directory Table from the Focus Table. I know you mentioned a reference field based on a numeric value of one. Could you clarify exactly what you meant there?
I will explain this is 2 steps as I don't know if I will have time now to get though both steps before I need to catch my plane.  On the Focus table make a relationships where 1 Hot Line Call has many Focus Hot Line Calls.  Make just 1 Focus call record.  It will be record ID# 1.  Change the Role permissions so that no one including yourself may add a Focus Hot Line record.

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#]
& "&disprec=1"
where xx is the field ID# of the [related hot line call].