Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Export FM records using xslt ... a variation on grouping isn't working right

Cabinetman

By Cabinetman
in XML/XSL

 Share

This topic is 3984 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Cabinetman Proficient

Cabinetman

Posted
Posted

This may not be the best way to do this OR describe the issue ....... but I feel like I'm ever so close.

I can almost get the output but I have one section coming in as blank.

 

Also I'm sorry for the length of this post. Just trying to give all the needed data .................... I hope.

 

I'm exporting 4 records with 4 fields ... but only using 3 fields per line in the exported file..

(Posted at the bottom is an example of the FM export data)

 

My desired output is a tab delim. text file with headers such as this :

sku    id    status
Parent-SKU1     0123456789     Update
Parent-SKU2     0123456789     Update
Child-SKU1     0123456789     Update
Child-SKU2     0123456789     Update
Child-SKU3     0123456789     Update
Child-SKU4     0123456789     Update

Notice that there 2 unique Parent-SKUs : Parent-SKU1 Parent-SKU2 (these are in COL[1] for each row)

 

and 4 unigue Child-SKUs : Child-SKU1 Child-SKU2 Child-SKU3 Child-SKU4 (these are in COL[2] for each row)

 

I'm using this .... within the stylesheet  :

<xsl:template match="/">    
        <!-- Header line, inserted only once per record set -->
        <xsl:text>sku    id    status</xsl:text>
<!-- End of Header -->
<!-- Begin Parent SKUs -->
<xsl:for-each select="xalan:distinct(fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW/fmp:COL[1]/fmp:DATA)">
        <xsl:attribute name="(fmp:COL[1]/fmp:DATA)"><xsl:value-of select="current()"/></xsl:attribute>    
        <xsl:value-of select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW/fmp:COL[1]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:ROW/fmp:COL[3]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:ROW/fmp:COL[4]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
            <xsl:attribute name="(fmp:COL[1]/fmp:DATA)"><xsl:value-of select="fmp:COL[1]/fmp:DATA"/></xsl:attribute>
</xsl:for-each>
<!-- Begin Child SKUs -->
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                <xsl:value-of select="fmp:COL[2]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:COL[3]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:COL[4]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
        </xsl:for-each>
    </xsl:template>
<xsl:variable name="newrecord"><xsl:text>
</xsl:text>
</xsl:variable>

........ AND I'm getting :

sku    id    status
         
         
Child-SKU1     0123456789     Update
Child-SKU2     0123456789     Update
Child-SKU3     0123456789     Update
Child-SKU4     0123456789     Update

As you can see I get the 2 lines in there but not the DATA (and it does include the spacing between each export field)

 

I've tried all kinds of variations on the 'distinct' section .... since I feel sure that's where the error is ..... trying to pull the data in but I can't seem to figure it out.

 

FM export example data is :

<FMPXMLRESULT>
  <ERRORCODE>0</ERRORCODE>
  <PRODUCT BUILD="06-18-2009" NAME="FileMaker" VERSION="ProAdvanced 10.0v3"/>
  <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="SSP_Variations_1_3 BU.fp7" RECORDS="6" TIMEFORMAT="h:mm:ss a"/>
  <METADATA>
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SKU-Parent" TYPE="TEXT"/>
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SKU-Child" TYPE="TEXT"/>
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="id" TYPE="TEXT"/>
    <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="status" TYPE="TEXT"/>
  </METADATA>
  <RESULTSET FOUND="6">
    <ROW MODID="61" RECORDID="83">
      <COL>
        <DATA>Parent-SKU1</DATA>
      </COL>
      <COL>
        <DATA>Child-SKU1</DATA>
      </COL>
      <COL>
        <DATA>1234567890</DATA>
      </COL>
      <COL>
        <DATA>Update</DATA>
      </COL>
    </ROW>
    <ROW MODID="36" RECORDID="84">
     <COL>
       <DATA>Parent-SKU1</DATA>
     </COL>
     <COL>
       <DATA>Child-SKU2</DATA>
     </COL>
     <COL>
       <DATA>1234567890</DATA>
     </COL>
     <COL>
       <DATA>Update</DATA>
     </COL>
    </ROW>
    <ROW MODID="29" RECORDID="85">
      <COL>
        <DATA>Parent-SKU2</DATA>
      </COL>
      <COL>
        <DATA>Child-SKU3</DATA>
      </COL>
      <COL>
        <DATA>1234567890</DATA>
      </COL>
      <COL>
        <DATA>Update</DATA>
      </COL>
    </ROW>
    <ROW MODID="21" RECORDID="86">
      <COL>
        <DATA>Parent-SKU2</DATA>
      </COL>
      <COL>
       <DATA>Child-SKU4</DATA>
      </COL>
      <COL>
        <DATA>1234567890</DATA>
      </COL>
      <COL>
        <DATA>Update</DATA>
      </COL>
    </ROW>
  </RESULTSET>
