How to calculate distance and travel time using Google Maps in FileMaker

Hands up if you’ve ever used Google Maps to calculate distances between two addresses and work out how long it will take you to get there. I’m sure I’m not the only one to use Google Maps for this purpose.

Wouldn’t it be great if we could incorporate this functionality in our FileMaker solutions?

Imagine building a tool for sales reps to help plan their travel time better, or show a list of other clients they can visit along the way?How about building a tool to help clients manage their deliveries better? Or possibly a tool to help you plan your own meetings better?

In this post I’m going to show you how you can use Google Maps within FileMaker to do just this in a matter of minutes.

First, you will need FileMaker Pro 16 (or later). This functionality can be built using FileMaker 15 or earlier, but it’s a lot more effort so I’m only going to be showing you how to build it using FileMaker 16 (or later).

More specifically, we’re going to be using the Google Maps API within FileMaker 16 using the built in JSON parser. If not you’re familiar with these are, don’t worry we’ll cover them shortly.

Step 1 – Creating an API Key within Google Maps

The very first thing we need to do is create an API Key within Google Maps. The key grants your solution permission to use the Google Maps API and is unique to your solution – so don’t give anyone your API Key.

Go to https://developers.google.com/maps/documentation/javascript/get-api-key and click on the ‘GET A KEY’ button. If you don’t have a google account you will need to create one first, it’s free.

Once you click on the ‘GET A KEY’ button, you will see a dialog box as follows:

Create a new project – you can use your solution name as your new project, then click on the ‘Next’ button.

Google will now create your new project and provide you with your unique API Key. Copy the API Key, you’ll be needing it shortly.

Step 2 – Create the necessary fields in FileMaker

For the purpose of this exercise, we’ll create some new global fields within FileMaker. However, please note that in a real world solution you would not necessarily need to create all these fields. Some of these values could be hard coded within your scripts such as the unit of measure (i.e. Imperial or Metric). We’ll get into that shortly.

We need input fields in order to pass values to the Google Maps API, and we also need result fields to display the results returned by the API.

The input fields

These are fields we need to create within FileMaker:

SearchOrigin – Text – Global
SearchDestination – Text – Global
Mode – Text – Global
Units – Text – Global
Avoid – Text – Global

SearchOrigin – lets you specify the origin address to search within Google.

SearchDestination – lets you specify the destination address to search within Google.

Mode –  lets you specify the mode of transport being used, i.e. Driving, Walking, Bicycling or Transit.

Units –  lets you specify the unit of measure to use, i.e. Imperial or Metric.

Avoid – lets you specify what transport options should be avoided, i.e. Tolls, Highways, Ferries.

The result fields

ResultOrigin – Text – Global
ResultDestination – Text – Global
Distance – Number – Global
Duration – Time – Global

ResultOrigin – the actual address that Google is using as the origin.

ResultDestination – the actual address that Google is using as the destination.

Distance – the distance between the two addresses.

Duration – the travel time between the two addresses.

CAUTION: The ResultOrigin and ResultDestination fields are used to display the exact address that Google is using to calculate the distances. If the SearchOrigin or SearchDestination are entered incorrectly, entered partially, or multiple similar results exist, Google may assume you are searching for something else and use an address you weren’t expecting. Therefore Google returns the exact addresses it’s using to calculate distances so you can verify the addresses are in fact the ones you were expecting.

Real life solutions

In a real life solution, if you don’t intend your users to change any of the input options, then you can avoid creating them as fields in FileMaker and hardcode the values in your script instead. For example if you only intend to offer driving directions and no other mode, then you can simply hardcode the value ‘driving’ in your script without having to create a field in FileMaker.

Alternatively, you may choose to create some of these fields within a settings table so that users can customise how the options work. For example you may want to allow users to specify if they want distances shown as Imperial or Metric.

In a real life solution, most likely wouldn’t have users entering the origin address and the destination address manually like we do in the example. In stead you may have a Contacts database with each contact having their own address. You would probably add contacts to a delivery schedule or have some sort of way to choose which two Contacts you want to calculate distances for.

How you build this into your own solutions is entirely up to you and beyond the scope of this post. For the purpose of this exercise we’re simply creating all these fields as global fields within FileMaker just to demonstrate the method and its capabilities.

Step 3 – Write your code in FileMaker

Create a new script in FileMaker. I called mine ‘Get Distance’ but feel free to call it whatever you like.

The script is a little long so we’ll break it up into multiple sections so we can explain what each section does.

Section 1 – Set the variables

