Print 
PDFUpdate 4 Sep 2012: Preliminary testing indicates that the “line swallowing bug” alluded to below has been fixed in FileMaker 12.
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
- Add a new “virtual_list” table in your solution, but don’t create any records yet.
- Define a number field, serialNumber, as an auto-enter serial number with an initial value of 1.
- Define a calculated text field called “paragraph” as
GetValue ( $$virtualList ; serialNumber )
…and set the storage type to unstored. - 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).
- 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.
Nice hack!
In the # preview to view section, the Find command might cause serial number to be indexed.
Because your virtual list table is sequentially numbered and unsorted, you could try this:
go to record [ $paragraphCount ]
omit multiple [9999999999]
I like the hoops you go through to work around the formatting bug. Definitely SFM™ territory (stupid filemaker tricks) :)
But the serial number DOES have to be indexed for many uses of virtual lists. Not sure why this is presented as a problem.
I’m a big fan of this technique!
Thanks for writing it up so well for people Kevin, it’s a pretty easy to do once you get the idea (I’m always thankful that Bruce pioneered this), but this will make it much easier than bootstrapping it.
It’s also worth noting that this same basic idea works well to compile and export large text files (like dynamic XML) — when the “CR/LR” vs. “CR” encoding for end of lines is being a problem.
In that use-case: Instead of printing you build your entire file’s text in a variable in memory, and then goto the Virtual List and export just the virtual list “field”/records as .tab delimited, and this places the correct “end of line” breaks on the file.
The nice thing about this method is your not moving the large chunks of text around much, so it’s FAST.
Great work all!
Thanks for that Jonathan. Funny you should mention the CR/LF issue, because the first time I wrote about Virtual List was just over a year ago, and it was for a similar reason: Exporting Data to iCal, Outlook, Google Calendar, etc.
Thank you! This is a real bug for me!
I’m trying to use this technique in FM12, and one thing is bugging me: when I have a blank line in the source text, the sliding layout swallows it, so for example:
“The line following this one is intentionally left blank.
This line is intentionally populated.”
Prints as:
“The line following this one is intentionally left blank.
This line is intentionally populated.”
Is there a solution to this problem?
Hi Michael,
I opened a copy of the long docs demo in FM 12, deleted all the existing text, pasted in your example text… and it correctly previews with the empty line. Did you try that, and if so, what was the result?
Regards,
Kevin
I can confirm that using your example file, the text displays correctly if I import the database into FM12.
However, when I replicate your solution in FMP12 from scratch, the sliding layout behaviour is different. Empty lines are compressed slightly, and multple empty lines look the same as one empty line.
I’ve been over the databases with a fine-toothed comb and I can’t see any discrepancies – maybe FM12 does have different sliding behaviour, but preserves the behaviour of imported FM11 files?
Here’s my attempt, if you want to take a look: https://dl.dropboxusercontent.com/u/77795925/FMP/Long%20Documents%20in%20FM%2012%20From%20Scratch.fmp12
Hi Michael,
The trick is to make all objects on the virtual_list layout 4 pts shorter than the body height itself. In your demo, the body height is 1682, so if you go into layout mode, do a select all, and set the height of the layout objects to 1678, you will then see the desired behavior in preview mode.
long documents in fm 12 from scratch, v2
Regards,
Kevin
Ah, okay, I see now. You did put that in the post, I just can’t read. Unfortunately it seems that using this technique the spacing only looks correct if you have a single empty line in between paragraphs. Any more or less and the spacing looks wrong. I guess there’s only so much that can be done to replicate the exact formatting using a workaround like this. Thanks for your help!
Hi Michael,
a) You’re welcome.
b) If your supposedly-empty row actually contained a single space, it would still appear to be an empty row but perhaps you’d have better results. I’m not suggesting polluting your original document, but when the “preview document” script runs, perhaps a bit of creative substitution would be in order? And in that case you might want to restore the objects on the layout back to their original heights.
Regards,
Kevin
You know I just had the exact same thought? It’s actually quite simple to add a space, since I’ve already got a calculation field that’s designed specifically for this usage, so I’m not worried about breaking other systems. I’ve changed the paragraph calc to:
Let ( [
a = GetValue ( $$virtualList ; Extend ( serialNumber ) ) ;
b = GetAsCSS ( a ) ;
c = Get ( CalculationRepetitionNumber )
] ;
Case (
c = 1 and IsEmpty ( a ) ; ” ” ;
c = 1 and PatternCount ( b ; “text-align: ” ) = 0 ; a ;
c = 2 and PatternCount ( b ; “text-align: center” ) > 0 ; a ;
c = 3 and PatternCount ( b ; “text-align: right” ) > 0 ; a ;
c = 4 and PatternCount ( b ; “text-align: full” ) > 0 ; a ;
“”
)
) // end let
So any empty lines are represented as spaces, and the spacing looks exactly right, regardless of the number of empty lines! Looks like this is going into the production version.
Here’s my fixed example:
https://dl.dropboxusercontent.com/u/77795925/FMP/Long%20Documents%20in%20FM%2012%20From%20Scratch%20-%20Line%20Spacing%20Fixed.fmp12