Mapping from FileMaker Pro with OpenStreetMap and Leaflet Markercluster.js

2018-12-17 Update: It has been pointed out by reader Andreas that the geo coding function no longer works in this demo file. As I recall, this demo was still using the Google API to get the geo coordinates.

  • I haven’t yet found a URL method for looking up Map Coordinates for Longitude and Latitude. This capability may be there, but the documentation in WIki format, is a little harder to follow than Google’s well documented API. Accordingly, I am still using the Google URL method to lookup coordinates. These calls, if kept in reason do not seem to require a Google Mapping API Key.
Google is continuously changing and updating their API, usually with a view to further monetizing everything they do. I looked at Open Street Maps to see if their geocoding instructions were any less Byzantine, but it doesn’t look like it to me.
My suggestion would be to sign up for a Google API key for Geo Coding and give them your credit card. The costs are small unless your numbers are huge.
To fix the URL see the documentation here:
You will need to update the script called ‘Google Maps – Get Coordinates for this Address’ to include the API key in the Insert from URL option. The new format looks like this:,+Mountain+View,+CA&key=YOUR_API_KEY
While you are at it, you might as well switch the format from csv to JSON and use FM 17’s new JSON parsing functions to grab the Long/Lat info.

Recently, when searching for updates to markercluster.js on Google, I found an interesting Javascript library called Leaflet Markercluster, that uses OpenStreetMap instead of Google Maps. OpenStreetMap is an open source, Wikipedia-like project for maps. Apple is using some of the data from OpenStreetMap in the Maps app on iOS and Macintosh. The maps and data are not as robust as Google Maps yet, but it keeps getting better. You may need to experiment a bit with your own data to see if it meets your standards.

Regular readers of this blog may recognize the demo file. I modified my existing Google Cluster Maps demo database to use with the new system.

This demo file should run fine in either FileMaker Pro or 13 — no special 13 features were employed. In fact, if you really need this to run in earlier versions of FileMaker, it could probably be made to run in FileMaker 7 format. The only thing that would probably need to be redone is the Get Coordinates script, which now employ the Insert from URL step. If you look back on early versions of the Cluster Maps demo, they were done in FileMaker 11 and used a Web Viewer instead.

The new Javascript library is called Leaflet.markercluster.js. It is a nice little library with bouncy little animations for the clusters and individual points when they break out. It also resizes the map dynamically, centering on your data points, so there is no need for globals to set the Longitude and Latitude for map view.



Some nice features of the Leaflet Javascript Library:

  • more modern look
  • animations when drilling into the clusters
  • handles multiple address at the same location
  • no Google API key required
  • performance feels faster than Google Maps

Limitations of OpenStreetMap

  • The OpenStreetMap data is not as complete as Google Maps
  • I haven’t yet found a URL method for looking up Map Coordinates for Longitude and Latitude. This capability may be there, but the documentation in WIki format, is a little harder to follow than Google’s well documented API. Accordingly, I am still using the Google URL method to lookup coordinates. These calls, if kept in reason do not seem to require a Google Mapping API Key.
  • There may be issues with the FileMaker export displaying Unicode characters — specifically German accent characters. FileMaker is exporting as UTF-8. One beta tester was set to give up but then tried Windows ANSI in the export settings and was able to get the data to display properly.
  • Running this solution on a Network, I recommend you add in some Pauses to allow the exports to complete. Without these pauses in place, you may experience a FileMaker Pro crash.
  • The demo was tested on Windows 8.1. It seems to work OK, except, when the exported file tried to auto-open, Windows threw up a dialog box asking which application to open the file in. The first time you run the script, you may need to hide FileMaker Pro’s window temporarily once the export has happened and select your favourite browser to view the file in. Depending on your security settings, you may also need to click the ‘Allow Blocked Content’ button for the Javascript mapping to run.

Demo File


Download Demo File

1) To test the demo, I recommend you select a subset of points, for instance all the California addresses.
2) Choose where you would like the files to export to.

  • If you want to examine the output, put them on your Desktop.
  • If you want to just view the file in a browser, use the Temporary Folder (files get erased when FileMaker quits).
  • To run the solution on an iOS device, choose the Documents Folder.
  • For simplicity all the files (HTML, Javascript, CSS) are set to export at the same level. FileMaker Pro does not have the capability of generating folders on the fly, otherwise, it would probably be nice to put the support files in a folder, one level down from the main HTML file.

3) Set the Title for the HTML file. This will also be the file name with a .html extension.
4) With a small subset, click the ‘Generate Cluster Map’ button at the top.
5) Click the ‘OK’ button and watch the screen flash around for a bit.

Settings Tab


The Settings tab is where all the global fields which store the necessary Javascript, HTML and CSS files that make this map look so good.

Adding the OpenStreetMap Cluster Mapping functionality to your solution


To add this Cluster Mapping capability to your solution,

