Forum Discussion

RyanMatteson's avatar
RyanMatteson
Qrew Trainee
6 years ago

Google Maps Route Planning

I don't know if this one is going to work for you or not.

 https://ryanmatteson.quickbase.com/db/bn89sa4ur

There is a Parent table "Campaigns" and a child table "Contacts". Within each campaign are lists of related contacts.

Under the list of related Contacts, I would like to have a map showing the best route to take to deliver to each contact, I would also like to be able to print out the list in the correct order with the map (remember mapquest).

Each Campaign has a list of related contacts that are part of the campaign, I would like to have this list of related contacts be plotted on a map and a driving route be automatically created so my agents just need to click (If need be) "Create Route" and BOOM they can print (or at least view the best route to take to deliver.

9 Replies

  • If your agents just need to click a button to be able to generate a route, you can make a formula URL button that opens Google Maps with your starting and ending address and it will suggest the best route at the time and they could drill in / print the route

    For example:
    https://www.google.com/maps/dir/LoDo,+Denver,+CO+80202/Boulder,+Colorado/

    The https://www.google.com/maps/dir/ would be the root of your button, then you would pass in the starting and ending address fields like https://www.google.com/maps/dir/address 1/address 2
  • In your formula url field -> 
    "https://www.google.com/maps/dir/" & [Starting Address] & "/" & [Ending Address]

    If your starting address is static and will never change (like if you were always sending them from your office) you could swap out the field [Starting Address] and just type in your start like so:

    "https://www.google.com/maps/dir/1234 My Office Address, Denver, CO/" & [Ending Address]

    If your starting address draws from another table like the campaign, or if you have an address unique to the Agent assigned like their home address they'd be traveling from, then pass it in as a lookup field and you'll be good to go like in the first example
  • I like the idea of pulling the address from another table like the "Primary address" of the agent.

    The list of addresses will always be ALL of the related contract addresses. 

    The code appears to require I set [address 1], [address 2], [address 3], [address 4], .....,[address x].

    If the qty of addresses vary how do I include all related addresses?

  • From a google maps standpoint - its pretty simple. Its already built to let you send it multiple destinations in a chain - so you would do something like
    https://www.google.com/maps/dir/Denver,+CO/Boulder,+CO/Fort+Collins,+CO/Colorado+Springs,+CO
    Where you're going from Denver -> Boulder -> Fort Collins -> Colorado Springs. You just sort your addresses in the order you want to travel and do like this:
    "https://www.google.com/maps/dir/" & [Address 1] & "/" & [Address 2] & "/" [Address 3] .... (rinse and repeat)

    Now the catch is I don't know if there is a limit to how many destinations google can accept, so I can only truly speak for Quick Base and what you can try and send it.

    I'm going to assume that your comment about ALL of the related contact addresses means that if the campaign has 20 contacts you want to chain all 20 child contacts together and run the route. 

    Option A, depending on your coding ability, would be to do a API_DoQuery when the button is clicked, use Javascript to pull the address field from each record and use those address strings to build a custom URL. If you want to go that route I can provide more info. 

    Option B - which wasn't possible as of last week until the Dec/2018 release, is to use the new Summary List Text feature that combines text fields from your child records into your master record. 
    This starts to get a little hairy so bear with me, but I went and played around to be 98% confident this will work. So shoutout to your timing and a great new feature

    Step 1 - Set up a formula-text field in your Contacts Table and use ToText() to convert your address to plain text => ToText([Contact Address])

    Step 2 - In your relationship between your campaign and contacts, summarize your new text field from (1) to create a new combined text field that will display all the addresses you want

    Step 3 - Because of the way Quick Base generates this field - you'll need to split your new field back into its components using a Part() function.
    So like this => Part(ToText([Your new combined address field]),1,";")
    Will pull the first address in the list.

    Step 4 - Since we're summarizing this into your campaign, you'll want your formula-url button to map the route to be in the 'Campaigns' table where you have your combined text summary field as well. In that button - you'll still follow the google structure of 
    "https://www.google.com/maps/dir/", but now we're concatenating each chunk / part from the list. So it'll look more like this
    "https://www.google.com/maps/dir/" & 
    Part(ToText([Your new combined address field]),1,";") & "/" &
    Part(ToText([Your new combined address field]),2,";") & "/" &
    Part(ToText([Your new combined address field]),3,";") & "/" &
    Part(ToText([Your new combined address field]),4,";") & "/" &
    Part(ToText([Your new combined address field]),5,";") & "/" &
    Part(ToText([Your new combined address field]),6,";") & "/" &
    Part(ToText([Your new combined address field]),7,";") & "/" & 
    ... on and on

    There is the catch that this isn't 'fully' dynamic, if you want that you'll need to revert to Option A and have to query and do a forEach / for loop to account for any number from 1-100 addresses

    But you could theoretically copy and paste that part() formula above and just copy it 100 times and just change the numbers to match.

    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      Just FYI
      The summary text field will do up to a maximum of 25 unique text values in my testing.
    • RyanMatteson's avatar
      RyanMatteson
      Qrew Trainee
      Seems like option A is the more efficient route between the two, there is another article that shows an option with an example but has very little explanation of how to do it.

      Here is a link to the thread
      https://community.quickbase.com/quickbase/topics/is-it-possible-to-combine-a-calendar-report-and-a-g...

      Here is a link to the example
      https://haversineconsulting.quickbase.com/db/bmihnujwd?a=dr&rid=7&_ga=2.1547511.779468550.15...

      I like this example the only problem with it is it plots points with letter designation and not the name of who the delivery is for.

      I was hoping to be able to recreate this and add a checkbox on the list of participants to select which contacts to add to the formula and have a button to press that (like we were talking about) takes you to googles directions page.
    • ChayceDuncan2's avatar
      ChayceDuncan2
      Qrew Cadet
      The actual code showing how to do that would be here if you're going that route
      https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=550&_ga=2.43640931.754537564...

      In this case - you'll want to revert back to A and the DoQuery to swap out that string here " query: "{9.EX." + kRid + "}", with the specific query to locate the clients that you have 'checked' that you want to build a route for, as well as updating the clist to grab the particular field id of your address. 

      As for changing labels - I can't find an exact example of using DirectionsService and changing labels - but there is a tidbit I dug up that shows a way to change the label. This particular one still just does letters, but the 'label' components may provide some insight unless someone else has specific examples
  • Before you do all that work, and to actually argue against myself here, technically the above is a solution for chaining stops 1,2,3,4 .... in sequence. Meaning that however you ordered your contacts would be the same sequence, which may not return the 'best' route to go when you look at several stops. If you're looking at 'optimal route' planning, you'll need to get more complex and revert back to Option A which requires some coding in my experience.

    The Google API Guide details some of the possibilities for setting waypoints and optimizing the route as one solution. You'll find details about that in the 'Optional Parameters'.
    https://developers.google.com/maps/documentation/directions/intro