ContributionsMost RecentMost LikesSolutionsRe: Find Vendors within a set distance of a Customer Postal Code on the flyMy 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 ------------------------------ 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 ability upon entering the customer location (Postal Code in the Location table record) getting back a list of the closest vendor locations (from the Vendor Location table) for service within 150 miles PLUS a list at the bottom for all National Vendors (which is a checkbox on the records in the Vendor table. We have four key tables that I need to use for the solution: 1) Locations (which would be the customer location where service is required) 2) Vendor (which is the Parent table for Vendor Locations) 3) Vendor Location (which is the Child table for Vendors) 4) Postal Code (which is related to Locations and Vendor Location on Postal Code to get the latitude and longitude values) The formula to calculate the distance is: (CEILING(2 * 3961 * asin(sqrt(power(sin(radians((VLoc.[Latitude] - Loc.[Latitude]) / 2)),2) + cos(radians(Loc.[Latitude])) * cos(radians(VLoc.[Latitude])) * Power(sin(radians((VLoc.[Longitude] - Loc.[Longitude]) / 2)),2)))*1.15)) I was hoping that I could create a report to do this for me, but I am thinking there is more to this. Even if I simplify the request to just calculating and pulling only the Vendor Location records in one report, then the National Accounts are just a basic report I can create on the Vendors table since they are not dependent on distance calculation. Any ideas how we might do this within Quickbase if possible? I am sure it could be done outside of Quickbase probably using Webhooks to bring back results. I would like to avoid this if possible as I already have a tool users can use that runs a SP on SQL to get back results in an Excel file. ------------------------------ Philip Jones ------------------------------