Skip to main content
Question

Google Maps Route Planning

  • December 18, 2018
  • 0 replies
  • 270 views

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.
This topic has been closed for replies.

  • Registered
  • December 18, 2018
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

  • Author
  • Registered
  • December 18, 2018
That's exactly what I need! How do I set up passing the fields? 

  • Registered
  • December 18, 2018
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

  • Author
  • Registered
  • December 18, 2018
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?


  • Registered
  • December 18, 2018
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.


  • Registered
  • December 18, 2018
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