xslt for QuickBooks xml

[DanBrill]

Hi,

I want to import some xml into FM via an xslt. I've done this lots of times, but setting up this particular xslt is giving me fits. Can anyone help me out? What I want is for each DataRow to be a record. Each ColData should be a particular field.

When I perform the import using the xslt that I've got, it imports the right number of records, but all of the fields are empty.

I think the problem is in the way each ColData element has a colID value. I've never seen it set up this way before, and that's probably where I'm going wrong. How do I get each of these to be a field? How do I deal with the fact that each row doesn't always have a value for all of the columns?

So I'm attaching Journal.xml, which is what I want to import. Journal.xslt is where the problem is. Any suggestions?

Thanks,

Dan

Journal.zip

[Fenton]

I'm sure you know more xml than I. But I also like to give myself fits, so I tried. I was able to get either the data, or the rows (which you got); but not both at the same time. It wouldn't work simply by nesting the xsl:for-each. I'm sure they could be combined, but I couldn't quite get it.

<xsl:for-each select="QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow/ColData">

<COL><DATA>

<xsl:value-of select="@colID"/>

</DATA></COL>

<COL><DATA>

<xsl:value-of select="@value"/>

</DATA></COL>

</xsl:for-each>

There are also 2 elements SubTotalRow and TotalRow which may need to be taken into account, maybe -|

Needless to say, I didn't do this in FileMaker, which wouldn't have accepted the result of the above alone. But another parser would show the data. I used the free TestXSLT, Mac OS X.

[DanBrill]

Hey Fenton,

Thanks. This actually helps a lot. I'm sure I don't know more xml than you do since some of the syntax you are showing is new to me, and clears up quite a bit. I don't even have a parser yet -- just using notepad to create the stuff and testing by attempting the import into FM. But I'm downloading one as I type, so that may help too.

I think my new year's resolution is to get good at xml, since this does seem to be the wave of the future.

The other 2 elements shouldn't matter in this case. I plan to do the totaling within FM.

Know any good books on xml?

Thanks again,

Dan

[Fenton]

@attribute name gets the specified attribute at that node

I don't know why the nested xsl:for-each returns nothing. I've seen it work other places, so there's something else going on.

Filemaker Pro 6 Developer's Guide to Xml/Xsl by Beverley Voth

This is the most comprehensive book that is specifically for FileMaker; required reading.

Advanced FileMaker Pro 6 Web Development by Bob Bowers, et al

Has a large section on XML. It is less comprehensive, but good also, because he uses a slightly different approach in his syntax and examples from Beverly's.

There is also much material on web publishing in general; as there is a fair amount of material on it in Beverly's book also. There is some overlap, but different approaches, hence both are worth having.

Definitive XSLT and XPath by G. Ken Holman

I think it's going to be useful; I'm trying to read it, a few pages at a time. Any books on straight XML, XSLT are like learning a well-organized alien language; at least to non-programmers like myself.

http://www.w3.org/

The basics for free.

[DanBrill]

Thanks, man. I've found a way to spend that Barnes and Noble gift certificate I got for Christmas.

Still can't get it to work though. Any other xml whizes out there? (Chistian Jaeger lurking out there perhaps?)

Thanks,

Dan

[Fenton]

This gets the detail data.

<xsl:for-each select="./ColData">

<COL>

<DATA>

<xsl:value-of select="@value" />

</DATA>

</COL>

</xsl:for-each>

The only change from my previous post is that I used a "relative" XPath reference, rather than the full one. I don't quite understand why the former didn't work.

I'm including the FileMaker result. There's still more work however; because basically the entries don't line up. What needs to happen is to match the ColID attribute in the field element.

I believe there's 2 ways to do this. One would be the "experts" method, using XSL conditional tests; over my head at the moment.

The other would be to hard code the 26 IDs into the XSL; rather than using the simple xsl:for-each (well it seems simple now that I got it to work -)

You need to output a Tab when there's no match.

