How many times have you wanted to produce a report that showed *two different* lists of records? Or a couple pages of summary information, then a list of raw data? Or a title page, then a few pages of charts, then one list of data, then a few more charts, then a second list? In FileMaker, reports are tied to layouts, and a layout is tied to just one table. Of course you can just print several reports one after the other, but that doesn’t help if you want to *email* the report as a PDF, or store it on the file server.
Luckily, you can do all this (and more) with the new Append to PDF feature in FileMaker 9. Once you see it in action, you’ll agree it is very useful.
>Note: An expanded version of this tip will appear in the next issue of *Advisor Basics of FileMaker Pro*, a great magazine for folks learning FileMaker. The Advisor Basics version goes in to more detail, and covers page numbering and security considerations. You can subscribe [here](http://advisorstore.com/as-ztfmfu.html).
Here’s the deal: You operate a web retailer. As business grows, it becomes more and more important to get the right data in the hands of your employees, so they can make good decisions. You decide you want everybody to have a special report in their in box every monday morning. This report should show the week’s totals on the first page, then summary information about how many new customers ordered from you, broken down by region, a list of aggregate sales information for top selling products, and a list of every order that was returned.
Before FileMaker 9, this would have been a very difficult report to produce. Each individual part isn’t too hard to do: some web viewers, a sub-summary report or two, and a list report. But putting all these divergent parts together in on report is essentially impossible (at least without heroic hacks and a handful of headaches).
But with FileMaker Pro 9’s new Append to PDF feature, this is an absolute breeze. Here’s how.
## Build the Individual Parts
First, build a separate layout for each individual part. In this example, you might have one layout with web viewers displaying each chart. Another layout, based on the Customers table, would have a sub-summary parts for City and State. The aggregate product sales data would come from the Order Line Item table, again with sub-summary parts. Finally, you would build a layout showing a list of orders.
## Script the PDF
Once the layouts are in place, you can write a script that produces the final report. It might start off like this:
Go to Layout [Weekly Summary]
Set Field [Week Start, Get(CurrentDate)]
Save Records as PDF [Restore; No Dialog; “Weekly Report.pdf”]
This is *old school FileMaker 8.5* stuff here. You’re just saving the record (ie: layout) as a PDF file called `Weekly Report.pdf`.
Next, you want to export the new customer information. You add these steps to the script:
Go to Layout [New Customer Info]
**# find the new customers for this week**
Enter Find Mode []
Set Field [Created, “GetAsText(Get(CurrentDate) – 7) & … & GetAsText(Get(CurrentDate) – 1)”]
Perform Find []
**# and sort them for the sub-summaries
Sort [Restore; No Dialog]
At this point in the script, you’re ready to output the next section of the report. Just add the `Save Records as PDF` script step to your script, like you did before. If you look closely at the bottom of the Edit Script window, though, you’ll notice a new option. You can see it here:
To get this technique to work, you need to do two things:
1. Turn on the new “Append to existing PDF” check box
2. Click Specify next to “Specify an output file” and specify a PDF file that *already exists*.
If you do both these things, instead of *replacing* the PDF file when this step runs, FileMaker will add pages to the end of the PDF that is already there.
To complete the technique, add more steps to the script to visit additional layouts, perform the requisite finds and sorts, and append to the PDF.
## Emailing the Result
When you use this technique to build up a PDF, you perform the `Save Records as PDF` script step several times. If you turn on FileMaker’s “Create email with file as attachment” option (in the Specify Output File dialog box) every time, you’ll end up with several emails. Instead, just turn this option on in the *last* `Save Records as PDF`. Each step will add more to the PDF on your hard drive, but only the last one will email it to the users. Since the PDF was appended, the email will include the results of the entire script.
Using this technique, and FileMaker 9’s new Append to PDF feature, you can build electronic reports that are much more complex than was ever before possible. Thank you FileMaker 🙂
This was a helpful article and great feature. I’ve been creating multiple pdfs and then combining them with Acrobat. This will be soooo much faster.
I’ve read all the Filemaker 9 tips. Great job. My first time to this site and will definitely be back
This Append looks great but can it paginate across the entire set of PDF files. eg can you print layout A with pages 1 and 2, then layout B and have it be pages 3 and 4, using Append to PDF?
Or will it paginate as page 1, 2, 1, 2?
I want the page numbers in footers or headers
(and of course, would be great to have page 1 of x where x is the total pages. Too much to ask?)
@sharon: Thank you for your kind words. I’m glad it was helpful 🙂
@norman: You can probably accomplish what you want with a little fuss. Create an global field called
Page Startand an unstored calc field with this formula:Get(PageNumber) + Page Start. Then use the calc field in your footer, instead of the page number symbol (##). I have not tried this but I believe it will work. It’s worth a shot anyway.As always, if you are interested in a more detailed explanation, let me know and I’ll add it to my list of future articles.
Geoff
@norman: I’ve just posted an article about customizing the page numbers here.
One other thing, which isn’t quite so obvious until you start getting bad results, is the ability to print must be enabled in the users access level or check the “Run script with full access privileges” checkbox to allow anyone to use the function.
@Bart: You’re the man. Thanks for adding that. We definitely will add that to the post body… The really weird thing is that if you don’t have print privileges and you attempt to print or append to a pdf, FileMaker doesn’t throw the standard
Error Code 9 - Insufficient Privileges. It throws a much more obtuseError Code 9 - Command is Unavailable. Strange.This is a great feature. I have been doing this in Windows using command line code which works great with a little more scripting. The Append feature should allow me to clean up my scripts and, of course, use native features instead of external methods.
I just ran into some trouble with the Append feature though that you didn’t touch on here….
If you impose any kind of Security on the PDF such as not allowing printing or editing, the Append feature doesn’t work. It creates the PDF just fine, but when you try to append to it, you get the Error Code 9 – Insufficient Privileges error.
I tried not imposing any Security until the very last Append and, while the last append worked, it did not apply the Security settings.
For the most part, this is not a big deal to most people. However, I do work in the Healthcare industry and as such am concerned with HIPPA compliance.
Anyone have a work around or perhaps knowledge that I don’t.
@Matt: Coincidentally, I ran in to the exact same problem just last week. It took me a while to figure out so I wish you had posted earlier 🙂 I have not yet found a workaround.
It appears that I spoke too soon. I found that if you allow insertion, deleting, rotating of pages in the Security Options then you can append to the pdf.
It would be nice to be able to apply the security options to the pdf AFTER appending. That way I could just run the script that appends to the pdf without any security restrictions and then lock down the final version of the PDF.
I know I can do this will external apps, but it would nice to get rid of the external app from the equation.
Geoff,
YOur articles are GREAT! One question: In FMP 8 when you created a stand-alone version, the PDF functionality was lost (workaround was to print, then select as the printer ‘Adobe PDF’. Do you know if with 9 Advanced the PDF and Append PDF function are available when you create a stand-alone version?
THanks!
@stefan: I believe PDF creation is still MIA in the 9 runtime. My recollection is that this has to do with the PDF technology licensed by FMI. I’ve been told that their license doesn’t allow the kind of unlimited end user distribution the runtime allows, so they have to keep this feature out. I may be wrong though. Anybody know a different story?
Thanks for the example of appending pdf. I have a unique situation – a brochure (same for every one) + Certificate (varies for each person). I want to send these two togeter in one email as attachment.
When I append the certificate to brochure, the brochure file keep growing and keep appending new certificates at the end.. which is not a desirable outcome. I want to keep brochure file clean as orig and append certificate, and send that brochure+cert out keeping the brochure.pdf in its orig state. Can this be done with FM 9?
@raj:
I assume you’re sending your email in a loop. The key here is to turn the Append option *off* when you output the brochure layout, and then *on* when you output the certificate(s). This way, each time through the loop, the brochure is re-created, overwriting the existing one, and the certificate is then added to the new, fresh PDF.
If recreating the brochure every time isn’t appropriate (maybe it is slow?) then you’ll have to put something like the Troi File plug-in or AppleScript in the mix. Create the PDF once, then use the plug-in to copy it to a new location. You can then append to this copy, send the email, then delete it and make a fresh copy.
Hope this helps.
Geoff
This is great, thanks a lot. Allmost what I wanted. But I wonder if there is any way to use a value from a field to name the output PDF file.
Jan
@Jan:
Yes, absolutely there is. Any time FileMaker asks you for the path to an output file in a script, you can put a variable instead of a hard-coded path. First, you need to create a variable with the correct path. Something like this:
That puts a “file:” type path in the
$pathvariable. It will refer to a file on the user’s Desktop whose name comes from theMy Fieldfield.Then, in your
Save Records as PDF, when you specify the file, put$pathin the window instead of choosing a file.Hope this helps 🙂
Geoff
That is a great Article. I am working on a Instant Web Published Application. I have a question on saving the records as pdf. Is there any way to Save current record as PDF file with the web published application. I had gone through several articles, but could not get the solution for that.
Thanks for this article. Your tip with the path variable is exactly what I was needing and it saved me some time messing around in Filemaker to get it all working.
Warmest regards,
Colin
@Matt: I have a few users still using 8.0 so the append is not an option. Do you have the scripting for the append still?
Thanks,
Dave
Geoff’s note about the $path variable just pulled me out of a hole on Windows. I had a script with “Save Records as PDF” working fine with FMP 10 on Mac OS X 10.6.2, but it failed on Windows XP with the unhelpful “could not be created on this disk” error.
It turns out a variable with a full path needs no “filemac:” prefix on Mac OS X, but “filewin:” is required on Windows.
— Ward
Dear Geoff’s
How to Combine multiple reports PDF into one PDF ???
under FM9….
Thank’s
David