This section of the code sets up the variable used within the script. Make sure you enter your API Key you obtained in Step 1. Also, make sure you specify the fields you created in Step 2.

Set Variable [ $APIKey; Value: YOUR_API_KEY]
Set Variable [ $Origin; Value: YOUR_SEARCH_ORIGIN_FIELD]
Set Variable [ $Destination; Value: YOUR_SEARH_DESTINATION_FIELD]
Set Variable [ $Mode; Value: YOUR_MODE_FIELD]
Set Variable [ $Units; Value: YOUR_UNITS_FIELD]
Set Variable [ $Avoid; Value: YOUR_AVOID_FIELD]

Section 2 – Query Google

This next section makes the API call and gets a response.

Set Variable [ $URL; Value:
	Let( [
	o = Substitute ( $Origin ; [" " ; "+"]; ["¶"; "+"]; ["|"; "+"] );
	d = Substitute ( $Destination ; [" " ; "+"]; ["¶"; "+"]; ["|"; "+"] );
	m = Case($Mode <> ""; "&mode=" & Lower ( $Mode ));
	a = Case($Avoid <> ""; "&avoid=" & Substitute ( Lower( $Avoid ) ; "¶" ; "|" ));
	k = "&key=" & $APIKey
];
"https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & o & "&destinations=" & d & m & a & k )

Insert from URL [ Verify SSL Certificates; Select; With dialog: Off; $Source; $URL]
Set Variable [ $ErrorMessage; Value: JSONGetElement ( $Source ; "error_message")]
Set Variable [ $Status; Value: JSONGetElement ( $Source ; "rows[].elements[].status")]

The first script step sets a variable called $URL. This builds the Google Maps API call which is used in the second step.
The second step makes the call to the API. The response from API is stored in the variable $source.

The third and fourth script steps both parse the response from the API using the JSONGetElement function. One gets any errors, the other gets the status of the call.

Section 3 – Validate the response

This next section validates the response in case there were any errors or issues.

IF [ $Source = "" ]
	Show Custom Dialog [ "Message"; "Could not connect to Google Maps."]
ELSE IF [ $ErrorMessage <> ""]
	Show Custom Dialog [ "Message"; $ErrorMessage]
ELSE IF [ $Status = "ZERO_RESULTS"]
	Show Custom Dialog [ "Message"; "Origin or destination address not found."]
ELSE IF [ $Status = "NOT_FOUND"]
	Show Custom Dialog [ "Message"; "Route not found."]
END IF

This should be pretty much self explanatory so I wont get into detail about what it does.

Section 4 – Get results

This section parses the response from the API and gets the results.

Set Variable [ $ResultOrigin; Value: JSONGetElement ( $Source ; "origin_addresses[]" )]
Set Variable [ $ResultDestination; Value: JSONGetElement ( $Source ; "destination_addresses[]")]
Set Variable [ $ResultDistance; Value:
	Let ( [
		dist = JSONGetElement ( $Source ; "rows[].elements[].distance.value" );
		metric = Round(dist / 1000; 2);
		imperial = Round(metric * .621371; 2)
];
Case( $Units = "Metric"; metric; $Units = "Imperial"; imperial ))
]
Set Variable [ $ResultDuration; Value: Let ( dur = JSONGetElement ( $Source ; "rows[].elements[].duration.value" ); GetAsTime(Round((dur/60); 0) * 60) + 0 )]

As discussed earlier, Google may sometimes assume you mean a different address. This usually happens when the address is entered incorrectly, entered partially, or multiple similar results exist. The first two script steps get the actual address that Google used for the calculation and you can present it to the user to verify whether they are correct or not.

The third script step gets the distance and performs a small calculation to show the distance in the correct unit of measure.

The fourth script step gets the travel time and performs a small calculation to show the time correctly in FileMaker.

Section 5 – Use the results

Now we get to use the results. The following script steps simply set whatever field you need set in FileMaker. It’s pretty self expalnatory so I wont go into detail here either.

Set Field [ YOUR_RESULT_ORIGIN_FIELD; $ResultOrigin]
Set Field [ YOUR_RESULT_DESTINATION_FIELD; $ResultDestination]
Set Field [ YOUR_DISTANCE_FIELD; $ResultDistance]
Set Field [ YOUR_DURATION_FIELD; $ResultDuration]

 

That’s it.

Thats it your script is now done and you can call it from FileMaker as you please. You can download the example file below if you want to see it in action. However, please note you will still need to use your own API Key.

Download

Visit the page GetDistance – Calculate Distance and Travel Time using Google Maps API in FileMaker to download the demo file.