</FMPXMLRESULT>

I'm sure I've left out something, am going about this wrong or not explained this well enough but here it is .....

 

ALL help is appreciated !!

 

 

 

 

Cabinetman Proficient

Cabinetman

Posted
  • Author
Posted

Stylesheet ...........

<?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:xalan="http://xml.apache.org/xalan"
exclude-result-prefixes="xalan">
    <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
    <!--
File: custom_delim.xsl

Transforms data in FMPXMLRESULT grammar into a text file with the
specified characters as field and record delimiters.

Note that it is possible to choose field and record delimiters
that would result in an exported file that FileMaker Pro can no
longer import.

For example:

Database data
=============
White     Johnson  Red       33
Green     Mark     Red      100
Blue      Sam      Blue     201
Yellow    Susan    Green    839
Straight  Wendy    Orange    22

Output
======
White*Johnson*Red*33
Green*Mark*Red*100
Blue*Sam*Blue*201
Yellow*Susan*Green*839
Straight*Wendy*Orange*22
 
===============================================================

Copyright © 2002 FileMaker, Inc.
All rights reserved.

Redistribution and use in source and binary forms, with or
without modification, are permitted provided that the following
conditions are met:

* Redistributions of source code must retain the above copyright
  notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in
  the documentation and/or other materials provided with the
  distribution.

* Neither the name of the FileMaker, Inc. nor the names of its
  contributors may be used to endorse or promote products derived
  from this software without specific prior written
  permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
    
===============================================================
-->
<xsl:template match="/">    
        <!-- Header line, inserted only once per record set -->
        <xsl:text>sku    id    status</xsl:text>
<!-- End of Header -->
<!-- Begin Parent SKUs -->
<xsl:for-each select="xalan:distinct(fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW/fmp:COL[1]/fmp:DATA)">
        <xsl:attribute name="(fmp:COL[1]/fmp:DATA)"><xsl:value-of select="current()"/></xsl:attribute>    
        <xsl:value-of select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW/fmp:COL[1]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:ROW/fmp:COL[3]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:ROW/fmp:COL[4]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
            <xsl:attribute name="(fmp:COL[1]/fmp:DATA)"><xsl:value-of select="fmp:COL[1]/fmp:DATA"/></xsl:attribute>
</xsl:for-each>
<!-- Begin Child SKUs -->
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                <xsl:value-of select="fmp:COL[2]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:COL[3]/fmp:DATA"/><xsl:text>   </xsl:text><xsl:value-of select="fmp:COL[4]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
        </xsl:for-each>
    </xsl:template>
<xsl:variable name="newrecord"><xsl:text>
</xsl:text>
</xsl:variable>
</xsl:stylesheet>
<?xml version="1.0" encoding="UTF-8" ?><FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"><ERRORCODE>0</ERRORCODE><PRODUCT BUILD="06-18-2009" NAME="FileMaker" VERSION="ProAdvanced 10.0v3"/><DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="SSP_Variations_1_3 BU TEST.fp7" RECORDS="4" TIMEFORMAT="h:mm:ss a"/><METADATA><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="SKU Parent" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Child_SKU" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="id" TYPE="TEXT"/><FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="status" TYPE="TEXT"/></METADATA><RESULTSET FOUND="4"><ROW MODID="2" RECORDID="90"><COL><DATA>Parent-SKU1</DATA></COL><COL><DATA>Child-SKU1</DATA></COL><COL><DATA>1234567890</DATA></COL><COL><DATA>Update</DATA></COL></ROW><ROW MODID="2" RECORDID="91"><COL><DATA>Parent-SKU1</DATA></COL><COL><DATA>Child-SKU2</DATA></COL><COL><DATA>1234567890</DATA></COL><COL><DATA>Update</DATA></COL></ROW><ROW MODID="2" RECORDID="92"><COL><DATA>Parent-SKU2</DATA></COL><COL><DATA>Child-SKU3</DATA></COL><COL><DATA>1234567890</DATA></COL><COL><DATA>Update</DATA></COL></ROW><ROW MODID="2" RECORDID="93"><COL><DATA>Parent-SKU2</DATA></COL><COL><DATA>Child-SKU4</DATA></COL><COL><DATA>1234567890</DATA></COL><COL><DATA>Update</DATA></COL></ROW></RESULTSET></FMPXMLRESULT>
comment Grand Master