[Fenton]

Oops. Forgot to attach the FileMaker file. Not that's it a whole lot of help; and you could just create your own from the XML/XSL. It shows that the fields don't match the data correctly. Great help, huh -?

Journal.zip

[Fenton]

Another useful program. I use MacTidy, but HTML Tidy is available for all platforms.

It can test and indent your XML. It doesn't however do actual transformations. Good error message output however (if such can be good).

http://sourceforge.net/projects/tidy

[Fenton]

Actually that page is just too geeky, source code stuff. This one's better:

http://tidy.sourceforge.net/

[Fenton]

A couple more links of Tidy versions for Macs:

http://www.balthisar.com/tidy

MacTidy:

http://www.geocities.com/SiliconValley/1057/tidy.html

[Fenton]

I wrote: You need to output a Tab when there's no match

Dumb. I was thinking of exporting via xml to text. Tabs have no meaning in xml imports I don't think. We have obviously reached the end of my useful knowlege.

[DanBrill]

Hey again,

Thanks so much for all of your help. I am getting closer. Here's my best and current, though still not right, version

  

                            <RESULTSET>

				<xsl:attribute name="FOUND">

					<xsl:value-of select="count(QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow)"/>

				</xsl:attribute>

				

				<xsl:for-each select="QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow">

					<ROW MODID="1" RECORDID="1">

						<xsl:attribute name="RECORDID">

							<xsl:value-of select="position()"/>

						</xsl:attribute>

						<COL><DATA>

							<xsl:value-of select="ColData/@colID"/>

						</DATA></COL>

						<COL><DATA> 

							<xsl:value-of select="ColData/@value"/>

						</DATA></COL>

					</ROW>

				</xsl:for-each>

			</RESULTSET>

This returns the right number of records with data, but only for one variable. Well actually 2. The first data that's returned is the value of colID. The second is the value that it holds.

I was thinking along the same track as you: there's probably an elegant way to do this that I won't figure out. So I should hard code the 26 fields that I need.

But I'm not even sure how to do that. Any suggestions?

Dan

[DanBrill]

Oooh, I'm getting close... I've got the nested loop thing worked out. But I still don't know how to make it "match up" fields so that if one row has values for columns 1, 3, 5, and 7 it matches them to fields 1, 3, 5, and 7 rather than 1, 2, 3, and 4. How do I make it return a blank value if a row doesn't contain a specific column?

			<RESULTSET>

				<xsl:attribute name="FOUND">

					<xsl:value-of select="count(QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow)"/>

				</xsl:attribute>

				<xsl:for-each select="QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow">

					<ROW MODID="1" RECORDID="1">

						<xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>

						<xsl:for-each select="./ColData">

							<COL><DATA> 

								<xsl:value-of select="@value"/>

							</DATA></COL>

						</xsl:for-each>

					</ROW>

				</xsl:for-each>

			</RESULTSET>

Thanks,

Dan

[Fenton]

I tried several esoteric (to me) conditionals, such as If, and If, When, Otherwise. So far I find that I can come up with:

No rows

A few rows (the number that have entries), but no data

The rows with data, but only the same number as above

A lot of rows, some empty, some with the data. Unfortunately way more than needed, 91. Obviously it's processing some rows twice.

In almost all cases the rows were hard-coded, ie. test="@colID=1", test="@colID=2", ...25

What we are trying to ask for is:

If colID=1, value of @value; else just the <Col><Data /></Col> tags (it converts empty Data tag pairs to <Data />, for efficiency I guess).

