Long Documents in FileMaker 11

Update 4 Sep 2012: Preliminary testing indicates that the “line swallowing bug” alluded to below has been fixed in FileMaker 12. Also, in FM 12, the maximum layout length and width have been increased to 444 inches (32,000 pt).

There was a time, many years ago, when the maximum number of characters you could store in a FileMaker text field was 64,000. With the introduction of FileMaker 7 in 2004, that limit was expanded to approximately one billion characters (2 Gb of data divided by 2 bytes per Unicode character), i.e., more than you or I will typically ever need.

But while the capacity of a text field expanded astronomically, the maximum length of a layout remained unchanged at 110 inches (ten 8.5 x 11 inch pages), leading to a dichotomous situation where FileMaker can store vastly more data in a field than it can easily preview, print or output to PDF.

For example, let’s say you’ve decided to build a data warehouse for public domain literary works. You can easily store the text of these works in a FileMaker field — one record per work — but how will you generate printed output? Overcoming this limitation is the topic of today’s article and demo file, long documents in FM 11.

To be clear: the 110 inch (≈ 10 page) limit applies to individual records, not print jobs. It’s an important distinction to grasp. Obviously FileMaker print jobs can, and frequently do, exceed 10 pages, but that’s because multiple records are involved.

So, we need to temporarily slice a monolithic block of text into separate records, and Bruce Robertson’s Virtual List technique is tailor-made for stunts like this. Of course one can instead:

1.  use a looping script to parse each paragraph into its own record in a utility table
2.  view the resulting found set as a list, and
3.  preview, print or save as PDF

…and originally I was going to do a two-part series, comparing these two methods, but when it came down to it, the Virtual List approach struck me as superior in almost every respect, and its performance is unbeatable. However, if you want to see the other approach, it’s used in my FM 11 Line Swallowing Bug demo (more on this bug later).

Below is the Relationships Graph for the Virtual List solution, and it couldn’t be much simpler. The document table will have one record per literary work, which will be stored in a single field: bodyText. The virtual_list table will contain two fields, and some large number of records (see step 4 below).

We’re going to implement the technique in two phases: 1) the basic recipe, and 2) a fix to work around the line swallowing bug, with several digressions in between.

The Basic Recipe

1. Add a new “virtual_list” table in your solution, but don’t create any records yet.
2. Define a number field, serialNumber, as an auto-enter serial number with an initial value of 1.
3. Define a calculated text field called “paragraph” as
   GetValue ( $$virtualList ; serialNumber )
   …and set the storage type to unstored.
4. Create “more records than you’ll ever need” in this table. If that sounds unhelpfully vague, why not start with 25,000? That’s enough to accommodate Moby Dick (which contains approximately 21,700 paragraphs).
5. Define the following script, and attach it to a button on your document layout.

In a nutshell, this script will…

• push the contents of bodyText into a $$virtualList variable
• note the number of paragraphs in a $paragraphCount variable
• go to the virtual_list layout
• reduce the found set to the number of paragraphs in $paragraphCount
• preview and then return the user to the starting layout

…and if it weren’t for that pesky line swallowing bug, we’d be ready to discuss the Virtual List layout, and then we’d be done. The good news is, there is a work around for this bug, and we’ll get to that in a minute, but first…

Interlude #1 (wherein bugs are identified)

Here’s an example of a different long document that has been processed via Virtual List in FileMaker 10 — note that the bottom line breaks cleanly:

Unfortunately, in FileMaker 11v1 and 11v2, the bottom line is partially chopped off:

With the release of the FileMaker 11v3 updater the partial-line-cutoff problem was resolved, but at the cost of a more insidious (i.e., harder to detect) bug — one that occasionally removes entire lines (and no, this was not fixed by the 11v4 updater):

The Virtual List Layout

Okay, we’ve seen Virtual List output in preview mode, but what does it look like in layout mode?

Shown here at 25% magnification, the layout is sized two pages high (in portrait orientation) to accommodate very long paragraphs — don’t laugh, Dr. Jekyll and Mr. Hyde contains several paragraphs that run to nearly one page in length.

The paragraph field is formatted as an “edit box” and set to slide and shrink up. Actually, what you see at right is an idealized depiction of how it should work (and indeed this would work just fine in FM 10).

At this point, savvy FileMaker developers may be saying to themselves, “I bet he doesn’t know that a merge field is the solution to this problem”… actually, I do and it isn’t. Or to be more precise, depending on your requirements, it may or may not be.

Interlude #2 (a discussion of relative merits)

It’s true that if you use a merge field in place of an edit box, the line swallowing bug will be quashed, but any manually-applied paragraph alignments will be lost. Here’s the original record in the document table. The text field is left-aligned, but the title and author lines have been centered:

Here’s the Virtual List edit box approach:

…and here is the Virtual List merge field approach:

So we have a situation where, on the one hand, the edit box “solution” occasionally swallows a line, and, on the other, the merge field “solution” displays all text as per its default alignment setting.

The Fix

Can we have the best of both worlds? It turns out we can with a little extra work. Did you notice how the merge field correctly renders character-level formatting? It’s only paragraph-level formatting that doesn’t carry over, i.e., left, center, right and full justification.

At first I was going to define four separate “paragraph” calcs in the virtual_list table, one for each possible alignment, and then precisely overlay them on the list layout… the first one left-aligned, the second one center-aligned, etc.

But four calcs seemed like overkill. Instead I decided to redefine the existing paragraph calc as an unstored repeating field with 4 reps.

Note the use of GetAsCSS to determine alignment. When text is centered it returns something like:

<SPAN STYLE= “text-align: center;” >BY ROBERT LOUIS STEVENSON</SPAN>

…while left-justified text returns:

<SPAN STYLE= “” >STORY OF THE DOOR</SPAN>

And if you’re wondering about the Get ( Calculation Repetition Number ) function, it helps ensure that each rep will display under different alignment conditions.

So, with the paragraph now defined as a 4-rep repeater, we can place merge fields corresponding to each rep on the list layout, properly aligned, like so:

…and then of course precisely overlay them:

Final Thoughts

a) Although the body part is sized as tall as two pages (in portrait mode), the merge fields should be three or four pixels shorter, to produce the correct amount of white space between paragraphs.

b) This seems like an awful lot of work doesn’t it? If you have an easier method that is 100% reliable, I hope you’ll let me know by posting a comment here.