Using FMPXMLResult, how do I get the field position?

[El_Pablo]

Hi.

I'm trying to export data from an existing system to another one. I read somewhere that the FMPDSORESULT is deprecated so I want to use FMPXMLRESULT.

All fields in both system have different name. e.g. in the FM system the last name field is lastName and in the target system it is personLastName.

Since FMPXMLRESULT separate the structure from the data, how can I get the FM field name position from the structure?

...

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="lastName" TYPE="TEXT"/>

...

<COL><DATA>Smith</DATA></COL>

...

I would like to convert to the following format

<personLastName>Smith</personLastName>

So like I wrote I would like to get the position of lastName to be able to retrieve the right data column.

Thanks for any help

[comment]

Usually you would know the order in which fields are exported.

[El_Pablo]

I'm actually in development phase. So the field order is always changing while developing, modifying, testing, ...

In XSL, position() returns the current position, but I need something like "indexOf("lastName")".

[comment]

I'm actually in development phase.

So the field names will not be changing?

---

BTW, I wouldn't take FMPDSORESULT being deprecated too seriously; IIRC, it's been that way since v.7. OTOH, I might be proven wrong within the next 10 days...

Edited March 5, 2012 by comment

[El_Pablo]

No, the field names are already set in both system. The order of exportation could change because I'm creating a layout specially for this purpose. I just added a few fields for testing and after I get to know how it works, I will all the necessary fields (500+).

10 days... interesting :)

[comment]

You can calculate the ordinal number of the lastName field as =

count(fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD[@NAME='lastName']/preceding::fmp:FIELD)+1

[El_Pablo]

Thanks! I found a similar solution.

<xsl:key name="kValues"

    match="/fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD"

    use="following-sibling::fmp:FIELD/@NAME"

/>

 

  <!-- separate templates increase readability -->

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

<xfa:data xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/">

<form1>

    <subform>

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

	    <personLastName><xsl:value-of select="fmp:COL[count(key('kValues', 'lastName')) + 1]" /></personLastName>

...

	  </xsl:for-each>

    </subform>

  </form1>

[El_Pablo]

Question for an old hand!

Having a huge number of fields and knowing FMI deprecated FMPDSORESULT.

Would it be preferable to use FMPXMLRESULT over FMPDSORESULT for a new solution?

Thanks

[comment]

Do you mean: just in case FMI do discontinue FMPDSORESULT in the next version, will rewriting the XSLT styleshhet all I'll have to do to upgrade?

[El_Pablo]

I'm just concern that I don't want to restart over again, but as you wrote a lot of developer used FMPDSORESULT.

Thanks