exporting summarized data

[comment]

Let me start by saying I know practically nothing about XML. I have tried to modify a few example XSL sheets I have found here, but failed miserably.

I want to export, to a text file, a report with sub-summaries. My db has 2 tables: Companies and Products. The report is from Products, grouped by Companies.

Ideally, I am looking for a generic XSL that I could modify (if needed), that would have the following parts:

[color:navy]

1. Header - information from globalHeader field;

A variable separator (let's say for now three carriage returns);

2. Sub-Summary (leading) - information from related fields in the Companies table, separated by another variable separator (let's say a single CR for now);

A third variable separator (let's say for now two CR's);

3. Body - information from fields in the Products table, separated by a fourth variable separator (let's say a tab) between fields, and a fifth variable separator (a single CR) between records;

A sixth variable separator (let's say for now two CR's);

4. Sub-Summary (trailing) - information from related fields in the Companies table, separated by a seventh variable separator (a single CR for now);

An eighth variable separator (let's say for now three carriage returns);

5. Footer - information from globalFooter field

Is this kind of generic XSL even possible? I can do this by looping between records and building a global text field, but it gets tiresome when minor changes are required to the structure (and I expect a lot of those).

[Fenton]

Yes, it's possible. Not a lot of fun perhaps. I'm not an XML expert either. So I cheat a little. I imagine there's some pure xml/xsl method to figure out dynamically which is the 1st record of what we'd call a Summary. But I know FileMaker much better. So I say, "mark the 1st record in a flag field in FM before exporting." This could be an auto-enter lookup or similar, or it could be inserted before exporting with a Loop.

Once you have a field that will be marked as the 1st record, then you can test for that in the xsl, to only output a line for that record, to be the summary line.

The Header and Footer are simpler, esp. if the data is in globals; though it does not have to be. You just produce lines for only the 1st Row.

The separators are whatever you put in between the data. Put as many as you like:

Tab: 

Return (Unicode)

As you see, the above is done in FMXMLRESULT format, and I'm just using the position of the columns (fields) to say which field I want.

The enclosed template does most of the above. It is done mostly in the "" style, rather than the "" XPath style; mostly 'cause that's the only example I had that was close to this. Either could be used. The template stylesheet I used explicitly removed the Metadata section however, which precludes getting the field names. Let me know if you need them and I'll figure out how (hopefully).

InvoiceSummary_XML.zip

Edited January 30, 2006 by Guest
Tabs and returns got whacked

[comment]

Thanks, I'll try to digest that (I'm aftaid it all looks quite Chinese to me at the moment).

I don't know what to do with the attachment, though: what is a .work file? I tried to export from my file using your SummaryTest.xsl, but that of course didn't work. But before you trouble yourself with that, let me ask this:

There is a file called subsummary.xsl that comes with FMP's examples, that ALMOST does what I need. The two problems I have with it are:

(a) it produces a HTML table, not text; and

( it only takes one field from the parent record.

As it happens, I need 3 fields from the parent in the subsummary leader, then a list of children. But it IS generic in the sense that it worked with my file with no mods to either.

Would you have a tip as to how to modify this file? (If it's not fun, then it's not that important - I do get by with a scripted global. But I believe such a mechanism would be tremendously useful for lotsa other stuff.)

[Fenton]

Sorry, as a forum regular I thought you might know that the .work file belonged to the excellent free TestXSLT application (which I often mention), which lets you see the result of your transformation before you try it with FileMaker (and gives much better error messages). It is not however a full XML editor (there are none native for Mac OS X, only Java ones, which are OK, as far as it goes)

http://www.entropy.ch/software/macosx/#testxslt

That's why I also included a FM XML export, as the .work file uses both the xml and the xsl file to show the result.

My xsl file is a modified version of that Summary example you were looking at; which was, as you saw, for HTML. Also, you specified more parts than it had. There's more than one way to get the data via an xsl file. What you see in my file is a way to extract data from a field in only the 1st record (a global field(s). You can add either more fields, or other "hard-coded" data into the parts.

Mine works only with an FMXMLRESULT export, not a FMDSORESULT export.

[comment]

Oh. I HAVE TestXSLT, but didn't know .work belonged to it. But I thought it cannot deal with FMP's raw XML export. Do I understand this correctly:

You exported FMXMLRESULT from FMP, without using a stylesheet, then loaded it into TestXSLT to construct a stylesheet? And the resulting stylesheet - if it works - can be then used to export directly from FMP?

[Fenton]

TestXSLT is a tool to let you see what an XSLT transformation will produce. That requires 2 pieces, an xml file and an xsl file. TestXSLT takes those 2 and shows you the result, as either text, or rendered as a web page, if that's what you're doing. It can hold all 3 in one file. And you can make make minor changes to the xsl, to test and see what it'll produce. It's easier than testing by exporting directly from FileMaker, and gives much better error messages when something is wrong.

TestXSLT is optimized to work with BBEdit or TextWrangler. So I usually am editing the xsl in BBEdit. When I save the file, the changes are automatically passed to TestXMLT, for retesting. BBEdit has almost no xml testing.*

What I did in this case was to export raw XML from FileMaker. That's what the InvoicesNew.xml file is, a raw FMXMLRESULT export, with no XSL file specified. I did this because I needed some data to work with, in order to see whether the XSL file, Summary.xsl, was actually working. I never get it right the first time -]

Once the xsl is working you can use it directly from FM, as the xsl choice during export. If, as in this case, the result is a text file, there is no ".xml" produced; just the final result. So, yes, you can just export directly from FM using the .xsl file. But I find these "in-between" files useful for troubleshooting.

*On my current machine, a new iMac with an Intel chip,** I cannot even run my old standby, the BBTidy extension with BBEdit, which was very convenient. Apparently is it too old. It is pretty annoying. The internal Tidy implementation in BBEdit is, IMHO, crap. It only does html (anyone feel free to call me an idiot and prove me wrong). I was forced to use a Java xml app to format the xml/xsl. Now, Java is OK. But since it has NO support for Mac OS Navigation services, I find it clunky to use.

BTW, many other old standbys don't work on this machine, MaxMenus, Fruit Menu, WindowShade, and my trusty Default Folder. All require updates. FileMaker itself runs just fine however. I have not even tried any plug-ins. Don't want to push my luck. Still running my old machine as well, for that reason.

**Long story. Basically I won a big discount in the year-end raffle at my local Mac User Group. It was a little embarrassing; because I wrote the FM file that did the raffle, and the membership, etc.. But not embarrassing enough to give it up. Hey, it was random :-] I swear!!

[comment]

I knew what TestXSLT does in general, just thought that Filemaker's XML was too esoteric for it to handle. I now see it does handle it, although it crashed on a particular combination or two. Which processor should I use, BTW?

I have made some progress meanwhile, and I feel like I am 60-70% there, just missing something very simple for the finishing touch. The great discovery: the export needs to be done from the parent file. Nothing in in the "regular" FM experience led me to expect this, but if you include related fields from the child in a XML export - you get ALL children records grouped with the parent data. The XML looks like this:

Each parent field gets a column on its own, while a child field has all the children data in a single column, separated by tags.

Now if I could somehow (a) treat separately the two kinds of columns; and ( re-group the child data back into their original records, I think the rest would be trivial.

P.S. You're complaining about "old stanbys" - I still rely quite massively on Classic...

Archive.zip

[Fenton]

Here's a rather convoluted solution. It could perhaps be done more compactly.

It uses the numerical position [1], etc., as well as "position()" and "last()" functions to test which column & data to get, and what to put after it. It also uses the element. There is also an complex element, which is a lot like FM: If, ElseIf, End If. That is, multiple tests, with an optional default result. is just one test (though it can have multiple parameters).

So the 1st 2 lines are the "header" of the ROW, then the related data of a column are tab-separated. Then the last column is return separated.

There is also a test for the last row, so it doesn't get extra returns afterwards. Yes, it's kind of crazy looking. One difference between what xsl can do and regular imports, like tabbed text, is that xsl can return the same value more than once. That's why sometimes you have to say what not to get, as well as what to get. Because if you match an element twice, you'll get it twice.

Archive_fej.zip

[comment]

Thanks again. The problems I see is not of it being "convoluted".

The first problem is it's hard coded. I need something more generic, i.e. instead of relying on a known position of

, it should look at the current structure. Then, if there are multiple

The second problem is that the child elements need to be pivoted (rows to columns), so that the final result is:

-- begin Company ---

APPLE COMPUTER

They make these fine products:

iMac..................all in one

miniMac...............a cheap computer

iPod..................portable toy

Apple is for the rest of us.

--- end of Company ---



-- begin Company ---

MICROSOFT CORP.

Microsoft makes software:

Windows...............Operating System

Outlook...............Personal Information Manager

Not recommended.

--- end of Company ---

I have no idea how this can be done - perhaps a loop?

[Fenton]

Here's another xsl file, which produces the above. You're not going to like it though, because it's even more hard-coded. It is not so much that xsl cannot be intelligent and generic. It's that it is more difficult to write that way, especially if you're not an expert.

There is a count() function. It would be possible to count the node you were in, the DATA in this case. Then make a decision how to process it. But in your case, and this would be common, you have 2 related fields following each other. Which is basically like a portal. What would be a bit trickier would to count the data of the descendent columns, to see it they were also related. I imagine it could be done however.

TextSummaryComplex.zip

[comment]

Yes, you're right - I don't like it. If I cannot have it generic, I'd rather do it inside FMP - at least I'll feel like I know what I am doing (relatively speaking...). I suppose I have bitten off more than I can chew here. I'll come back to this after I read up on XML/XSLT a bit. Thanks again.