export with summary label

[shaalan]

Hi, folks. I have a db that is a calendar of events. Events are sorted first by event date, then by category. I have those fields set as sub-summaries.

In the export, when I choose to group by those fields, and remove those fields from the export list, the fields don't appear in the exported file. No surprise. When I group by those fields and include those fields in the export list, those fields appear before each record in the exported file. I am hoping the resulting exported file would resemble the print preview in FMP... with the event date field as a header above all event records occuring on that date...

As an example, I am getting

Thursday, Jan 18

Entertainment

Event A

Thursday Jan 18

Entertainment

Event B

Instead of

Thursday Jan 18

Entertainment

Event A

Event B

I'm sure this is a well-known situation, but I searched and can't find a solution (that I understand!) I'll be grateful for any help. Thanks!

[dazlunn]

Hi, not sure if this method qualifies as 'FM Best Practice' but this is how I do it...

Include your event date field in the subsummary part. You also need to include a date field calculation, based on your event date, in the body part. Group by the first field when exporting.

The two date fields will look something like this:

The first date is your existing one..

Events::EventDate [Date]...

The second field is created in the same table..

Events::cEventDate_DIVIDER [Calc]... If (Getnthrecord(Events::EventDate), Get(RecordNumber)-1 = Events::EventDate, "", Events::EventDate)

This lets a record look at the previous record to see if the date is the same, if it is then it returns "" (nothing), otherwise it displays the date when it's the first record in the list. (according to the sort specified!)

I use this mostly in portals but have used it a couple of times in subsummary reports.. you may need to tweak it slightly for your particular solution.

Sorry if the explanation was not clear?!

[Fenton]

Here's another method, using Export XML, with an XSL stylesheet. The trouble with the regular FileMaker Export with summarized data is, as you saw, it only exports the "grouped" by data once, in any given group. It does export the tab between the fields however. Whether that's OK depends on what you're doing with the data, but it doesn't look right if you're just outputting to visual text.

The example uses xsl to only export the fields that have actual data in them. It puts a line feed in between those fields.

A second script does the same, but exports an extra line in between the records.

Export_Summary_Events.zip

Edited January 15, 2007 by Guest

[shaalan]

Thanks for the speedy responses... I am using FMP7 and don't think it features the getNthrecord function. Could there be an alternative calculation to achieve the same goal? I am exporting to xml for import into InDesign and am not sure if I can swing that with an xsl style sheet... As it is, I found I had to ad fields specifically to create line breaks between other fields in order for the InDesign tags to work correctly. (As noted in my profile: NOVICE)

I'm so close, and this is going to knock hours off my weekly workflow.

[dazlunn]

Sorry! It's been a while since I used 7...

