Reputation: 155

Issue with XSL Template for Dates from Filemake Exported Data

OK, so I've asked about this before but now am further along in what I'm trying to achieve, so can (hopefully) explain better.
I am supporting a FileMaker application which has been updated to allow users to export data in specific formats, five reports in total, and each one is correct in the data it produces. To make it easier for the end user to understand the output, we have switched from simple exports with field names to using XSL templates to allow us to customise what the results look like. It all works perfectly, with the exception of date fields. After discussions and subsequent help on the FileMaker forums, I've got to a point where I need specific help that I'm hoping someone here can help me with.
I'll include the relevant snippets of the XSL (as the stylesheet details and heading rows are working correctly), but to explain my code, I define a 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 a template to manage the date formatting from either blank or conversion from YYYY/MM/DD (the format exported by FileMaker) to YYYY-MM-DDTHH:MM:SS.000 (the format suitable for Excel to understand).

  <xsl:template name="format-date">
    <xsl:param name="dateParam" />
    <!--store default time to append to date-->
    <xsl:variable name="timeFormat" select="concat('T', '00:00:00.000')"/>
    <!--define default for 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 the month and day elements of the 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 all the parts to make a date in the 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 all the parts to make a date in the correct format-->
            <xsl:value-of select="concat($yearPart, '-', $monthPart, '-', $dayPart, $timeFormat)" />
          </xsl:when>
        </xsl:choose>
      </xsl:when>
    </xsl:choose>
  </xsl:template>

The issue I am having is that any record that contains a non-blank or date which is not 1900/01/01 gets set to 1900-1-01T00:00:00.000, so has the incorrect value assigned by the XSL template. I have run my export using XML (no XSL) to confirm the data and here is a sample of what is produced:

<ROW MODID="4" RECORDID="19">
<COL><DATA>Company A 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 be an Excel workbook with a single sheet, row one contains bold headings and rows 2 onwards are the data, with dates formatted correctly, and any other characters removed - here is the final part of the code:

  <!--called for every "COL" node in a "ROW"-->
  <xsl:template match="fmp:COL">
    <!--get the current field position-->
    <xsl:variable name="i" select="position()" />
    <!--store the current field type-->
    <xsl:variable name="fmType" select="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[$i]/@TYPE" />
    <!--create and 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 and 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 and associated number format-->
    <Cell ss:StyleID="{$ssStyle}">
      <!--define data and associated type-->
      <Data ss:Type="{$ssType}">
        <xsl:variable name="d" select="fmp:DATA" />
        <xsl:choose>
          <!--clean up 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 I actually get for the same three records (after running through the XSL) is:

<Row>
<Cell ss:StyleID="Other"><Data ss:Type="String">Company A 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 any existing dates (01/01/1900 or a real date) get reversed and the month is not prefixed with zero, but the blank date appears to get added correctly. I'm sure it's my template causing these issues, but I cannot see where I've gone wrong for looking!
Any help/advice gratefully received
Many thanks
Martin

Upvotes: 0

Answers (2)

MartinS

Reputation: 155

I got there, and have written a generic template to deal with both (though would like to understand how to control the output format from Filemaker, but that's another question!). So here's my working code:

  <!--set default and reformat dates-->
  <xsl:template name="format-date">
    <xsl:param name="dateParam"/>
    <!--store default time to append to date-->
    <xsl:param name="time" select="'T00:00:00.000'"/>
    <xsl:choose>
      <!--set default value for blank entries-->
      <xsl:when test="not(string($dateParam))">
        <xsl:value-of select="concat('1900-01-01', $time)" />
      </xsl:when>
      <!--reformat non blank dates-->
      <xsl:otherwise>
        <xsl:choose>
          <!--deal with dates in the format 'dd mmm yyyy'-->
          <xsl:when test="contains($dateParam, ' ')">
            <xsl:variable name="d" select="substring-before($dateParam, ' ')"/>
            <xsl:variable name="m" select="substring-before(substring-after($dateParam, ' '), ' ')"/>
            <xsl:variable name="y" select="substring-after(substring-after($dateParam, ' '), ' ')"/>
            <!--convert the month name to a number-->
            <xsl:variable name="mNum" select="
                          string-length(substring-before(
                            'JanFebMarAprMayJunJulAugSepOctNovDec', 
                            substring($m, 1, 3))) div 3 + 1"
                />
            <xsl:choose>
              <xsl:when test="number($mNum) &lt; 10">
                <!-- construct date from elements -->
                <xsl:value-of select="concat($y, '-0', $mNum, '-', $d, $time)" />
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="concat($y, '-', $mNum, '-', $d, $time)" />
              </xsl:otherwise>
            </xsl:choose>
          </xsl:when>
          <xsl:otherwise>
            <!-- normalize separators to "/" -->
            <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 from elements -->
            <xsl:value-of select="concat($y, '-', $m, '-', $d, $time)" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

Thanks for you help/advice
Martin

Upvotes: 0

michael.hor257k

Reputation: 117103

How about a 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 your default result for blank dates here</xsl:when>
        <xsl:otherwise>
            <!-- normalize separators to "/" -->
            <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 from elements -->           
            <xsl:value-of select="concat($y, '-', $m, '-', $d, 'T00:00:00.000')" />
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

Note:

This assumes that the input dates are in DD/MM/YYYY or DD.MM.YYYY or DD-MM-YYYY format (not YYYY/MM/DD as stated in your question), and that the Day and Month values are zero-padded to two digits;
If you are using Filemaker's built-in XSLT engine, you cannot use the format-number() function. If you do need to pad the values, you must use another device, e.g. the EXSLT str:align() extension function.

Upvotes: 1

Issue with XSL Template for Dates from Filemake Exported Data

Answers (2)

Related Questions