In my FileMaker Pro solution, I would like to be able to reliably import date values from an XML file using an appropriate .xslt file. In the XML schema the date entities are defined to be xs:date. Most commonly I would have the format of

2009-11-25 and similar

but since xs:date can optionally include timezone info, the value might be any of the following formats

2009-11-25+2:00

2009-11-25Z

2009-11-25+00:00

or even

-2009-11-25

(I'm not sure why a leading dash is permitted in an xs:date.)

For my needs I will be ignoring any time zone information and expect only 4 digit years, but I haven't found a good way to handle converting to the user's localized format of their FileMaker date, i.e, I need to be able to support 11/25/2009 and 25/11/2009.

Up until now, I have been assuming that all of the xs:date values are yyyy-MM-dd and that FileMaker is using MM/dd/yyyy for its import and export. I created a rudimentary xsl:template that I call with a parameter to scan for '-' or '/' and convert the format of the strings. This method breaks for users preferring dd/MM/yyyy and for dates in the XML that aren't precisely yyyy-MM-dd.

Does anybody have any good suggestions?

XPath 2.0 has some built in functions to extract the pieces from xs:date but FM10 seems to only support XPath 1.0.

FileMaker Web Publishing has fmxslt: functions for date conversion, but I'm doing direct XML file import/export so those aren't available.

I'd like to find a method of handing this in a robust way directly in .xslt. There are many dates going into and coming out of a variety of FileMaker fields in different tables. (I import multiple times from the XML file to populate the tables and export multiple times to generate the final XML file.)

-Tom

Tom, a template to parse is your best method. Beg for XSLT 2.0, while you're at it.

FMI feature requests

Tom, a template to parse is your best method.

I'm not so sure about that now.

I've been doing some more research on this since my original post. I think that the date import feature of FileMaker may be fundamentally broken with respect to date localizations and XML. The DATEFORMAT="format string" sequence appears in the metadata for FMXMLRESULT but is completely ignored when importing. Thus there is no way for the .xslt file to know what is the appropriate date format for that specific FileMaker installation. For my runtime solution distributed worldwide, this is a show stopper.

An alternative is to have a different XSLT file for each localization. Somehow detect the required data entry format for date fields in FileMaker and then invoke the correct XSLT parser.

My alternate solution in FileMaker is to import the YYYY-MM-DD format dates directly into the FileMaker date fields (resulting in '?' in each date). After importing I replace the field contents with a calculated result that uses Date(month, day, year) to insert the local date format. This is aesthetically unappealing, but it seems to work.

When exporting, I can use the DATEFORMAT string in the exported XML file to determine the localized format of the FM date fields. A xsl:template parser can do the work there. The trick is to parse the DATEFORMAT string and apply it in the template. Or choose between the limited number of DATEFORMAT values that would be generated worldwide. (In practice I think it may be limited to five or under distinct strings.)

Since I was already committed to importing YYYY-MM-DD directly into FileMaker, I opted to put the FM date fields on a dedicated layout and apply FileMaker formatting to display as YYYY-MM-DD. Then export "using the layout's format" option will put YYYY-MM-DD directly into the XML file.

Beg for XSLT 2.0, while you're at it.

FMI feature requests

At your recommendation I did that just now. Thank you for the link.