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
- 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
Great hack. Thanks. This should be implemented in the FM core by now.
Interestingly I downloaded and opened your demo file in V11.01 to find it didn’t work. As suspected straight away the problem was US Letter vs A4 international paper sizes (and probably a ‘too old’ Filemaker version). As my own solution provides the ability to switch between paper sizes as preferences that are called on in scripts I will need to play with your solution to make it work for the rest of the world, not just the US, that is unless I’m missing something in my very quick review?
Hi Greg,
Thanks for taking the time to comment. Do you need a solution that works in 11? Because this problem got a lot easier to solve in 12 and later, when the line-swallowing bug was fixed. But either way (11 or 12/13), I think you could stick with a single layout and solve the problem by adding more overlapping calculated fields (or merge fields if FM 11), sizing and positioning them appropriately, and then ensuring that all are empty except for the correct one for whichever page size/orientation was chosen.
HTH,
Kevin
Hi Kevin,
Thanks for the response. I have to admit I have dedicated mere minutes on looking at this. Let me dig deeper and see how I could adapt your solution to the print setup issue. I am keen to include this functionality so I will keep you posted.
As for v13 I am most annoyed with FM for ruining vector transparency functionality – buttons etc. For me that is a killer. I want to get what I have right in 11 for now, skip 12 and move to 13 when FM gets this issue addressed.
Hello. I just ran into this article I am still digesting it, I am not that good using all functions available. I understand the article was posted years ago, nut I think something like it is what I am looking for: I have a survey data base with about 80 fields to be filled in. Some of the fields data are used to graph and some others for comments. I use a script to go through each survey question creating graphs and saving the comments in another layout that I called “Print to question 11” save the answers on a PDF. Then, on the same script I continue to question 12 all the way to question 28 which are saved on another layout called “print from q12”, save these answers as an appended PDF. The reason I do the appended PDF is because the layout doe s not let me go over 444 inches high. The resulting PDF most times comes out fine, meaning that there isn’t any large blank spaces between the end of the first PDF and the beginning of the appended on. But when the survey answers are a few or short, I begin to see a huge blank space between the two PDFs. I have tried everything to overcome that issue without much success and that is why I came across your article. But as you said, the hack works for a single long record. Any insight on what to do?
Hi Mauricio,
Perhaps a hybrid approach, where you combine the technique in this article with what you’re currently doing?
Best wishes,
Kevin
Hi Kevin,
This comment is ‘a bit’ late. It’s about the line swallowing. No idea if this is still of any use.
The line swallowing will not occur if the ‘page end line’, that thick slanted dotted line, is one or two pixels below a base line. The position of that line can be shifted by changing the size of the footer part. A book print should have headers and footers, so that is not a problem.
That is all.
Hi Martin,
Thank you for taking the time to comment. As per my note at the beginning of the article, this was fixed in FileMaker 12 and to the best of my knowledge is no longer a problem.
Regards,
Kevin