comment

Posted
Posted (edited)

I don't know what you're trying to do with attributes; attributes are applied to XML elements - your output is text.

Looking at your desired output, there is no "grouping" as such going on here. It looks like you simply want to add two "records" - one for each parent SKU - to the 4 existing child records. See if this makes sense:

<?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:xalan="http://xml.apache.org/xalan">

<xsl:output method="text" encoding="UTF-8"/>

<xsl:variable name="newline"><xsl:text>
</xsl:text></xsl:variable>

<xsl:variable name="tab"><xsl:text>    </xsl:text></xsl:variable>

<xsl:template match="/">   

<!-- Header line, inserted only once per record set -->
<xsl:text>sku    id    status
</xsl:text>
<!-- End of Header -->

<!-- Begin Parent SKUs -->
<!-- 1.GET DISTINCT Parent SKUs -->
<xsl:for-each select="xalan:distinct(fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW/fmp:COL[1])">
<!-- 2.FOR EACH OF THESE, USE THE DATA FROM THEIR OWN ROW -->
    <xsl:value-of select="concat(current(), $tab, ../fmp:COL[3], $tab, ../fmp:COL[4], $newline)"/>
</xsl:for-each>

<!-- Begin Child SKUs -->
<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
    <xsl:value-of select="concat(fmp:COL[2], $tab, fmp:COL[3], $tab, fmp:COL[4], $newline)"/>
</xsl:for-each>

</xsl:template>
</xsl:stylesheet>
Edited by comment
  • 3 weeks later...
Cabinetman Proficient

Cabinetman

Posted
  • Author
Posted

BEYOND FRUSTRATED !! I have spent days and days on this ......................

 

FM is adding extra CRLF's in the export ... an issue I've had for some time but that didn't really effect anything ... and breaking the upload in this use case.

 

My header, which I didn't see how it could matter ... or think it would, has 2 lines.

 

TemplateType=Wireless    Version=2013.0903

sku    id    status

 

 

SSSsssooooooooooooo what I end up getting is :

 

TemplateType=Wireless    Version=2013.0903

 

sku    id    status

 

Parent-SKU1    1234567890    Update

 

Child-SKU1    1234567890    Update

 

Child-SKU2    1234567890    Update

 

Child-SKU3    1234567890    Update

 

Child-SKU4    1234567890    Update

 

 

If I check the export in Notepad it LOOKS normal. However when I throw it into Notepad++ and view the symbols it shows .........

 

TemplateType=Wireless    Version=2013.0903

sku    id    statusCRLF
CRLF
Parent-SKU1    1234567890    UpdateCRLF
CRLF
Child-SKU1    1234567890    UpdateCRLF
CRLF
Child-SKU2    1234567890    UpdateCRLF
CRLF
Child-SKU3    1234567890    UpdateCRLF
CRLF
Child-SKU4    1234567890    UpdateCRLF
CRLF

 

I am just at a loss on this .................

 

ANY Ideas ?????

comment Grand Master

comment

Posted
Posted

It's possible that this a Windows-related bug. To be sure, try replacing this part:

<xsl:variable name="newline"><xsl:text>
</xsl:text></xsl:variable>

with:

<xsl:variable name="newline" select="'&#13;&#10;'" />

and this part:

 

<xsl:text>sku    id    status
</xsl:text>

 

with:

<xsl:text>sku    id    status</xsl:text>
<xsl:value-of select="$newline" />

or possibly:

<xsl:text>sku    id    status</xsl:text><xsl:value-of select="$newline" />

If there is a difference between the last two, then you are definitely affected by the said bug. IIRC, the workaround is to remove all white space between all elements of the stylesheet.

Cabinetman Proficient

Cabinetman

Posted
  • Author
Posted

I was able to take a different route with an XML upload but still need to figure this out.

 

I'll test it out later and post back about it but I did try several similar things myself before ....

This topic is 3984 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept