Is it possible to combine a Calendar Report and a Google Maps API integration to figure out routing/scheduling for our service employees?

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
  • (Edited)
Here's an overview of our situation: we have a handful of employees that go out and perform a specific type of service call, and there are 3 possible timeslots for those service calls per day.

When our administrative employees go to schedule a service call with a customer (whose information is already entered in our "customers" table), I'd like to be able to have something that pulls the customer's address and automatically compares it to each of our service employees' addresses (we don't use GPS tracking, but each one tends to stay in a certain radius, so their home address is a proxy for their real-time address) to figure out which of them is closest. Then, once the closest one is identified, feeds that into an "add to calendar" button that lets you create a new event based on that closest employee and the customer's information, and the user can pick a time slot.

The major problem seems to be that the Calendar feature is a report, and not a widget, so I can't set it up where the search is right next to the open calendar and the user doesn't have to switch back and forth between app tabs to find the closest one then check their availability. If this is a correct assessment of that hurdle, is there any decent workaround to it, and if not, what would be the way to incorporate a calendar view on the same page as the search/routing?

I'm already looking into the google maps API family, including the Distance Matrix one, which I will likely be implementing (display multiple routes based on one or more origins/endpoints, but in this case it would just be multiple routes based on one endpoint and a preset number of origins), but definitely open to any number of suggestions!
Photo of S F

S F

  • 110 Points 100 badge 2x thumb
  • unsure

Posted 9 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
I read the complete title and scanned the body of your message. It is possible but I think you are looking at a custom solution to hit all of your requirements many of which I don't think you have surfaced in your message but are there nevertheless.

I created a demo of using the Distance Matrix API which I am trying to locate.

Here is one fragment message (it was altered during the forum software update) that deals with a similar issue

https://community.quickbase.com/quickbase/topics/map-route-planning-for-field-technicians
Photo of S F

S F

  • 110 Points 100 badge 2x thumb
well, good to know that it's at least something that looks workable. Full disclosure: I'm fairly new to QuickBase (haven't built anything up from scratch yet) but have an okay programming background and have spent the last week or so going through every bit of the QB University content from the beginning through the "Api Bootcamp" part of the Advanced videos. definitely agree that there are probably lots of things encapsulated in this idea that I haven't explicitly come out and defined, but I'd like to help out as much as I can with the development.

the other thing that's weighing on me is that given our project integrates with the calendar view, with the upcoming rollout of the new calendar in a couple weeks, it might not even be worth starting this until we can see how it would look and what parts of our existing scheduling process break :\
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
Well I am the resident cynic here but I do think the new calendar it going to be a big improvement and well received. The customization you need will probably be on the map side of things especially if you want some type of dynamic route planning or optimization. There are enormous possibilities here that are largely unexplored. 

I can't wait till I get my hot little hands on that new calendar thingy so I can script it up! QuickBase has assigned their best developers to this project and they have been working at top speed. They could not possibly have had adequate time to think of all the ways I am going to enhance it with script .

BOOM
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
Found it:

Roadtrip
https://haversineconsulting.quickbase.com/db/bmihnujsy

There are 10 roadtrips defined. Here is a screenshot of Embassy Row roadtrip generated from five addresses:



https://haversineconsulting.quickbase.com/db/bmihnujwd?a=dr&rid=7
Photo of S F

S F

  • 110 Points 100 badge 2x thumb
would you mind sharing the method you used to convert the DistanceMatrix API response to that Google Maps image? I've been playing around a bit and figured out a way to concatenate a bunch of tabled values (our service tech addresses + the customer's address) into a DistanceMatrix API url, which is converted into a clickable button ("show routes") in the Customers table.

I would like to have it show an image that displays the wheel-and-spoke-esque mapping of our techs' routes to the customer, or at the very least, a way of displaying the routes ordered by distance, instead of just opening a page with the JSON/XML response...
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb


I am out of planet till Monday but you can find the script I used in the Pastie Database.

Pastie Database.
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=550

I may be slightly delayed in my return as there appears to be a problem with Google's driving directions API at the moment.

Astro Waypoints
https://haversineconsulting.quickbase.com/db/bmihnujwd?a=dr&rid=6
(Edited)