FileMaker-to-InDesign Tips

FileMaker—> XML—> InDesign

Here are some detailed instructions on how to export from FileMaker Pro to InDesign via XML. Corrections or improvements are welcome!

The database used in this discussion is FileMaker Pro, but the technique works equally well with any database system that can create an XML file.

SETTING UP YOUR DATABASE

You will need a database of books or products with fields such as Title, Author, ISBN, Price, and other fields that you want to display in a catalog or brochure. Assuming you have a FileMaker Pro 7/8 database, create a new table linked one-to-one to your binding table by your primary ID. If you are using FileMaker 5/6, create a new database linked to you main database by primary ID.

Define fields in the new table or database like this:

YourRelationshipName::Title & "¶" [that is, the FileMaker Paragraph symbol in double-quotes] Thus, each field in the new database is simply the same as the fields in your main database PLUS a hard return. The hard return at the end of each field is to allow InDesign to recognize the element as a paragraph, to which you can then apply a paragraph style. It is wise to name these fields with identifiable names, such as Title, Author, etc., rather than cTitle or title_calc because these names will become your tags in XML and InDesign.

Import some records from your main table or database into your new table or database file. Now that you have them, choose File, Export. Select Files of Type .XML and give the exported file a name. Let’s call myexport.xml. When prompted, choose FMDSORESULT. You will want to export Title, Author, ISBN, and whatever else you want in your designed document, but of course not the ID if you have one. Export them in the order you wish them to appear in InDesign. (Note: If you open myexport.xml now with a text editor or XML parser, you should see XML tags that match your field names. There will be a problem in InDesign if you have HTML codes embedded in your text fields, but this is covered below.)

SETTING UP INDESIGN

Next, create a new document in InDesign and Define Styles whose names exactly match your exported fields: For example, Title, Author, ISBN, Price. Each of these should be a paragraph style, not a character style. For testing purposes, you might want to make your style differences really obvious and simple, such as 18 point Arial for Title and 10 point Roman for ISBN.

Next, in InDesign, choose File, Import XML. Be sure to check “Do not import whitespace.” You should now see the Structure window at the left, with the word FMDSORESULT at the top. Expanding this will show you the structure of your imported XML document. Click on the little arrow to the right of the word Structure above the structure window to get the drop-down menu, then choose Map Tags to Styles, then choose Map by Name. You should see each XML tag mapped to one of the style tags. Create a text box on your InDesign layout, then click and drag the XML Element called FMPDSORESULT into it. You should see the correctly formatted text flow into your textbox.

If so, you can delete the FMPDSORESULT from the Structure Window, then save the empty InDesign file and you now have a “template” into which you can import other XML files. Obviously, you can make as many “templates” as you like.

ITALIC AND BOLD STYLES

NOTE: With the advent of FileMaker 8, it is now possible to copy and paste text from a field in the database to other applications, such as Word, and preserve the text attributes, such as italic and bold. This means it is no longer necessary to hard-code <I> and <B> tags into text fields in order to export these styles to an HTML or other document. If you are using FileMaker 8 and are no longer storing Italic and Bold styles using HTML, you may need to purchase an inexpensive plug-in such as Jazz-HTML to make the following work, as it assumes you have characters such as HTML tags in your database fields.

How to deal with Italic or Bold styles within a text block:

If you are storing a sentence in your FileMaker database such as: Melville’s Moby Dick is a great book, it is possible to have the Italics appear correctly in InDesign. First, this method assumes you are using HTML tags in FileMaker to denote Italics. (I don’t know of any method to preserve italicized words in an export other than putting some kind of tags around them, but maybe there is one.)

In InDesign Define a Character Style (NOT paragraph style) in which the Tag known as I –that is, a capital letter I is defined as Italic in whatever font you are using for, say, the description.

Now the awkward part: When FMP exports to XML, any embedded HTML Italic or Bold tags are escaped out as &ltI>. (You’ll see this if you open the XML file with a text editor.) So, while the XML file is sitting on your hard drive and before you import it, you will need to search and replace to put the …’s back. (I have a Word Macro to handle this if you don’t feel like writing one yourself.) Once the Italic and/or Bold HTML is back in, the file is no longer valid XML, but InDesign does not seem to care.

Your FMP export script (once you write one) can then activate the Word Macro via the Send Message (or Applescript) step. Assuming you always name you export file the same, you can modify the macro so that you open the XML file, run the macro, then save it as plain text. You may or may not get a prompt from Word saying Are you sure you want to save this, but that’s the least of it. Just be sure to save it as PLAIN TEXT or it will not work.