I think you have the Get(RecordNumber) function though if memory serves me right?? (which doesn't happen as often as one would like!)

Use a regular date field with auto-enter calculation that replaces any existing value. You can still call this field Events::cEvents_DIVIDER

Calc goes something like this:

If(Get(RecordNumber)=1, Events::EventDate, "")

Warning: I haven't tried this but I don't see why it won't work.. please let me know how you got on with it..

p.s. If you haven't got this function in 7, then I apologise again...

[comment]

I don't see why it won't work..

It may work for the first record in the found set - but it won't work for the next record that is first in its group. You would need a scripted replace in version 7 to use this method.

I think XML/XSL is the better-fitting solution to use here, despite it's initial complexity.

[shaalan]

And that is the problem... Getting a single header for all the records with a particular event date. Currently, I am exporting to XML, without grouping. I then import into inDesign and manually remove the date label from all but the first record in each date.

I just barely figured out how to do this. XSL didn't seem to work for me in InDesign. A scripted replace seems like it might be a good idea (and possibly within my abilities...)

[comment]

Your first post indicated that you wanted a TEXT result. If you want XML, it may be somewhat easier - but we would need to know the exact format that InDesign expects.

XSL didn't seem to work for me in InDesign.

That seems to be a misunderstanding of the process. You'd use XSL for your export from Filemaker. The result can be XML (reformatted to fit InDesign), or text or HTML - all that is set in the XSL document. Then you would import the result directly into InDesign, without any intermediate manipulation.

[shaalan]

Sorry for the miscommunication... And yes, there is a misunderstanding of the process! I'm not sure what you mean by the"exact format that InDesign expects." Indd documentation doesn't specify anything... are you refering to the tags, stylesheets and placeholder frames I've created in the document? (Please excuse my ignorance... I really am just feeling my way through this...) I'm afraid I don't understand xsl... It seems I have to create an xsl style sheet file before exporting from FMP? Maybe I need to look research this option a bit more.

[comment]

It seems I have to create an xsl style sheet file before exporting from FMP?

Yes, that is correct. When you export as XML, you have the option to 'Use XSL style sheet'. The style sheet transforms the default Filemaker XML format to whatever you want. This happens DURING the export (i.e. you only get the final, transformed, result out of Filemaker).

I cannot answer your other question. I understand you want to get the data from Filemaker into InDesign, but I have no idea in what format the data needs to be before InDesign will accept it. That is the missing piece here, and you have it.

Have you tried Fenton's attachment?

[shaalan]

Ok. So I guess the million dollar question is how and with what do I create an xsl style sheet? (Feel free to laugh me off this board at any time.)

I tried Fenton's attachment (and was quite honored that he took the time—thank you, Fenton) and while it created a beautiful txt file, I couldn't seem to import it into indd.

[comment]

how and with what do I create an xsl style sheet? (Feel free to laugh me off this board

Don't flatter yourself - you're not that funny...

You would usually write your style sheet in a plain text editor, such as TextWrangler (free) or BBEdit. It's not an easy thing to learn, but there are are some nice examples included with FMP documentation (and a lot more on this forum) that you can use as a starting point.

Another application called TestXSLT (also free) is very handy for testing your style sheet.

while it created a beautiful txt file, I couldn't seem to import it into indd.

I guess I would call this the million dollar question. After all, it makes no sense asking how do transform Filemaker's data into something, when we don't know what that something is. I don't know anything about InDesign, so you need to tell us what the resulting file should look like.

[shaalan]

The resulting file is so close to what it should look like... Each field from the fmp data is mapped to an existing Indd style sheet. It takes about 3 or 4 clicks to format the entire file (several hundred lines of text) with the proper font, weight, alignment, spacing etc. (I'd attach a screen shot, but I don't think it's allowed)

The single error is that I can only export one record per event date (if I group on export), or every event gets the event date "summary" header.

So, before I immerse myself in the study of writing xsl styles, let me just ask: Is it your opinion that an xsl style sheet would allow me to export all found records under the summary header when sorted by the summary field?

[Fenton]

I was gone all day, just got back. Yes, it's a little confusing when you first see how xml using an xsl stylesheet works; the 2 files together* are used to produce a 3rd document. Xml is a markup language which can contain "structured tagged data." Xsl is also xml, but a special kind that can transform the xml data file into just about anything logical you want. Think of xsl as an intelligent "filter," which can also rearrange and even add data.

The result can, as comment says, be text, html or xml. If you're feeding the data to another application that can import xml, then xml is likely the best way to go.

The exact structure that InDesign wants for import is I'm sure quite different from what FileMaker wants for Import XML. I don't know what it is either; probably has special tags for specifying all kinds of things; and likely its own "namespace." I do know that many people do this, for publishing catalogs, etc..

I'm afraid I also don't know exactly what an InDesign "stylesheet" is. I believe the publishing apps began using that term before xml existed, so it may be something entirely different from an xsl stylesheet (which is defined by W3C).

*When you export xml from FileMaker using an xsl stylesheet you don't see the raw xml exported, all you see is the result after the xsl has transformed it. Do an export without the xsl to see what it looks like.

In answer to your specific question: Yes, you always export all found records. That's what my example file did. But I wrote it to produce exactly what you'd asked for in your 1st post. Every xsl stylesheet is written to produce an specified result; we can't write it until we know exactly what the result should be. You can also export with a result of html, if that helps; it has "formatting" tags. But I believe InDesign has specific xml tags for these things; though I don't know. I'm a data person, not a publishing one.