Forum Discussion

Qrew Member

5 years ago

Find Vendors within a set distance of a Customer Postal Code on the fly

In one of our apps, we receive calls from customer(s) who need some kind of service which could be anything from equipment to pick up service where we act as the broker. What we would like is the ab...

APIs and custom code

PhilipJones

Qrew Member

My formula is a derivative of the Haversine formula written using SQL functions. Our vendor locations are US covering areas from Puerto Rico to Alaska. Since we are looking for locations within 150 miles or 240 km, then error due to longitudinal changes moving closer or father from the poles or equator are minimized. This is the most accurate method for distances over 20km and accounts for the curvature more accurately than the pythagorean method. Nice article here.

I digress, my problem is not so much in how to calculate the distance as to how I would invoke using this formula within Quickbase. Assume Vendor Location and Location tables have these columns (plus others) where Lat and Long are in degrees or radians and use the appropriate formula for the conversion:

Record_ID City State Postal_CD Latitude Longitude

What I do not know is when I am looking at a Location Record and want to see a report out of Vendor Locations having all records within 150 miles, how would I generate this report. I will have many Locations in the Locations table that would need results related to the Location Zip.

------------------------------
Philip Jones
------------------------------

MarkShnier__You

Qrew Legend

5 years ago

I can only help you with native Quick Base and at this time there is no support for the Haversine functions. But I believe that you could come up with a lookup table to take the Longitude of the Location record and then adjust the "69" in the formula.

The way I would implement this would be to have a URL formula button to Edit or Create a record in a user focus table fo the current user where the Key field is the current user. It would set the geocode focus for the current user based on the Location they launched off of as their Focus Postal code.

Then that Focus can be looked up down to the Vendors table based on a relationship where the reference field is the field

[Current User]

with a formula user of

User() // ie the current user.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------