Obviously, this is pretty clunky although once it is scripted you don’t notice. If someone has a better solution to this part, I would welcome it.

RELATED CHILD RECORDS IN FM PRO

If your FM database contains multiple child records for each book, for example containing ISBNs or type of descriptive copy (published reviews, etc.), this too can be accommodated.

Let’s say your published reviews are in a file in which each review is identified by a Title ID (to link it to a book) and a number that identifies it as a Review (and not an author bio, e.g.). Let’s say you used the ONIX code 08 for this purpose. In FileMaker 6 and earlier, you need a field in your new file that consists of Title ID & “08”, then link to your reviews file with this. Define a relationship from your FMP database to the file that contains the child records, such that you are relating on a field Title ID & Code Number. (If you are in FileMaker 7 or 8 you can just do a join on multiple fields, assuming you define a field that contains the value 08 for all records.)

Name this relationship “REVIEWS” so it will appear as such in the XML export and save your sanity.

You will also have to define one or more fields in your “reviews” database that puts the infamous hard return at the end of each field.

Simplest scenario: You didn’t bother with ONIX code 08 and your reviews file contains only reviews. Thus, youhave a file of books related to a file of reviews, such that one book can have many reviews. The name of the field in the review file is Review. You define a calc field in the reviews file (yes, yes, or table) called Review_return that equals the Review field plus a hard return. You define a relationship from your new exporter file to the review file just like the relationship from your main book file. Call the relationship REVIEWS

Now you can include in your export the external field REVIEWS::Review_return and, amazingly, you will get an XML file in which each book record has properly nested reviews, ready for matching to their style tags in InDesign.

Taking this further, you could have separate styles for the reviewer name, etc., but I have not tried this yet.

A DESIGNER’S PERSPECTIVE

Finally, with the kind permission of Priscilla Nickeson at Columbia University Press (for whom I initially wrote the Filemaker export routine), I’d like include her reply to a question I had on text flow, which she understands much better than I do:

“When you choose Import XML, a window comes up. In the lower left-hand corner are some radio buttons:

- Replace Content - Append Content - Import into Selected Item

“When you begin working with an old file that already has all the tags and styles mapped, it makes sense to Replace the old content and Save As with a new name.

“If you are importing several XML files one by one into a large document, you would use Append Content after the first time.

“If you click on one of the elements in the XML Structure window on the left side and click the Import into Selected Item option, the new copy will be placed at the end of the structure element you have selected.

“If you set up the document with linked text boxes, the copy flows into them automatically when it’s appended into one “chain”. As far as new pages being added on automatically when there is more text, according to InDesign Help:

“• You can thread text frames on a master, but only across a single spread. To automatically flow text across multiple spreads, thread text frames on the document pages instead.

“To flow an entire story automatically:

“With the loaded text icon displayed, hold down Shift as you do one of the following: • Click the loaded text icon in a column to create a frame the width of that column. InDesign creates new text frames and new document pages until all tex”t is added to the document.

• Click inside a text frame that is based on a master text frame. The text autoflows into the document page frame and generates new pages as needed, using the master frame’s attributes.

“If you hold down Shift+Alt (Windows) or Shift+Option (Mac OS), no new pages are added.” __________________________________________________________

“Researching this answer taught me alot! I’ve never been able to get the automatic-page-adding thing to work until now. You just hold down shift!

“I must say that when I append several XML files into one document, sometimes the copy shows up a second time at the end of another XML file ‘chain’. I think it’s because I get confused about dragging new XML text elements into the document when they are already appended to existing placed text. So, watch out for duplicated copy!”

Good luck, and feel free to contact me with questions.

Exporting XML to InDesign

Instruction in Rhymed Couplets by Bob Oeste

Define each field with a hard return
Export to XML, and turn…

To InDesign, where you’ll invent
A brand new empty document.

Make paragraph styles with names that match
And a textbox for your text to catch.

Then File, Import XML
Map tags to styles, you’re doing swell!

(You’ll find that in the Structure pane
It’s not that hard, so don’t complain!)

Now click and drag the structure right.
It flows in perfect, nice and tight.

But now you’ll find, to your chagrin
Italics didn’t pop right in.

So next define a character style
Assuming <I>’s are in your file

Write a macro that expands
And change gt’s to greater than’s.

When all is done, you’ll garner praise
Perhaps you’ll even get a raise.

But if you fail, you’ll hear them say,
“Start working on your resume.”

Bob Oeste Database Administrator/Programmer The Johns Hopkins University Press roeste@jhu.edu