I have added a field for each record containing the KML markup and a tab separated export creates a usable file.

Of course the KML file requires a header and a footer. So far I have always created a new table with the first record being the header and the last record being the footer and my data imported via script in between - this works, but there must be a more elegant solution??

thanks

The elegant way to do it is to use xml/xsl. The example file does this in a couple ways. You'd want the script with Header in it. The line returns are Unix.

ExportData_wXSL_simple.zip

?

Edited February 9, 200718 yr by Guest

ok... i figured out a few things with the help of your example - thanks - where i am struggeling though is how i can get this:

[color:blue]<?xml version="1.0" encoding="UTF-8"?>

Placemarks

These are just some of the different kinds of placemarks with

which you can mark your favorite places

Inspections

into the space where it says: Some text as a header without creating a parsing error?

thanks

I have looked up KML on Google Earth and it looks very different from what you describe.

Here's a sample file with xsl stylesheet for exporting a basic KML document.

Archive.zip

They are not too different - other than the use of a folder to 'house' the placemarks. But your XSL answers my problems, since I could not figure out how to get the header into the kml file.

Thanks

Edited February 10, 200718 yr by Guest

hello - I tried your 1171062703 archive which exports kml files.

I was wondering if you have anything like that for google maps so it could be used within a web browser for multiple locations. I would like to show markers at many locations.

Thanks

Dave

Dear comment,

Thanks for this XSL tip.

Any user of our CRM can now easily map a found list of contacts in Google Earth. Contacts have been geocoded in FMPro already using Google's API.

I did have problems getting the KML file created using your xsl file recognized by Google Earth. So, I compared with a known good KML file and changed the case of 'p' in and tags to 'P'. I also upadted kml version to 2.2 and added tags. I then had success. I don't know which [combination] of these changes made it work.

My working KML.xsl now looks like this:

<?xml version="1.0" encoding="utf-8"?>





xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:kml="http://earth.google.com/kml/2.2"

exclude-result-prefixes="fmp">



















































































,

I have stored the KML.xsl file in a global container in my interface file. On running the 'Map contacts' script:

1. KML.xsl file is exported from the interafce file to the client machine

2. the contacts are exported as a kml file using KML.xsl

3. FMPro then sends an AppleEvent to open the .kml file using GoogleEarth

4. the kml file is imported to Google Earth as a Temporary Places folder.

Mapping 1000 contacts takes about 5 seconds.

Thanks again for providing an important part of the jigsaw.

ft.

PS. This is the first time I have used XSL. Do you mind if I ask where your template came from? Do you just write templates from scratch or is there a template resource?

I have a couple of basic templates that I wrote from scratch, and I adapt those as necessary. By now there are quite a few examples in these forums, and more at FMI's site - so you should be able to create a similar library of reusable code for yourself.

OK, I signed in today to specifically find out if I can map a client database in Filemaker. I have Filemaker Pro 8.5 and a database with just over 500 entries that I would like to map their addresses to give us a picture of where our advertising is working.

How do I generate the XML code out of Filemaker? I see the code samples and they're very helpful but I have no idea how to make Filemaker generate the data necessary for Google Maps.

Here is the process:

1. Get a Google Maps API key from http://code.google.com/apis/maps/signup.html

2. Create a webviewer in FileMaker Pro, and use your new Google Maps API key to pull longitude and latitude data for each record in your database (this is sometimes called 'geocoding').

3. Once your records are geocoded create a KML file (which Google Earth can open) by exporting your records in XML format using a template such as the XSL template mentioned above. The exported records would contain lat/long and perhaps text tags for the Google Earth placemarks, and perhaps urls for icons for different types of placemarks.

4. Open the KML file in Google Earth.

These are the basic steps without the detail, but I think all the info you need for the process is here in the fmforums (I posted various parts at points along my journey, and picked some low-hanging fruit from other posts containing the term 'google earth').

