xslt - Issue with XSL Template for Dates from Filemake Exported Data -
ok, i've asked before further along in i'm trying achieve, can (hopefully) explain better.
supporting filemaker application has been updated allow users export data in specific formats, 5 reports in total, , each 1 correct in data produces. make easier end user understand output, have switched simple exports field names using xsl templates allow customise results like. works perfectly, exception of date fields. after discussions , subsequent on filemaker forums, i've got point need specific i'm hoping here can me with.
i'll include relevant snippets of xsl (as stylesheet details , heading rows working correctly), explain code, define couple of styles:
<styles> <style ss:id="heading"> <font ss:size="10" ss:bold="1" /> </style> <style ss:id="datedisplay"> <numberformat ss:format="short date"/> </style> <style ss:id="other"/> </styles> and i've written template manage date formatting either blank or conversion yyyy/mm/dd (the format exported filemaker) yyyy-mm-ddthh:mm:ss.000 (the format suitable excel understand).
<xsl:template name="format-date"> <xsl:param name="dateparam" /> <!--store default time append date--> <xsl:variable name="timeformat" select="concat('t', '00:00:00.000')"/> <!--define default blank dates--> <xsl:choose> <xsl:when test="$dateparam=''"> <xsl:value-of select="concat('1900-01-01', timeformat)" /> </xsl:when> <!--reformat non blank dates--> <xsl:when test="$dateparam!=''"> <!--store month , day elements of date--> <xsl:choose> <xsl:when test="contains($dateparam, '/')"> <xsl:variable name="yearpart" select="substring-before($dateparam, '/')" /> <xsl:variable name="monthpart" select="format-number(number(substring-before(substring-after($dateparam, '/'), '/')), '00')" /> <xsl:variable name="daypart" select="format-number(number(substring-after(substring-after($dateparam, '/'), '/')), '00')" /> <!--concatenate parts make date in correct format--> <xsl:value-of select="concat($yearpart, '-', $monthpart, '-', $daypart, $timeformat)" /> </xsl:when> <xsl:when test="contains($dateparam, '.')"> <xsl:variable name="yearpart" select="substring-before($dateparam, '.')" /> <xsl:variable name="monthpart" select="format-number(number(substring-before(substring-after($dateparam, '.'), '.')), '00')" /> <xsl:variable name="daypart" select="format-number(number(substring-after(substring-after($dateparam, '.'), '.')), '00')" /> <!--concatenate parts make date in correct format--> <xsl:value-of select="concat($yearpart, '-', $monthpart, '-', $daypart, $timeformat)" /> </xsl:when> </xsl:choose> </xsl:when> </xsl:choose> </xsl:template> the issue having record contains non-blank or date not 1900/01/01 gets set 1900-1-01t00:00:00.000, has incorrect value assigned xsl template. have run export using xml (no xsl) confirm data , here sample of produced:
<row modid="4" recordid="19"> <col><data>company limited</data></col> <col><data>617642</data></col> <col><data>company</data></col> <col><data>walker, k</data></col> <col><data>yes</data></col> <col><data /></col> <col><data>active</data></col> <col><data /></col> <col><data /></col> <col><data>01/01/1900</data></col> <col><data>low risk</data></col> <col><data /></col> <col><data>n</data></col> <col><data /></col> <col><data>0</data></col> <col><data /></col> <col><data>4715</data></col> <col><data /></col> <col><data>1460</data></col> </row> <row modid="3" recordid="34"> <col><data>company b limited</data></col> <col><data>662922</data></col> <col><data>company</data></col> <col><data>jones, a</data></col> <col><data /></col> <col><data /></col> <col><data>active</data></col> <col><data /></col> <col><data /></col> <col><data /></col> <col><data /></col> <col><data /></col> <col><data>n</data></col> <col><data /></col> <col><data>0</data></col> <col><data /></col> <col><data>0</data></col> <col><data /></col> <col><data>7973.75</data></col> </row> <row modid="3" recordid="89"> <col><data>company c limited</data></col> <col><data>602611</data></col> <col><data>trustee</data></col> <col><data>smith, r</data></col> <col><data>yes</data></col> <col><data>ftse 100</data></col> <col><data>active</data></col> <col><data /></col> <col><data /></col> <col><data>23/06/2004</data></col> <col><data /></col> <col><data /></col> <col><data>n</data></col> <col><data /></col> <col><data>1816.25</data></col> <col><data /></col> <col><data>0</data></col> <col><data /></col> <col><data>0</data></col> </row> my results should excel workbook single sheet, row 1 contains bold headings , rows 2 onwards data, dates formatted correctly, , other characters removed - here final part of code:
<!--called every "col" node in "row"--> <xsl:template match="fmp:col"> <!--get current field position--> <xsl:variable name="i" select="position()" /> <!--store current field type--> <xsl:variable name="fmtype" select="/fmp:fmpxmlresult/fmp:metadata/fmp:field[$i]/@type" /> <!--create , set field type variable--> <xsl:variable name="sstype"> <xsl:choose> <xsl:when test="$fmtype='number'">number</xsl:when> <xsl:when test="$fmtype='date'">datetime</xsl:when> <xsl:otherwise>string</xsl:otherwise> </xsl:choose> </xsl:variable> <!--create , set cell style variable--> <xsl:variable name="ssstyle"> <xsl:choose> <xsl:when test="$fmtype='date'">datedisplay</xsl:when> <xsl:otherwise>other</xsl:otherwise> </xsl:choose> </xsl:variable> <!--define cell , associated number format--> <cell ss:styleid="{$ssstyle}"> <!--define data , associated type--> <data ss:type="{$sstype}"> <xsl:variable name="d" select="fmp:data" /> <xsl:choose> <!--clean number fields--> <xsl:when test="$fmtype='number'"> <xsl:value-of select="translate($d, translate($d, '0123456789.', ''), '')" /> </xsl:when> <!--reformat date fields--> <xsl:when test="$fmtype='date'"> <xsl:call-template name="format-date"> <xsl:with-param name="dateparam" select="$d" /> </xsl:call-template> </xsl:when> <!--pass other types unchanged--> <xsl:otherwise> <xsl:value-of select="$d" /> </xsl:otherwise> </xsl:choose> </data> </cell> </xsl:template> what same 3 records (after running through xsl) is:
<row> <cell ss:styleid="other"><data ss:type="string">company limited</data></cell> <cell ss:styleid="other"><data ss:type="number">617642</data></cell> <cell ss:styleid="other"><data ss:type="string">company</data></cell> <cell ss:styleid="other"><data ss:type="string">walker, k</data></cell> <cell ss:styleid="other"><data ss:type="string">yes</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string">active</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="datedisplay"><data ss:type="datetime">01-1-1900t00:00:00.000</data></cell> <cell ss:styleid="other"><data ss:type="string">low risk</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string">n</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">0</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">4715</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">1460</data></cell> </row> <row> <cell ss:styleid="other"><data ss:type="string">company b limited</data></cell> <cell ss:styleid="other"><data ss:type="number">662922</data></cell> <cell ss:styleid="other"><data ss:type="string">company</data></cell> <cell ss:styleid="other"><data ss:type="string">jones, a</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string">active</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="datedisplay"><data ss:type="datetime">1900-01-01</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string">n</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">0</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">0</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">7973.75</data></cell> </row> <row> <cell ss:styleid="other"><data ss:type="string">company c limited</data></cell> <cell ss:styleid="other"><data ss:type="number">602611</data></cell> <cell ss:styleid="other"><data ss:type="string">trustee</data></cell> <cell ss:styleid="other"><data ss:type="string">smith, r</data></cell> <cell ss:styleid="other"><data ss:type="string">yes</data></cell> <cell ss:styleid="other"><data ss:type="string">ftse 100</data></cell> <cell ss:styleid="other"><data ss:type="string">active</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="datedisplay"><data ss:type="datetime">23-6-2004t00:00:00.000</data></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string"/></cell> <cell ss:styleid="other"><data ss:type="string">n</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">1816.25</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">0</data></cell> <cell ss:styleid="other"><data ss:type="number"/></cell> <cell ss:styleid="other"><data ss:type="number">0</data></cell> </row> so existing dates (01/01/1900 or real date) reversed , month not prefixed zero, blank date appears added correctly. i'm sure it's template causing these issues, cannot see i've gone wrong looking!
help/advice gratefully received
many thanks
martin
how simple one:
<xsl:template name="format-date"> <xsl:param name="dateparam"/> <xsl:param name="time" select="'t00:00:00.000'"/> <xsl:choose> <xsl:when test="not(string($dateparam))">place default result blank dates here</xsl:when> <xsl:otherwise> <!-- normalize separators "/" --> <xsl:variable name="date" select="translate($dateparam, '.-', '//')"/> <!-- extract date elements --> <xsl:variable name="d" select="substring-before($date, '/')"/> <xsl:variable name="m" select="substring-before(substring-after($date, '/'), '/')"/> <xsl:variable name="y" select="substring-after(substring-after($date, '/'), '/')"/> <!-- construct date elements --> <xsl:value-of select="concat($y, '-', $m, '-', $d, 't00:00:00.000')" /> </xsl:otherwise> </xsl:choose> </xsl:template> note:
this assumes input dates in dd/mm/yyyy or dd.mm.yyyy or dd-mm-yyyy format (not yyyy/mm/dd stated in question), , day , month values zero-padded 2 digits;
if using filemaker's built-in xslt engine, cannot use format-number() function. if need pad values, must use device, e.g. exslt str:align() extension function.
Comments
Post a Comment