12 Replies Latest reply on Jul 21, 2017 8:59 AM by beverly

    XLM XSLT exporting Purchase Order for EDI

    Gingernut

      I have been asked to create an XLM file in a specific format for Purchase orders to be sent to suppliers.

       

      I have read about EDI XLM XSLT and have a general idea of how it all fits together so now need to knuckle down and make it happen

       

      I will need some new fields in my P Order which is the easy bit -and I know I can change the field names etc in an XSLT and make it in the format that is required - (I aim to try and work it out myself as think it will serve me well for other uses) 

       

      What I am struggling with (probably blindingly obvious) is this.

       

      I will be exporting from the P Order Line items and Purchase Oder tables but I only want the P Order header details once and the P Order lines will be the Product Lines in the suppliers XML file.

       

      Do I have to create a variable and set each Purchase Order Item and then add the lines by looping or can the XSLT strip out the superfluous data?

       

      Would SQL an alternative to this by using DISTINCT for the what are effectively header details IE Purchase Order and then the detail for the Lines.

       

      Any help much appreciated. Thank you.

       

       

      My end result needs to be something like this

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

      <root>

      <header>

      <version>1.0</version>

      <loginid></loginid>

      <password></password>

      <company></company>

      </header>

      <orders>

      <order>

      <ordernumber>363</ordernumber>

      <name>Ealing Camera Studio</name>

      <addressline1>243 The Broadway</addressline1>

      <addressline2>Ealing</addressline2>

      <towncity>London</towncity>

      <county></county>

      <postcode>W5 8UY</postcode>

      <country>UK</country>

      <contactname></contactname>

      <contactphone> 020 8745 2514</contactphone>

      <jobnumber>J2142/TD1</jobnumber>

      <serialnumber>GH34554454 (A1234)</serialnumber>

      <machinelocation>2nd Floor</machinelocation>

      <instructions>Please ask Anne to sign</instructions>

      <customerref>CustPOnum</customerref>

      <orderline>

      <orderlinenumber>363</orderlinenumber>

      <sku>34010351</sku>

      <qty>1</qty>

      <price>19.00</price>

      <productcode>TONERLI</productcode>

      <description>Lime Toner</description>

      </orderline>

      <orderline>

      <orderlinenumber>363</orderlinenumber>

      <sku>30010151</sku>

      <qty>1</qty>

      <price>20.00</price>

      <productcode>30010151</productcode>

      <description>Black Toner </description>

      </orderline>

      </order>

      </orders>

      </root>

        • 1. Re: XLM XSLT exporting Purchase Order for EDI
          beverly

          Don't throw SQL into the mix.

           

          What might be easiest is to export from the line items. Include "parent" fields. If you look at the RAW XML, you will see this 'duplicates' the parent info. Just make sure your XPath to get this data ONLY gets the first occurrence for the result XML. The for-each loop would be for the line items of course ('orderline').

           

          Do you have an XSLT started to do this? If you can post, we can help you get it to fit your desired result.

           

          If you are using calculated XML rather than XSLT, then you can do what is needed to grab the correct information as well.

          Beverly

          • 2. Re: XLM XSLT exporting Purchase Order for EDI
            Gingernut

            Thank you Beverly - I am about to embark on the XSLT today.

            Is there any advantage over calculated XLM over exporting as XML and transforming with XSLT?

            I know I can do the former but am quite interested to learn XSLT as can see there being other application for it once I understand and can do it.

             

            I will make a start and post what I think I need but will appreciate any help along the way- or if anyone has any sample files they can post to get me started that would be amazing too.

             

            As an aside

            I wish there were a Kindle or Google play edition of your book FileMaker Pro 6 Developer's Guide to XML/XSL as with all these things I want need it now. Have you considered doing that as most of the print editions are in USA and I am in blighty so would have to wait weeks . Anyway it seems that I can read it online which is a start - Lots of people talk about it so there could be an additional revenue stream for you there :-)

            • 3. Re: XLM XSLT exporting Purchase Order for EDI
              Gingernut

              I have created what I thought was an xslt but it is not correctly in the syntax and as I have cobbled it together from various examples I am not sure where my error is - I have looked for online validators and they just tell me there is a syntax error which I cannot for the life of me see. If anyone can spot it I would be more than grateful

               

               

               

              <?xml version="1.0" encoding="utf-8"?><!-- DWXMLSource="Latest/P Order Lines.xml" -->

               

              <xsl:stylesheet version="1.0"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes ="fmp">

               

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

               

                          <xsl:template match="fmp:FMPXMLRESULT">

               

                <xsl:variable name="firstRecord" select="fmp:FMPXRESULT/fmp:RESULTSET/fmp:ROW[1]"/>         

               

                         <root>

               

                         <header>

               

                         <version>1.0</version>

               

                         <loginid></loginid>

               

                         <password></password>

               

                         <company></company>

               

                         </header>

               

                         <orders>

               

                         <order>

               

                         <ordernumber> <xsl:value-of select="$firstRecord/fmp:COL[1}/fmp:DATA"/></ordernumber>

               

                         <name> <xsl:value-of select="$firstRecord/fmp:COL[3}/fmp:DATA"/></name>

               

                         <addressline1> <xsl:value-of select="$firstRecord/fmp:COL[5}/fmp:DATA"/></addressline1>

               

                         <country>UK</country>

               

                         <contactname></contactname>

               

                         <contactphone> <xsl:value-of select="$firstRecord/fmp:COL[6}/fmp:DATA"/></contactphone>

               

                         <jobnumber>js 987654321</jobnumber>

               

                         <serialnumber>12345678</serialnumber>

               

                         <machinelocation>2nd floor</machinelocation>

               

                         <instructions>PLease ask Anne to sign</instructions>

               

                         <customerref>cust ref in available</customerref>

               

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

               

                         <orderline>

               

                 <orderlinenumber> <xsl:value-of select="fmp:COL[1}"/> </orderlinenumber>

               

                         <sku><xsl:value-of select="fmp:COL[7}"/> </sku>

               

                         <qty><xsl:value-of select="fmp:COL[8}"/></qty>

               

                         <price><xsl:value-of select="fmp:COL[10]"/></price>

               

                         <productcode><xsl:value-of select="fmp:COL[9]"/></productcode>

               

              <description><xsl:value-of select="fmp:COL[12]"/> </description>

               

                          </orderline>

               

                          </xsl:for-each>

               

                          </order>

               

              </orders>

               

              </root>

               

                    </xsl:template>

               

              </xsl:stylesheet>

              • 4. Re: XLM XSLT exporting Purchase Order for EDI
                Jens Teich

                There are many errors like COL[2}, which should be COL[2].

                There are many editors which are able to indicate such errors.

                1 of 1 people found this helpful
                • 5. Re: XLM XSLT exporting Purchase Order for EDI
                  Gingernut

                  Ok I am super excited as I have managed to get the lines part of the Purchase order to work but the header detail IE Orders is not populating. Must be something to do with the $firstrecord variable - Can anyone please point me in the right direction  So near and yet so far .

                  I guess there may be other ways to get the first row data only am happy to learn

                   

                  <?xml version="1.0" encoding="utf-8"?><!-- DWXMLSource="Latest/P Order Lines.xml" -->

                  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp">

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

                              <xsl:template match="fmp:FMPXMLRESULT">

                      <xsl:variable name="firstRecord" select="fmp:fmpxmlresult/fmp:RESULTSET/fmp:ROW[1]"/>      

                            <root>

                            <header>

                            <version>1.0</version>

                            <loginid></loginid>

                            <password></password>

                            <company></company>

                            </header>

                            <orders>

                            <order>

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

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

                            <addressline1> <xsl:value-of select="$firstRecord/fmp:COL[5]/fmp:DATA"/></addressline1>

                            <country>UK</country>

                            <contactname></contactname>

                            <contactphone> <xsl:value-of select="$firstRecord/fmp:COL[6]/fmp:DATA"/></contactphone>

                            <jobnumber>js 987654321</jobnumber>

                            <serialnumber>12345678</serialnumber>

                            <machinelocation>2nd floor</machinelocation>

                            <instructions>PLease ask Anne to sign</instructions>

                            <customerref>cust ref in available</customerref>

                            <orderline>

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

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

                            <sku><xsl:value-of select="fmp:COL[7]"/> </sku>

                            <qty><xsl:value-of select="fmp:COL[8]"/></qty>

                            <price><xsl:value-of select="fmp:COL[10]"/></price>

                            <productcode><xsl:value-of select="fmp:COL[9]"/></productcode>

                    <description><xsl:value-of select="fmp:COL[12]"/> </description>

                              </xsl:for-each>

                              </orderline>              

                              </order>

                    </orders>

                    </root>

                        </xsl:template>

                  </xsl:stylesheet>

                  • 6. Re: XLM XSLT exporting Purchase Order for EDI
                    beverly

                    Hello, you are close.

                    Check the errors as JensT says!

                    I might have just defined the $fmpROW as

                    fmp:FMPXRESULT/fmp:RESULTSET/fmp:ROW

                    Then in your first row it would be:

                    $fmpROW[1]

                    and in the for-each loop it can be used

                    $fmpROW

                    Beverly

                    1 of 1 people found this helpful
                    • 7. Re: XLM XSLT exporting Purchase Order for EDI
                      beverly

                      <xsl:variable name="firstRecord" select="fmp:fmpxmlresult/fmp:RESULTSET/fmp:ROW[1]"/>

                      The use of lowercase 'fmpxmlresult' is not correct. You must match case of the elements and attributes in your source XML.

                      <xsl:variable name="firstRecord" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>

                      Beverly

                      1 of 1 people found this helpful
                      • 8. Re: XLM XSLT exporting Purchase Order for EDI
                        beverly

                        Many do not wish to learn XSLT, so the option to calculated the XML result is perfectly ok.

                         

                        Aside: I have no control of (& no income from) "the book".

                        Beverly

                        • 9. Re: XLM XSLT exporting Purchase Order for EDI
                          Gingernut

                          Would this be instead of defining firstRecord

                           

                          Thanks for bearing with me - I have cobbled this together from other examples I have managed to find but they are from various sources and I have never done anything like this - I know I can calculate it but then I would not have learned anything new :-)

                           

                          <xsl:variable name="firstRecord" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>     

                          would instead be

                           

                          <xsl:variable name="fmROW" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW"/>     

                          and then when I only want the first row the select would be for example

                           

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

                           

                          Do I still need the fmpDATA at the end and what does this do?

                           

                          The Orderline items would then be

                          <orderline>

                                <xsl:for-each select="fmp:RESULTSET/$fmpROW">   or would this just be $fmpROW

                               <orderlinenumber> <xsl:value-of select="$fmpROW:COL[1]"/> </orderlinenumber>

                                     <sku><xsl:value-of select="$fmpROW:COL[7]"/> </sku>

                           

                          I really appreciate your help Beverly . Thank you

                          • 10. Re: XLM XSLT exporting Purchase Order for EDI
                            beverly

                            Hey Gingernut!

                             

                            Using the single variable allows it to be used throughout. Adding the square bracket with the number makes that variable specific and not just "any".

                            Correct:

                            • <xsl:variable name="fmROW" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW"/>

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

                            (yes the COL and DATA are needed too!)

                            • <xsl:for-each select="$fmpROW">

                             

                            Just like the variables we set in FileMaker Pro (and elsewhere), except the variable set to the "list" of all rows (or any row), can also be specific.

                             

                            Beverly

                            1 of 1 people found this helpful
                            • 11. Re: XLM XSLT exporting Purchase Order for EDI
                              Gingernut

                              Thank you - I can now take the weekend off instead of spending it working - This is what I ended up with and it worked Its probably not the cleanest code as using a mix of the variable and full expression but for now I do not care IT WORKS - with the COL  but without the DATA - I will work through it in more detail next week and check with the supplier if it is going to work but I am VERY hopeful now. Cannot thank you enough.

                               

                              Aside (Shame about the book it has great reviews in the community and now the urgency has passed I will order and wait for it anyway)

                               

                              <?xml version="1.0" encoding="utf-8"?><!-- DWXMLSource="Latest/P Order Lines.xml" -->

                              <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp">

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

                                          <xsl:template match="fmp:FMPXMLRESULT">

                                  <xsl:variable name="fmpROW" select="fmp:RESULTSET/fmp:ROW"/>       

                                         <root>

                                         <header>

                                         <version>1.0</version>

                                         <loginid></loginid>

                                         <password></password>

                                         <company></company>

                                         </header>

                                         <orders>

                                         <order>

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

                                         <name> <xsl:value-of select="$fmpROW[1]/fmp:COL[3]"/></name>

                                         <addressline1> <xsl:value-of select="$fmpROW[1]/fmp:COL[5]"/></addressline1>

                                         <country>UK</country>

                                         <contactname></contactname>

                                         <contactphone> <xsl:value-of select="$fmpROW[1]/fmp:COL[6]"/></contactphone>

                                         <jobnumber>js 987654321</jobnumber>

                                         <serialnumber>12345678</serialnumber>

                                         <machinelocation>2nd floor</machinelocation>

                                         <instructions>PLease ask Anne to sign</instructions>

                                         <customerref>cust ref in available</customerref>

                                         <orderline>

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

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

                                         <sku><xsl:value-of select="fmp:COL[7]"/> </sku>

                                         <qty><xsl:value-of select="fmp:COL[8]"/></qty>

                                         <price><xsl:value-of select="fmp:COL[10]"/></price>

                                         <productcode><xsl:value-of select="fmp:COL[9]"/></productcode>

                                 <description><xsl:value-of select="fmp:COL[12]"/> </description>

                                          </xsl:for-each>

                                          </orderline>              

                                          </order>            

                                </orders>

                                </root>

                                    </xsl:template>

                              </xsl:stylesheet>  

                              • 12. Re: XLM XSLT exporting Purchase Order for EDI
                                beverly

                                The DATA is important if the field is related or repeating.

                                Have a good weekend!

                                Beverly

                                 

                                Sent from miPhone