Let me know if you need any more info.

ft.

Edited October 23, 200817 yr by Guest

OK, I am having trouble with step number 2.

[color:blue]...and use your new Google Maps API key to pull longitude and latitude data for each record in your database (this is sometimes called 'geocoding').

I have my API key, it's ABQIAAAA ... ijw. And I have, in the web address, "http://local.google.com/maps?" & "q=" & /*Address=*/ Client Database::Address 1 & "," & /*City=*/ Client Database::City & "," & /*State=*/ Client Database::State & "," & /*Zip Code=*/ RClient Database::Zip & "," & /*Country=*/ ""

I am allowing interaction with web viewer content and I am displaying content in find mode.

Where does my API go?

I feel like I am missing a step.

Below is the script I use to loop through a found set of customers to geocode their addresses.

The script does the following for each record:

1. uses Google's API to pull the geocode for each record's address into the webviewer

2. takes the webviewer content to a global field

3. break the contents of the global field into fields for longitude, latitude and precision for each record

Before running the script You will need to...

1. Switch MyGoogleAPIKey for the actual key that you have.

2. Create the following fields:

gGPSWebViewerContent - global, text

StreetLat - number

StreetLong - number

GPSPrecision - number

3. Give your WebViewer an object name of 'GPSWebService'

Good luck,

ft.

Go to Record/Request/Page 

[ First ] 

Set Web Viewer [ Object Name: "GPSWebService"; URL: "http://maps.google.com/maps/geo?q=" & CustomerAddress & 

"&output=csv&key=MyGoogleAPIKey" ] 

Loop 

Pause/Resume Script [ Duration (seconds): .5 ] 

Set Field [ gGPSWebViewerContent; GetLayoutObjectAttribute("GPSWebService"; "content") ] 

Exit Loop If [ not IsEmpty(gGPSWebViewerContent) ] 

End Loop 

Set Field [ StreetLat; Middle ( gGPSWebViewerContent ; Position(gGPSWebViewerContent;",";0;2) + 1; (Position 

(gGPSWebViewerContent;",";0;3) - Position(gGPSWebViewerContent;",";0;2))-1) ] 

Set Field [ StreetLong; Right 

(gGPSWebViewerContent; 

(Length ( gGPSWebViewerContent) - Position(gGPSWebViewerContent;",";0;3))) ] 

Set Field [ GPSPrecision; Middle ( gGPSWebViewerContent ; Position(gGPSWebViewerContent;",";0;1) + 1; (Position 

(gGPSWebViewerContent;",";0;2) - Position(gGPSWebViewerContent;",";0;1))-1) ] 

Set Field [ GPSHTMLError; Left 

(gGPSWebViewerContent; Position(gGPSWebViewerContent;",";0;1)-1) ] 

Loop 

Go to Record/Request/Page 

[ Next; Exit after last ] 

Set Web Viewer [ Object Name: "GPSWebService"; URL: "http://maps.google.com/maps/geo?q=" & cGeocodeAddress & 

"&output=csv&key= MyGoogleAPIKey" ] 

Loop 

Pause/Resume Script [ Duration (seconds): .5 ] 

Set Field [ gGPSWebViewerContent; GetLayoutObjectAttribute("GPSWebService"; "content") ] 

Exit Loop If [ not IsEmpty(gGPSWebViewerContent) ] 

End Loop 

Set Field [ StreetLat; Middle ( gGPSWebViewerContent ; Position(gGPSWebViewerContent;",";0;2) + 1; (Position 

(gGPSWebViewerContent;",";0;3) - Position(gGPSWebViewerContent;",";0;2))-1) ] 

Set Field [ StreetLong; Right 

(gGPSWebViewerContent; 

(Length ( gGPSWebViewerContent) - Position(gGPSWebViewerContent;",";0;3))) ] 