The problem is stopping it from evaluating the same relative location twice; but evaluating it once. This is probably not difficult, if you understand the mechanism well (obviously I don't).

IMHO, it was kind of lame of them to put all the data into attributes. But anyone who knows this stuff could solve it easily. Unfortunately they are not here today, or probably tomorrow.

[DanBrill]

Greetings,

During this weekend's Forum outage, Fenton went above and beyond the call of duty, professional courtesy, and friendship, and got this problem solved. We both scratched our heads, tried all kinds of code we'd never messed with before, and found ourselves on the same track. But he got the solution all on his own, and I am deeply indebted to him. The cool thing is that we both learned a lot about xml in the process. That's one reason I love this Forum so much. There's always more to learn, and there are some really wonderful people out there who give so much to help others. I'll never be able to answer as many questions as I've asked (and almost always had answered) here.

For those of you scoring at home, here's the key:

 <RESULTSET>

        <xsl:attribute name="FOUND">

          <xsl:value-of select="count(QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow)" />

        </xsl:attribute>

        <xsl:for-each select="QBXML/QBXMLMsgsRs/GeneralDetailReportQueryRs/ReportRet/ReportData/DataRow">

          <ROW MODID="1" RECORDID="1">

            <xsl:attribute name="RECORDID">

              <xsl:value-of select="position()" />

            </xsl:attribute>

            <COL>

              <DATA>

                <xsl:value-of select="./ColData/@value[../@colID=1]" />

              </DATA>

            </COL>

            <COL>

              <DATA>

                <xsl:value-of select="./ColData/@value[../@colID=2]" />

              </DATA>

            </COL>

            <COL>

              <DATA>

                <xsl:value-of select="./ColData/@value[../@colID=3]" />

              </DATA>

            </COL>





etc., for all 25 fields....

<xsl:value-of select="./ColData/@value[../@colID=1]" /> is the crucial bit.

The complete code is in Journal.xslt

I'm attaching 4 files.

InputJournal.xml is the request that is sent to QuickBooks. (You can use the FileBooks Link to send this xml request to QuickBooks. I actually use a command line processor that I wrote myself to do the job. Drop me a line for details on this.) It requests 25 variables from QuickBooks. There are actually a lot more that can be retrieved. See the QuickBooks SDK Concepts Manual v 2.0, pp 68 & ff for the full list. You can get the SDK from http://developer.intuit.com/

Journal.xml is the data that is returned by QuickBooks.

Journal.xslt is the translator that allows Journal.xml to be imported into FM. All credit to Fenton on this baby.

Test.fp5 is the data. It is essentially a traditional accountant's ledger with debits and credits.

The great advantage to pulling in the data through the Journal report is that it is not dependant on the transaction type. Checks, deposits, invoices, bills, and all other transactions are immediately combined in 1 table. Unfortunately, the way the xml is returned, the transaction number is only returned on the first entry of a transaction. So my job now is to write a little script that will set the transaction number for the subsequent lines of the transactions. No big deal. A relationship can then be created on the transaction numbers so that all enries of a transaction are related. The same can be done on the Name, so that all of an entities transactions can be summarized in a portal. You get the idea.

Thanks again to Fenton. I hope this can be of help to anyone else trying to get data into and out of QuickBooks, or to learn xml and xslt.

Happy New Year to all,

Dan

test.zip

[RyanESmith7]

I just ran across this and I'm wondering if it is relevant 14 years later. LOL

[comment]

1 hour ago, RyanESmith7 said:

I'm wondering if it is relevant 14 years later.

It's a long thread, and it would be helpful to pinpoint "this" to something more specific.

In general, very little has changed since Filemaker first implemented XML import/export in version 6, so I would expect an old XSLT stylesheet to work just as well with the current version of FMP.

Of course, this depends on the source XML schema remaining unchanged as well.

 

 

Edited April 14, 2017 by comment

[RyanESmith7]

Thanks, comment. I'm trying to import/process/parse some similar XML data from QB and this whole thread has been very enlightening. I've been investigating on how to parse this XML data for far too long.

[RyanESmith7]

The specific part is if this still applies to QBXML or has Intuit changed QuickBooks' XML format over the years

[comment]

That's not a Filemaker-related question. I suggest you start a new thread and post what you have so far: the source XML, your current stylesheet, and - no less importantly - the target field mapping.