Getting Started

 View Only
Expand all | Collapse all

Find the nearest Starbucks using QuickBase

  • 1.  Find the nearest Starbucks using QuickBase

    Posted 02-24-2020 18:32
    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:
    This shows the trigger part of the automation.

    Action:
    This is the second half of the automation that updates the Common Parent table.

    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.

    Is only checked if the distance is equal to or less than the Distance Mileage.

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

    Report Link configuration

    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
    ------------------------------


  • 2.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 16:27

    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
    ------------------------------



  • 3.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:02

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



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 4.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:11

    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
    ------------------------------



  • 5.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:20

    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
    ------------------------------



  • 6.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:33

    Getting the current location will require some coding outside of native Quickbase. Your process would likely need to use a code page where the user clicks a button and navigates them to said codepage where you have some javascript that logs the date/time and their information. In that code you would have to use your browser or devices geolocation API or service to get the current position and then log it. 

    In this situation you can't use Pipelines because its independent of the user at that point, and unfortunately Quickbase doesn't have any native services specific to geolocation. 



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 7.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:50

    I'm familiar with the method you outline. Kirk had his URL formula call a code page, which isn't working.

    I use a code page that I found on the Magic Buttons app to capture the current time.

    I also found code page that I can call from a button to open a popup window and then get the current lat/long. (attached), but I don't know how to get what I need from it. It works, but not on a mobile.

    Opens a pop up with a button that will capture the lat/long for current location.



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



  • 8.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 17:58

    At this point it would really just be trial and error with that code you linked above. That code is valid for modern browsers and should remain working on mobile, however you'll likely need to play around and attempt to tweak the response and display the full error string on screen so you can actually see what's going on.

    Its possible that the configuration of the button as a pop up isn't working since its on a mobile device, or there might be something about your device settings that is blocking your device from responding to the geolocation API. Someone else on this forum might chime in later with existing code that you could copy, but best course now is just to play with that existing code and figure out what, if any errors are responding once you cross over to mobile.



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 9.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 18:13
    Edited by Barry Dolan 08-16-2023 18:14
      |   view attached

    The frustrating part is, I know it works, I just don't know how to pass the results back to form.

    I can run the little popup and get the lat/long, so, in theory, I should be able to run some of that code to get the lat/long and pass it back to my fields.

    Opens the popup and then get the coordinates when clicking the button
    Attached is the code that captures the coordinates to display on the popup. 
    But then what???



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



  • 10.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 18:28

    Ah, well that is the easy part then. Once you have the results logged you would use the QB API to log it back to your record. Since your example says they're clocking in, then you'd be adding a record so you don't need anything like a Record ID parameter or anything. You can use either the HTTP API or the REST API, I've given an example of using the REST API below. 

    Using the REST api you need to get a temp token for the user logged in using a function like: 

            const tempToken = (dbid) => {
              return new Promise((resolve,reject) => {
                var headers = {
                  'QB-Realm-Hostname': 'your realm',
                  'QB-App-Token': 'your app token',
                  'Content-Type': 'application/json'
                }
                fetch(`https://api.quickbase.com/v1/auth/temporary/${dbid}`,
                  {
                    method: 'GET',
                    headers: headers,
                    credentials: 'include'
                })
                .then(res => {
                  if (res.ok) {
                    return res.json().then(res => resolve(res.temporaryAuthorization));
                  }
                  return res.json().then(resBody => reject({status: res.status, ...resBody}));
                }).catch(err => reject(err))
              })
            }
    You also need an upsert function to load it to QB - something like:
    const upsert = (commit,token) => {
              return new Promise((resolve,reject) => {
                var headers = {
                  'QB-Realm-Hostname': 'your realm',
                  'Authorization': `QB-TEMP-TOKEN ${token}`,
                  'Content-Type': 'application/json'
                }
                var body = {
                  to: dbid to load to,
                  data: commit,
                }
                fetch('https://api.quickbase.com/v1/records',
                  {
                    method: 'POST',
                    headers: headers,
                    body: JSON.stringify(body)
                  })
                .then(res => {
                  if (res.ok) {
                    return res.json().then(res => resolve(res));
                  }
                  return res.json().then(resBody => reject({status: res.status, ...resBody}));
                }).catch(err => reject(err))
              })
    }

    Put it all together in your code like: 

    function showPosition(position) {

         tempToken(your dbid).then((token) => {

              var commit = [

                        {

                             "fid of your latitude field": { value: position.coords.latitude },

                             "fid of your longitude field": { value: position.coords.longitude },

                             "fid of your date/time field": { value: code to get the date/time you want}

                        }

              ]

              upsert(commit, token).then((res) => {

                   //handle the qb response here and redirect if successful      

              }).catch((err) => { // handle errors here })

         })

    }

    Disclaimer is that you'll need to modify this code to fit your actual need and inject your own code to handle errors or any issues with syntax or formatting. It's hard to code directly into this interface so there might be syntax errors that would be easier to spot in a text editor. 



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 11.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 19:09

    I am definitely going to keep the snipet. Thank you. But I lost you right after you said "easy part", lol!

    For our purposes the user does not create a new record each time they clock in/out. This function updates an existing record, like a time card. 

    var text urlToExecute = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=ca5f4i4b5f6y8vy7zfzcfvximk
    & "&rid=" & [Record ID#] & "&_fid_64="&ToTimeOfDay(Now());

    I wrote a scheduled pipeline that creates a set of records each day. The user then has a new record to use as their time card for each workday.

    I am going to place 2 new fields beside the clock in field to record the lat/long when the button is tapped. And follow the same with the clock out button tap. 



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



  • 12.  RE: Find the nearest Starbucks using QuickBase

    Posted 08-16-2023 19:15

    If you have an existing record that they're logging to you'll need to pass that rid into your url string for the button. So if you go this route the button would direct the user to a code page instead - something like: 

    https://your_realm.quickbase.com/db/your_dbid?a=dbpage&pageid=your_db_page_id

    You would then change that to be like: 

    https://your_realm.quickbase.com/db/your_dbid?a=dbpage&pageid=your_db_page_id&rid=[Field to designate the current time card for them]

    in your code you then parse out the rid value - rough and dirty code is something like: 

    var l = window.location;

    var rid = l.search.split("&")[2].split("=")[1];

    You would then include the rid as part of your commit:

                        {

                             "3": {value: rid},

                             "fid of your latitude field": { value: position.coords.latitude },

                             "fid of your longitude field": { value: position.coords.longitude },

                             "fid of your date/time field": { value: code to get the date/time you want}

                        }



    ------------------------------
    Chayce Duncan
    ------------------------------