Set Field [ GPSPrecision; Middle ( gGPSWebViewerContent ; Position(gGPSWeb(gGPSWebViewerContent;",";0;2) - Position(gGPSWebViewerContent;",";0;1))-1) ] 

Edited October 27, 200817 yr by Guest
Amended field references in script to make better sense

I was working my way through this script pretty well until I encountered the second loop and the second instance of setting the webviewer. At that point, the URL included a reference to cGeocodeAddress, which I had not heard of.

Is this a field I should create? If so, is it a text field? A number field? A global field?

Here's hoping you're still monitoring this thread, as I am motivated to learn from your example and make this work. Many thanks...

Rick A.

Pleasanton CA

cGeocodeAddress is a calculation field.

It is the address to be queried, placed on a single line, with parts separated by commas, removing hash marks and replacing 'spaces' with 'plus signs' (which Google seems to like).

hth,

ft.

Hi

thankyou for this help

but there are some corrections i made

or your script is not finish by an end loop

in this line

1/ Set Field [ GPSPrecision; Middle ( gGPSWebViewerContent ; Position(gGPSWeb(gGPSWebViewerContent;",";0;2) - Position(gGPSWebViewerContent;",";0;1))-1) ]

what is this new field gGPSWeb ? in Position(gGPSWeb(gGPSWebViewerContent ....

and there is too much )) at the end if I remove and write only Position(gGPSWebViewerContent.....

2/ What is the difference between "CustomerAddress" and "cGeocodeAddress" ?

Set Web Viewer [ Object Name: "GPSWebService"; URL: "http://maps.google.com/maps/geo?q=" & CustomerAddress &

"&output=csv&key=MyGoogleAPIKey" ]

Set Web Viewer [ Object Name: "GPSWebService"; URL: "http://maps.google.com/maps/geo?q=" & cGeocodeAddress &

"&output=csv&key= MyGoogleAPIKey" ]

In my script , I put the same cGeocodeAddress in both line.

3/ I think, we need to create the following field also : GPSHTMLError

4/ I think we don't need to create an API key, do I ?

"Your API key. Visit http://code.google.c...aps/signup.html to sign up for a free Google Maps API key."

and Sign Up for the Google Maps API

The API keys generated by this page are no longer available. Please see the relevant section below for the API you using.

so the good command is

Set Web Viewer [ Object Name: "GPSWebService"; URL: "http://maps.google.com/maps/geo?q=" & cGeocodeAddress &

"&output=csv" ]

5/ For the futur listeners , here is the xst corrected (hidden on this website)

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

xmlns:kml="http://earth.google.com/kml/2.2"

exclude-result-prefixes="fmp">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes" />

<!--

Copyright © 2007 *COMMENT Visual Realisation

All rights reserved.

-->

<!-- BEGIN TEMPLATE -->

<xsl:template match="/">

<kml>

<Document>

<!-- BEGIN RECORD -->

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

<Placemark>

<name>

<!-- FIELD 1 -->

<xsl:value-of select="fmp:COL[1]/fmp:DATA"/>

</name>

<description>

<!-- FIELD 2 -->

<xsl:value-of select="fmp:COL[2]/fmp:DATA"/>

</description>

<Point>

<coordinates>

<!-- FIELD 3 -->

<xsl:value-of select="fmp:COL[3]/fmp:DATA"/>

<!-- COMMA -->

<xsl:text>&#x002C;</xsl:text>

<!-- FIELD 4 -->

<xsl:value-of select="fmp:COL[4]/fmp:DATA"/>

</coordinates>

</Point>

<!-- END RECORD -->

</Placemark>

</xsl:for-each>

</Document>

</kml>

</xsl:template>

</xsl:stylesheet>

6/ What do you mean by " KML.xsl file is exported from the interface file to the client machine"

if the Export.kml file is created on the server, how do you export it on the client machine ?

by which script

7/ "FMPro then sends an AppleEvent to open the .kml file using GoogleEarth"

ex?

Thanks