Custom Functions

  • Copy or recreate two Custom Functions to solution:
    • Extract – //Extract(source, start, end) Middle ( source; Position ( source; start; 1; 1)+Length(start); Position(source; end; Position ( source; start;1;1); 1 )-(Position(source;start;1;1)+Length(start)))
    • GeoBox -Source:


  • Add the Virtual List Utility data table to your solution. No relationship is needed.
  • Before you copy over the Settings table into your solution, rename the Settings table to whatever you have named the Global Settings table in your solution. If you have no Settings table, add one. This is where you will store the Global fields for HTML, Javascript and CSS text for the purposes of importing.


  • Copy all the Global fields from the Settings table into your settings table
  • If necessary, add a set of numeric Longitude and Latitude field to your ‘Data’ data table.
  • Add a text Global ‘XML Coordinates Catcher’ field to your Data table. This is used in the Insert from URL step in the Get Coordinates script.


  • Copy the set of OpenStreetMap scripts into your solution.
  • In the “Generate HTML file” script, modify the Go to Layout step to go to a layout in your solution that you are planning to have the address information come from.
  • Go into the script called “Generate HTML file” and set the fields in the $NameAndAddress variable to your data table’s address fields.
  • Copy the Get Coordinates scripts in to your solution. Edit the script step that sets the XML Coordinates Catcher to your newly created field.
  • If you plan on running the solution in a network situation, add some 3 second pauses to slow down the exports and prevent FileMaker Pro crashes. These steps are already in the script, but should be set to Active.

Value Lists

  • Create a Value List for the Export Location popup list. Include:
    1. Desktop Path
    2. Documents Folder
    3. FileMaker Folder
    4. Temporary Path
    5. FileMaker Web


  • Add the Settings fields to your Settings layout
  • Copy the ‘Generate Cluster Map’ button and add it to your layout.


  • Import or Copy and Paste all the text from the Settings tab in the demo solution into your solution. If you are HTML-savvy, you can edit the HTML text to display other things you want to show up on your webpage.
  • If you do not already have Longitude and Latitude numbers for the addresses in your solution, run the Get Coordinates for All Records script first.
  • Test the ‘Generate Cluster Map’ button.
  • Test. Fix, Test. Repeat as necessary.

Edit this script: OpenStreetMap – Generate HTML file


Put whatever data you want to display on the map pins in the $NameAndAddress variable. Set the $Long and $Lat variables to your coordinates fields.

Clusters on a Map


If you zoom in and click on the coloured circles, they should break down into blue dots. The number on the cluster indicates how many data points are in the cluster.

Clicking on a blue dot will reveal the name of the location


Multiple locations at a single address — Leaflet displays them


This was one of the limitations of the markercluster.js using Google Maps. It could not handle multiple addresses.

That’s it. Have fun with it. Let me know of limitations you find or suggested improvements. Hope it is useful to your solutions. If you really like it, donate to the OpenStreetMap project.

2014-07-04 Update: Modified the demo file to include scripts that get coordinates from OpenStreetMap instead of using  Google’s API. The OpenStreetMap lookup is much more sensitive to anomalies in your addresses, so you may need to edit your addresses to get good results.

2014-05-23 Update: Reader Bruce Robertson suggested a simpler approach to having to reset the serial number each time the script runs. Instead of using the ID as the basis of the numbering scheme in the Virtual List, he recommended using Get(RecordNumber). That way, as long as there are enough records in the Virtual_List_Utility table to match the lines of text in the $$virtual_list Global Variable, it doesn’t matter what the serial number is. What that would look like is changing the Field definition of virtual_list to:

GetValue($$virtual_list; Get(RecordNumber))

2014-07-08 Update: I tried running the Virtual List technique on FileMaker Server and my observation was that the routine of deleting all records, resetting the serial number to 1 and recreating the correct number of records was quite slow. It also exposed the user to a message saying ‘Deleting records’ which can be unnerving if they don’t know what is going on. Instead, at the suggestion of a clever developer on the Facebook FileMaker Users & Developers Group, I modified the Virtual List field to reference Get(RecordNumber), instead of the ID field, so it doesn’t matter what serial number it is. The virtual_list field definition now looks like this:

GetValue($$virtual_list; Get(RecordNumber))

Then I changed from the Delete All, reset the serial number and recreate new records approach. Now the Export script tests to see if there are enough records and add more if needed. If there are too many records, the script omits the extra records. Also, I added a button to display the Virtual List layout so you can see what is going on after the export is done. If you downloaded the demo before, I recommend you download a fresh copy. I think it is improved.

7 Responses to “Mapping from FileMaker Pro with OpenStreetMap and Leaflet Markercluster.js”

  1. As always, you are doing a great work !



  2. Apple does not contribute to OpenStreetMap. They used some of the data when they broke up with Google back in 2011-2012, but they aren’t contributing any data since then.

    (A minor point, but the preferred branding of OpenStreetMap does not have an s at the end)

  3. I see that you’re using Google’s APIs for geocoding, but not for mapping. This violates Google’s terms of use, which state that you’re not allowed to use the other APIs in their mapping family if that isn’t supporting your use of their maps. The documentation for the OpenStreetMap geocoding API ( is not as well written, but it does exist and it can be done. MapQuest also has a geocoding API ( (2 actually, one of which uses the OpenStreetMap data), which has better-written documentation. The MapQuest API does require an application key, but you can get one for free if your usage falls within their limits.

  4. danielshanahan July 8, 2014 at 4:02 am

    Nice post, Douglas. Thanks for doing the research and writing about it. I’ve often wondered about Google alternatives. I’ve played a little with Nokia’s maps ( but in the end, I go back to Google.

    I do like knowing the options, though and appreciate the time and energy it takes to explore those options and sharing them with the community. Thanks!


  1. An Alternate to Google Maps? Not Entirely. - June 30, 2014

    […] There is a list of limitations, as well, and pretty good instructions for integrating it into your solution.  Follow the link below to get started. More…Mapping from FileMaker Pro with OpenStreetMap and Leaflet Markercluster.js | HomeBase Software. […]

%d bloggers like this: