11 Replies Latest reply on May 30, 2010 1:39 PM by rrighini

    XSLT related records

    rrighini

      Title

      XSLT related records

      Post

      Can someone tell me where I'm going wrong?  I have two tables - PupilDetails and Exclusions related to each other by PupilRecordNo.  I want to export pupil details and then related exclusion records(which may be more than one).  This is my xslt

       

      <?xml version='1.0' encoding='UTF-8' ?>
      <xsl:stylesheet version='1.0'   
       xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
       xmlns:fm="http://www.filemaker.com/fmpdsoresult"
       exclude-result-prefixes="fm">
       <xsl:output version='1.0' encoding='UTF-8' indent='yes' method='xml' />
       <xsl:template match="/">
      <Pupils>
       <PupilsOnRoll>
       <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">
         <PupilOnRoll> 
          <PupilIdentifiers>
           <UPN><xsl:value-of select="./fm:_UPN" /></UPN>
           <UniqueLearnerNumber><xsl:value-of select="./fm:ULN" /></UniqueLearnerNumber>
           <FormerUPN><xsl:value-of select="./fm:_formerUPN" /></FormerUPN>
           <Surname><xsl:value-of select="./fm:_surname" /></Surname>
           <Forename><xsl:value-of select="./fm:_forename" /></Forename>
          </PupilIdentifiers>
         <Exclusions>
           <TermlyExclusion>
            <Category><xsl:value-of select="./fm:_CategoryCode" /></Category>
            <Reason><xsl:value-of select="./fm:_ReasonCode" /></Reason>
            <StartDate><xsl:value-of select="./fm:_StartDate" /></StartDate>
            <Sessions><xsl:value-of select="./fm:_NoSessions" /></Sessions>     
            <InCare><xsl:value-of select="./fm:ExInCare" /></InCare>
            <SENprovision><xsl:value-of select="./fm:ExSENprovision" /></SENprovision>
           </TermlyExclusion>
        </Exclusions>
        </PupilOnRoll>
        </xsl:for-each>
       </PupilsOnRoll>
       </Pupils>
       </xsl:template>
      </xsl:stylesheet>

       

      However, when the exclusion data exports I don't get separate records.  All related records appear one after the other for each item eg.- 

       

      <StartDate>24/9/200916/10/200918/12/2009</StartDate>

       

      what I want is

       

      <StartDate>24/9/2009</StartDate>

      <StartDate>26/10/2009</StartDate>

      <StartDate>01/01/2010</StartDate>

       

      Does anyone know how to achieve this? Thanks.

       

      I'm on FM 10,

















        • 1. Re: XSLT related records
          comment_1

          It's rather difficult to follow without knowing which fields you exported from which table. I am guessing you want something like:

           

           

          <?xml version='1.0' encoding='UTF-8' ?>
          <xsl:stylesheet version='1.0'   
          xmlns:xsl='http://www.w3.org/1999/XSL/Transform'
          xmlns:fm="http://www.filemaker.com/fmpdsoresult"
          exclude-result-prefixes="fm">

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

          <xsl:template match="/">

          <Pupils>
            <PupilsOnRoll>
            <xsl:for-each select="fm:FMPDSORESULT/fm:ROW">
              <PupilOnRoll>
                <PupilIdentifiers>
                  <UPN><xsl:value-of select="fm:_UPN"/></UPN>
                  <UniqueLearnerNumber><xsl:value-of select="fm:ULN"/></UniqueLearnerNumber>
                  <FormerUPN><xsl:value-of select="fm:_formerUPN"/></FormerUPN>
                  <Surname><xsl:value-of select="fm:_surname"/></Surname>
                  <Forename><xsl:value-of select="fm:_forename"/></Forename>
                </PupilIdentifiers>
                
                <Exclusions>
                <xsl:for-each select="fm:_CategoryCode/fm:DATA">
                <xsl:variable name="pos" select="position()"/>
                
                <TermlyExclusion>
                  <Category><xsl:value-of select="."/></Category>
                  <Reason><xsl:value-of select="../../fm:_ReasonCode/fm:DATA[$pos]"/></Reason>
                  <StartDate><xsl:value-of select="../../fm:_StartDate/fm:DATA[$pos]"/></StartDate>
                  <Sessions><xsl:value-of select="../../fm:_NoSessions/fm:DATA[$pos]"/></Sessions>     
                  <InCare><xsl:value-of select="../../fm:ExInCare/fm:DATA[$pos]"/></InCare>
                  <SENprovision><xsl:value-of select="../../fm:ExSENprovision/fm:DATA[$pos]"/></SENprovision>
                </TermlyExclusion>
                </xsl:for-each>
                
                </Exclusions>
              </PupilOnRoll>
            </xsl:for-each>
            </PupilsOnRoll>
          </Pupils>
          </xsl:template>
          </xsl:stylesheet>

           

          In any case, this would be considerably easier if you exported from the child table.

           





















          • 2. Re: XSLT related records
            rrighini

            All of the fields within "pupil identifier" are from the "PupilDetails" table and all fields in "Exclusions"  are from the exclusions table.  Both tables are in the same database file.  Sorry I'm a complete novice with xslt.  I want to end up with :

             

            <PupilsOnRoll>

            <PupilOnRoll>

            <PupilIdentifiers>

            <UPN>L353700123456</UPN>

            <UniqueLearnerNumber>47123456</UniqueLearnerNumber>

            <FormerUPN/>

            <Surname>Smith</Surname>

            <Forename>John</Forename>

            <MiddleNames/>

            <PreferredSurname>Akram</PreferredSurname>

            <PupilIdentifiers>

             <Exclusions>
            - <TermlyExclusion>
              <Category>FIXD</Category>
              <Reason>PA</Reason>
              <StartDate>2009-10-01</StartDate>
              <Sessions>2</Sessions>
              <InCare>false</InCare>
              <SENprovision>S</SENprovision>
              </TermlyExclusion>

            - <TermlyExclusion>
              <Category>FIXD</Category>
              <Reason>PP</Reason>
              <StartDate>2010-02-04</StartDate>
              <Sessions>2</Sessions>
              <InCare>false</InCare>
              <SENprovision>S</SENprovision>
              </TermlyExclusion>
              </Exclusions>

            <PupilOnRoll>

             

            <PupilOnRoll>

            <PupilIdentifiers>

            <UPN>L987654321</UPN>

            <UniqueLearnerNumber>487654</UniqueLearnerNumber>

            <FormerUPN/>

            <Surname>Jones</Surname>

            <Forename>Billy</Forename>

            <MiddleNames>John</MiddleNames>

            <PreferredSurname>Jones</PreferredSurname>

            <PupilIdentifiers>

             <Exclusions>
            - <TermlyExclusion>
              <Category/>
              <Reason/>
              <StartDate/> 
              <Sessions/>

              <InCare/>
              <SENprovision/>
              </TermlyExclusion>

              </Exclusions>

            <PupilOnRoll>

             

            As not all pupils will have exclusions.  Some may have more than one.  If there is an easier way to set up my xslt that's fine, I just don't know how to do it.  Thank youo so much for your help.

             

             

             

             

             

             

             

             

             









            • 3. Re: XSLT related records
              comment_1

               


              BusyBabeReet wrote:

              As not all pupils will have exclusions. 


              OK, if you need to include all pupils then you'd better export from there - so just forget my closing remark.

               

              Did you try the suggested stylesheet?

               


              • 4. Re: XSLT related records
                rrighini

                Yes I have but it hasn't exported exclusion data. Here was my result.

                 

                <PupilOnRoll>

                <PupilIdentifiers>

                <UPN>P123456789</UPN>

                <UniqueLearnerNumber/>

                <FormerUPN/>

                <Surname/>

                <Forename>Billy</Forename>

                </PupilIdentifiers>

                <Exclusions/>

                </PupilOnRoll>

                <PupilOnRoll>

                 

                and this particular pupil should have 3 exclusion records showing here.

                 

                • 5. Re: XSLT related records
                  rrighini

                  Also, I'm exporting as a FM dso export.  Is that right??  I have to upload my file to the national school census so it has to be in a certain format.

                  • 6. Re: XSLT related records
                    comment_1

                    Could you post your raw XML? Just export one or two pupil records the same way as you do now, except without using any stylesheet.

                    • 7. Re: XSLT related records
                      rrighini

                      You mean like this??

                       
                       <?xml version="1.0" encoding="UTF-8" ?>
                      - <!--
                       This grammar has been deprecated - use FMPXMLRESULT instead 
                       --> <script type="text/javascript"></script>
                       <ERRORCODE>0</ERRORCODE>
                       <DATABASE>File P.fp7</DATABASE>
                       <LAYOUT>PupilInformation</LAYOUT>
                      -<ROW MODID="75" RECORDID="390">
                       <_UPN>T3521111111</_UPN>
                       <_forename>John</_forename>
                       <_surname>Smith</_surname>
                       <_StartDate/>
                       <_CategoryCode/>
                       <_ReasonCode/>
                       <_NoSessions/>
                       </ROW>
                      -<ROW MODID="77" RECORDID="410">
                       <_UPN>B35322222222</_UPN>
                       <_forename>Adam</_forename>
                       <_surname>Brown</_surname>
                       <_StartDate/>
                       <_CategoryCode/>
                       <_ReasonCode/>
                       <_NoSessions/>
                       </ROW>
                      -<ROW MODID="95" RECORDID="490">
                       <_UPN>P3523333333</_UPN>
                       <_forename>Chantelle</_forename>
                       <_surname>Green</_surname>
                      -<_StartDate>
                       <DATA>24/9/2009</DATA>
                       <DATA>16/10/2009</DATA>
                       <DATA>18/12/2009</DATA>
                       </_StartDate>
                      -<_CategoryCode>
                       <DATA>FIXD</DATA>
                       <DATA>FIXD</DATA>
                       <DATA>FIXD</DATA>
                       </_CategoryCode>
                      -<_ReasonCode>
                       <DATA>VA</DATA>
                       <DATA>VA</DATA>
                       <DATA>VA</DATA>
                       </_ReasonCode>
                      -<_NoSessions>
                       <DATA>2</DATA>
                       <DATA>6</DATA>
                       <DATA>2</DATA>
                       </_NoSessions>
                       </ROW>
                       </FMPDSORESULT>
                      • 8. Re: XSLT related records
                        comment_1

                        Actually I meant the unprocessed result of the export. Anyway, assuming it's reasonably close, and after cleaning it up a bit, this is what I get using the stylesheet I posted earlier:

                         

                         

                        <?xml version="1.0" encoding="UTF-8"?>
                        <Pupils>
                          <PupilsOnRoll>
                            <PupilOnRoll>
                              <PupilIdentifiers>
                                <UPN>T3521111111</UPN>
                                <UniqueLearnerNumber></UniqueLearnerNumber>
                                <FormerUPN></FormerUPN>
                                <Surname>Smith</Surname>
                                <Forename>John</Forename>
                              </PupilIdentifiers>
                              <Exclusions/>
                            </PupilOnRoll>
                            <PupilOnRoll>
                              <PupilIdentifiers>
                                <UPN>B35322222222</UPN>
                                <UniqueLearnerNumber></UniqueLearnerNumber>
                                <FormerUPN></FormerUPN>
                                <Surname>Brown</Surname>
                                <Forename>Adam</Forename>
                              </PupilIdentifiers>
                              <Exclusions/>
                            </PupilOnRoll>
                            <PupilOnRoll>
                              <PupilIdentifiers>
                                <UPN>P3523333333</UPN>
                                <UniqueLearnerNumber></UniqueLearnerNumber>
                                <FormerUPN></FormerUPN>
                                <Surname>Green</Surname>
                                <Forename>Chantelle</Forename>
                              </PupilIdentifiers>
                              <Exclusions>
                                <TermlyExclusion>
                                  <Category>FIXD</Category>
                                  <Reason>VA</Reason>
                                  <StartDate>24/9/2009</StartDate>
                                  <Sessions>2</Sessions>
                                  <InCare></InCare>
                                  <SENprovision></SENprovision>
                                </TermlyExclusion>
                                <TermlyExclusion>
                                  <Category>FIXD</Category>
                                  <Reason>VA</Reason>
                                  <StartDate>16/10/2009</StartDate>
                                  <Sessions>6</Sessions>
                                  <InCare></InCare>
                                  <SENprovision></SENprovision>
                                </TermlyExclusion>
                                <TermlyExclusion>
                                  <Category>FIXD</Category>
                                  <Reason>VA</Reason>
                                  <StartDate>18/12/2009</StartDate>
                                  <Sessions>2</Sessions>
                                  <InCare></InCare>
                                  <SENprovision></SENprovision>
                                </TermlyExclusion>
                              </Exclusions>
                            </PupilOnRoll>
                          </PupilsOnRoll>
                        </Pupils>

                         

                         

                        • 9. Re: XSLT related records
                          rrighini

                          Yes that's exactly what I'm after.  Sorry if I'm being a bit dim - don't understand XSLT.  Did you get that by using your example stylesheet above and should I use it with fm dso?  I REALLY appreciate your help - you're being an absolute star for me, I've been messing around with this for months.  Can't quite believe how quick I am now getting a solution.  I don't understand your solution one bit but are you saying it works fine?  If yes, I'll play around.

                          • 10. Re: XSLT related records
                            comment_1

                            I believe all you need to do is:

                             

                            1. Copy the code in message #2 above;

                            2. Paste it into a text editor and save it as "anyname.xsl";

                            3. When you export from Filemaker, specify "Use XSL style sheet" > "File" and select the file you saved in step 2.

                            • 11. Re: XSLT related records
                              rrighini

                              Many thanks.  You've been brill.