It’s always been easy to create a straightforward database in FileMaker Pro, and with the enhancements in FileMaker Pro 11, even a newbie can start entering data immediately. But it’s more challenging to figure out how to create useable printed reports. Here are a few tips that will help you design attractive and useful printed reports in FileMaker Pro. (Using Bento instead? We’ll cover Bento in a separate article.)
Input vs. output
If your FileMaker database is going to be used to print reports of any kind, you should create special layouts designed exclusively for printing. There are many reasons for this, and understanding them will help you design better print layouts.
For starters, computer screens are almost always wider than they are tall, but most users want printed reports in portrait orientation, that is, a regulation 8.5-by-11-inch document in the traditional upright position. You could simply design your data-input layouts to fit those dimensions, but that wastes a lot of horizontal space on screen. So, to make it all work efficiently, make your data-input layouts wide, but build your print layouts tall.
Second, data-input layouts usually have a variety of user-interface elements that you don’t want to reproduce on your printed output. Your print layout should get rid of buttons, organizational boxes, field shading, on-screen instructions or hints, and perhaps even field labels. You may want to place fields on a data-entry form quite differently from the way you want them to print on reports. For example, on the data-entry form, you might want to display separate fields for first, middle, and last names, but on a printed report, you may want to concatenate those fields into a single full name.
Third, certain fonts are better for viewing on screen than they are for printing, and vice versa. Verdana and Georgia, for example, are two fonts designed specifically to be viewed on screen. Such fonts usually do not look so good in print. I use Verdana a lot for onscreen display, but for reports, I use Helvetica Neue on the Mac or Arial, which is a good font for cross-platform viewing.
Special case 1: large text fields
There are two very special cases that definitely want their own layouts for printing: portals and large text fields.
Many of the fields in a database store values (content) that don’t exceed a certain length. The dollar (number) fields in my invoicing and banking databases, alas, don’t ever need to store very large values. Dates, social security numbers, state abbreviations and zip codes, and many other data types are basically fixed lengths or nearly so. The field on the layout can display the entire value.
But sometimes, we define fields without knowing whether they will store a little data or a lot. In an invoicing database, for example, a line item describing work that you’re invoicing for might typically be no longer than 30 to 40 words; but occasionally, it could be longer. And notes fields sometimes can be quite long. A text field in FileMaker Pro can store more data that it can normally display.
To print such a field, you build a print layout and make the text field larger than you imagine it ever needing to be. Then you define the field to shrink as necessary.
Special case 2: portals
A portal is simply a way of showing a list of related records. Portals are used display all kinds of things. If you are looking at an invoice, the portal might display related line items. If you are looking at an author record in a library database, the portal might list books by that author.
There’s no practical limit on the number of related records, but there is a limit on the number of rows that a portal can display without scrolling. For example, if you use the portal to display notes that are related to a personal record in a contacts database, you might define the portal to show 10 rows. If the notes are all brief, and you never have more than 10, then you could, in theory print the portal. But notes are unpredictable lengths, so an individual note might contain more text than can be displayed in the portal row. Moreover, you don’t always know in advance how many notes will be created for any given person, or how many line items an invoice will have.
The way to print the records displayed in a portal is to build a layout based on the child table that “owns” the records in the portal, and do your printing there. In other words, if you are printing an invoice, don’t print from a layout based on the INVOICES table, with the line items in the portal; instead, jump to a layout based on the child table, LINE ITEMS, and print from there. By doing so, your invoice can go on for as many pages as it needs to show all the line items, because in a layout based on line items, every line item is a separate record.
And what if some of the text fields in the child table contain values that are sometimes short and sometimes long? This might occur if you are printing related notes in a contacts database: some notes might be a word or two, others might be a couple of paragraphs. In this case, you can make the text field that contains note text large enough to accommodate the largest amount of data you expect to be entered in the field. If you have some long-winded note writers, you might want to make the note field very large indeed. Then configure the field to slide up when printing, using the Inspector, Position tab.
Building layouts
FileMaker’s new-layout wizard tries to make things easy, but I think layout design is too important to be done on automatic. I usually start with a blank layout and add fields myself, one by one.
If you are designing a data-entry layout, it doesn’t matter at all how your Page Setup dialog is configured, nor does it matter whether you specify page margins. But if you are designing a print layout, then these things matter very much.
If you know exactly what printer you’ll be using, you can configure the Page Setup dialog, and select your printer, then go into layout mode in FileMaker. You’ll see a squiggly line marking the right edge of the printable area; and if you drag the body part of the layout down far enough, you’ll see a horizontal squiggly line indicating where the page break will occur. You always want to stay within the line on the right; if you don’t, the printed page will cut off your content. As for the line on the bottom marking the page break, you can stretch a text field beyond that if you need to, but it’s risky. Lines might not break perfectly between pages.
Neither your word processor nor your spreadsheet program can compete with FileMaker Pro at handling structured data, but by the same token, FileMaker Pro can’t compete with your word processor if you need to print very large blocks of text. If you have fields that contain, say, essay-length (or longer) blocks of text, your best bet might be to export that data and merge it in Word.
Sliding left, sliding up
It’s not the latest dance step. In fact, it’s a feature that’s been in FileMaker Pro forever. I’ve already touched on sliding but it deserves another word or two.
When you place objects on a layout that you are going to print, you can configure them to slide left and/or slide up, to fill in blank spaces. For example, if you are printing an envelope, you might put these fields on the layout side by side: NamePrefix, NameFirst and Name Last. Now the name fields need to be long enough to accommodate long names like Ramandranath or Zeckendorfer-Johansen. But when the name fields print, you’d like the last name to move left to snug up neatly next to the first name, that is, you want this:
Ms Pam Zeckendorfer-Johansen
Not this:
Ms Pam Zeckendorfer-Johansen
To get the result you want, you select these fields in layout mode and use the Inspector to configure them to slide left. Be sure to leave a small horizontal space between the fields. The space won’t slide. If you don’t leave the space, you might get this result:
MsPamZeckendorfer-Johansen
You can also configure fields to slide up. This has two purposes. First, as I mentioned above, it’s useful if you have to place a large field on a layout to accommodate a potentially long block of text. Sliding up will cause the field to shrink vertically when there’s only a little text in it. Sliding up is also useful as a way to fill the space where fields are empty. For example, in the screen shot above of the labels layout, you’ll see that the fields Company and Position are on the layout. What happens when those fields are empty? You don’t want to see this:
Ms Pam Zeckendorfer-Johansen
12345 Utopia Tr
Nockwurst, TX 77909
To fill in the blanks, you configure the Company and Position fields to slide up. When those fields are empty, they basically just disappear in the printed report and you get the result you want:
Ms Pam Zeckendorfer-Johansen
12345 Utopia Tr
Nockwurst, TX 77909
Remember how I suggested above that you leave a little horizontal space between First Name and Last Name, so the names don’t bang up against one another? Well, when you are sliding up, you do not want to leave any vertical space. Be careful to place the fields on the layout so they are exactly contiguous. Otherwise, when they slide up, you may get slightly uneven spacing between the lines.
There’s a final option on the Inspector to mention: “Also resize enclosing part.” If this is checked, the layout part that contains your fields—that is usually the Body part—will also slide up from the bottom or shrink, when fields and other layout objects slide up. This is great for printing notes. A long note might take up nearly an entire page, but you’ll be able to fit a dozen very short notes on the next page if the body part is configured to resize when the note fields slide up. Do not check this box when you are printing a part that needs to stay a fixed size, for example, when you are printing envelopes or labels.
Print setup
If you don’t know what printer you’ll be using, minimum margins of 0.25 inches all around seem to work pretty reliably. After you’ve set up the page parameters, you might want to save them in a script step. This is especially useful if the page setup options are unusual. For example, printing envelopes probably requires different settings from those you use for normal full-page reports. In this case, it’s a good idea to save the envelope settings in a script. To encourage users to print using your script, put a button on the data-entry or browse layout where users are most likely to be when they decide to print. The script can be very simple. And at the end of the script, you can call a different script that puts the page setup settings back to normal (to minimize the chance of a full-page report coming out of the printer looking like it was supposed to be on an envelope).
If you are building a database that will be printing reports to many different printers, especially if you can’t predict what kind of printers those will be, it’s a good idea to add a step to your printing scripts that simply displays the Print Setup dialog just before each Print script step. You force users to hit Enter, but it’s worth it. Simply showing the Print Setup dialog causes the active printer to be recognized by FileMaker and thus finesses problems with page metrics.
Intermediate tip: Using a script to print to PDF
In any program on the Mac, of course, you can always click the PDF button in the Print dialog and create a pdf file instead of using paper. But FileMaker has the built-in ability to print to pdf, and it’s a very nifty feature. Here’s a very simple script that saves your report to the desktop, gives the pdf the name of the database file, and then opens the pdf in Preview or your favorite pdf viewer.
Set Variable [$pdfpath ; Value:Get ( DesktopPath ) & Get ( FileName )]
Set Error Capture [On]
Allow User Abort [Off]
Go to Layout [“ADDRESSES Print Labels” (ADDRESSES)]
Print Setup []
Save Records as PDF [“$pdfpath”; Automatically open; Records being browsed]
Go to Layout [original layout]
Note in particular the way that the path to the desktop and the name of the output pdf are set into a variable (here “$pdfpath”) and then used in the Save Records as PDF script step. This saves the user the need to deal with the save-as dialog at all. And the PDF will open automatically, so the user can review the document. If they like what they see, they can then print the old-fashioned way from Preview. Or they can send the report as an email attachment, or archive it.
In any case, regardless of whether you do it using FileMaker’s built-in Save as PDF feature, or whether you use the PDF button in the Mac OS X Print dialog, printing to PDF requires that you deal with the same layout issues that are discussed above for printing to paper.
Conclusion
Not every database needs to be printed. Many need only to store data and be searchable. Online reports sent to the computer screen may be sufficient. But if you need to print, FileMaker Pro gives you the tools for generating really spiffy reports. You just have to figure out how to use them!
[William Porter is a database applications developer and event photographer in Dallas.]
FileMaker Pro 11