Forum Discussion

MikeMike1's avatar
Qrew Cadet
7 years ago


I need to build a Heat Map for our executive team displaying saturation of certain geographically defined areas.  Sadly, QB doesn't allow for a heat map organically, and most tools require the use of Lat/Long in order to render correctly.
Does anyone know of a formula that would convert the organic "Address" field into a Lat/Long?


16 Replies

  • MCFNeil's avatar
    Qrew Captain
    If you already have the Lat & Long, you can plug those into the address 1 and address 2 respectively, and qb will map it for you. 

    As far as heat maps, they haven't added that update yet, but it is coming.  For now you can add a summary chart for the counts in certain regions if that helps.
  • You are going to have to use script to do the geocoding of the address.

    This code:
    async function geocodeAddress(address, key) {
      let response = await fetch('${address}');
      let data = await response.json();
      return data;
    var address = "1600 Amphitheatre Parkway, Mountain+View, CA";
    var key = "";
      .then(data => console.log(JSON.stringify(data, null, "  ")));
    Returns JSON which includes the lat/lon values::


    (1) You may have to get a key from Google for your planned usage:.

    (2) If you use a key you have to add it the the URL being fetched
  • What would be the easiest way to run this on existing data in a table? A formula field?
  • The only way I know who to do this is to use a 3rd party add on like Juiced who can produce maps and record the geocodes into Quick Base fields so they do not have to be re-geocoded each time. 

    Maybe Dan has some other magic, but I thought that you now need to have a Subscription contract with Google to routinely do geocoding.  I thought is no longer a free service from Google although perhaps they allow small volumes for free.  But I think for the maps or the usage of the geocodes to be fast, they need to get calculated once and recorded in Quick Base as geocoding is relatively slow. My experience with Juiced is they are able to geocode at a rate of about 3 per second, which sounds fast, but if you have 1000 addresses on report, that would take 50 minutes! 

    But in our use case, we just code Customer Accounts that are not already geocoded, so there may just be a few each day.  So we run a dummy map of accounts not geocoded each day and Juiced writes the missing geocodes into a pair of lat long fields of the customer Account record.
  • I simply need to convert the address field into lat and lng every time one is added.
  • I have a subscription to google cloud for many various projects I work on outside of quickbase. So the api key is no issue. I can export the data and re-import it with updated lng/lat information for all existing locations. But if I wanted to update a location as it comes in what would be the best route here without paying a fortune to have an addon installed to quickbase. 
    • _anomDiebolt_'s avatar
      Qrew Elite
      My script does exactly what you want. There is a little additional code that is needed to grab the address from the open form and after geo-coding stuff the lat and lon into fields.

      Also many Google services are free and/or don't require a key when used below a rather generous threshold.
    • MatthewMatthew3's avatar
      Qrew Cadet
      Thanks Dan, any guidance on how to get this rolling inside of quickbase? I am unfamiliar with some of the javascript practices and workarounds outside of making buttons inside of qb app.
    • MatthewMatthew3's avatar
      Qrew Cadet
      I suppose I could try the image onload technique or run every added location through zapier parsing the address with the js code to update the record.
  • Here is a full demo that uses the essential snippet of code I posted previously:

    Client Side Snapshot Fields ~ Add New Location

    Pastie Database

    I would like to explain the name I choose for the application.The application takes a address entered as a single text field and uses Google's Geocoding service to look up the latitude and longitude. You can think of the geocoding process as a function or formula that takes the address value and calculates the latidude and longitude. However, this function is calculated client side (in the browser) once and only once at the time of interacting with the form. If by some other mechanism the address value was changed (say through grid edit) the geocoding process is not reevaluated. This may or may not be an issue depending on your expectations and workflow.


    1) The example demonstrates two different ways of invoking the geocoding - automatic and manual. However, if you examine the code you will see there is only one difference in the code, namely what event is being responded to (change versus event):
      $("#_fid_6").on("change", function() {...});
      $("a.QBU_Button").on("click", function(event) {...});
    The form may appear to operate differently in the two cases but from a coding perspective the code is just responding to two different events (change and click). There are LOTS of event script can respond to so this is a very accommodating way of responding to user behavior

    Wiki on HTML Events

    2) To preserve the integrity of the geocoding against accidental change of the latitude and longitude, the latitude and longitude fields were made readonly at the HTML form level:
      $("#_fid_7").attr("readonly", true);
      $("#_fid_8").attr("readonly", true);
      $("#_fid_10").attr("readonly", true);
      $("#_fid_11").attr("readonly", true);
    3) The general technique used in this example can be extended to an enormous number of problems and follows a simple three step process:

        a) Grab some field values out of the form in response to some event
        b) Calculate some quantities using script
        c) Paste the calculated quantities into some other fields on the form

    Step b) above may involve a complex calculation or algorithm that can be implemented in the formula language or it may involve calls to web services such as Google's geocoding.

    4) The dbid and apptoken parameters are in the code as boiler plate although they are not used. The key parameter for Google's Geocoding service is left blank as it is not needed for low volume usage. For high volume usage you will have to supply your own key.

    5) When I initially answered this question I was intentionally used a new scripting feature called async / await. These advanced techniques will make you script solutions easier and very short. However, I am limited in the time I have to write up elaborate explanations to every question. Over time you should become comfortable taking the "essential code" of a solution and supplying the necessary boilerplate to form a complete solution.
  • Thank you for the detailed explanation, for the time being I've configured a zapier task that will geocode using their built in code action to filter any added rows to the database, calculate their coordinates and send it along with the rest of the information since these get added via external forms anyways. Alongside using their coordinates to calculate the time zone with another external API. 

    However, this will be extremely useful in updating existing locations within the database, if the address is incorrect or needs to be modified in any way.
    • _anomDiebolt_'s avatar
      Qrew Elite
      Keep in mind that Google's Geocoding service can geocode multiple addresses with one API call.