KirkTrachy1
Quickbase Staff
5 years ago

# Find the nearest Starbucks using QuickBase

Last week Howard popped into an "Office Hours" webinar asking about how to find the closest customers to a given Latitude and Longitude location.  Well you can do that with QuickBase.

I went looking for a CSV file with a bunch of Latitude and Longitude locations and I found a file with all the Starbucks locations in the US.

The essence of the application is to find a Starbucks, enter the distance you want to search and return a report and map of all those locations within that distance.  This can be calculated if you have both the Home and the Destination's Latitude and Longitude.

So we now have a Locations table butktrachy@quickbase.com we need to add a parent table we can use to pass down all the home's Latitude, Longitude and Distance as lookup fields.

We add the "Common Parent" table with only one record and made that related to all Location records. (we changed the reference field, "Related Common Parent" to a formula numeric field and put the number 1 in as a formula.  Since there was only one Common Parent record, it's Record ID# was 1.

So we created an automation that fires whenever a Location record's "Within Miles" field is updated.  The automation will edit the parent record and update its Latitude, Longitude and Within Miles fields.  This constant one to ALL relationship between the tables allowed us to always be sharing the last requested Latitude, Longitude and Mileage.

Trigger:

Action:

Now that all the data is on all the location records, we added a formula numeric field to calculate the distance between the two.

```var number HomeLat = ToNumber([Home Latitude]);
var number HomeLong = ToNumber([Home Longitude]);
var number Lat = ToNumber([Destination Latitude]);
var number Long = ToNumber([Destination Longitude]);
var number Distance = Round(((69*(\$HomeLat - \$Lat))^2 + (53*(\$HomeLong - \$Long))^2)^0.5);

If(\$HomeLat = 0 or \$Lat= 0 , 0 , \$Distance)
```

Next we needed a way to flag only those locations that were within the "Within Miles" range.  We created a formula checkbox field that would be checked if the Location record was within the range.

So now we have the matching criteria to create the report link fields to display the results.

The result:

We cover topics like this in our daily, "Office Hours" webinars.  Held M-F at 1:00 PM Eastern time.  Feel free to drop in anytime.

------------------------------
Kirk Trachy , Senior Solutions Consultant
QuickBase | 603-674-5454 M | ktrachy@quickbase.com
------------------------------
• Hi Kirk,

I'm a user of your Magic Buttons app and have gained a great deal of insight from a lot of its functionality. However, it seems as though the coding for getting latitude and longitude does not work. I'm curious if you have an updated working version you could share. Or could you point me to where I could find a working model.

Thanks

------------------------------
Barry Dolan
------------------------------
• Kirk Trachy us retired from being a QuickBase employee and is now living in Florida.

------------------------------
mark.shnier@gmail.com
------------------------------
• Hi Barry,

Are you wanting to know how to get the Lat and Long for a given address?    You can't do it directly in quickbase,  you'll need to use an outside service like google or geocodi.io and a pipeline to geocode,  but it's pretty simple to set up.

------------------------------
Jeff Peterson
------------------------------
• I am wanting to get the lat/long for the current location.

When a user taps a button on the qb mobile app it will capture the current time and update a form field. I have this much set up and working. But what I also need is to capture the location of that user when the button is tapped.

This is functionality we're programming to replace our time clock so employees can clock in/out on the mobile app. But we need to be able to determine where they clocked in from.

I saw an older post with a similar question, regarding government mandates for that person's needs, but there wasn't any response to the post.

I'm getting more familar with API's in pipelines, so I'd be interested in learning more of what you suggest.

Thank you,

------------------------------
Barry Dolan
------------------------------