FileMaker’s abilities are beyond your normal database. The hacks used by us developers extend it to great lengths. All these hacks of course are always fuelled by our clients’ imagination. I recently had a request for an assessment report based on FileMaker. The solution was quite simple but the report was quite lengthy and complex, which also included a watermark. The report was based on a sole table and the only relation was for some line items.
The report included the line items in several places and therefore basing it on the lines tables was out of the question. Instead to show the line items i used simple script to put the data into a few repetition fields, which worked like a charm and fairly fast compared to using FM’s calculation fields (which I hate!).
One further complexity was the use of page breaks at a few places to separate the sections. This was also easily resolved by having a Header & Footer, the body plus a few sub-summary parts with page breaks. I used the sub-summary parts with arbitrary sorting as it didn’t really make a any difference as I was forcing FM to show 1 record (assessment) at a time. Neat trick!
The Watermark Problem
But the whole complication started with a silly page border and an image watermark for the logo. Well the first one was the relic from using MS Word and its page borders. The fun part is explaining the client that “Hey, I can do the whole report but I cannot watermark your logo or put a page border” and well I hold it as principle of avoiding telling the client such stupid things. Back to the drawing board….or should I say Google!!
I did not find so many posts as I hoped and it appears a lot have given hope or keep the solutions away from the public eye. One solution I thought of as well and came across it online, was to put at least one pixel of the image on the header which would then fix it for the rest of the report. It works….but only for the first page! Don’t ask me why, I have zero clue.
So next thought was, well this problem could easily be solved by a plugin, which is probably a good case but I lack the programming skills in C/C++. Other plugins out there are ridiculously expensive just to use 2-3 features out their 500!! I want say names. I did however find a similar post (somewhere) that described the process of adding a stamp on a few thousand PDF quotations stored in a container.
The recipe requires a small cross-platform software called PDFtk which is also free-ish! The cool think about this is that it is a command line tool. Why is it cool? You can use the “Send Event” FM script step. Although I only tested this with my Mac version of FM and not Windows, on the Mac for some weird reason, you can use this step for every app except the Terminal!! So I used the Applescript script instead. The command takes 2 inputs and gives 1 output file as expected:
do shell script pdftk [input file] multibackground [stamp file] output [output file]
where [input file] is the path to a file that you have already saved the PDF export from FM. The [stamp file] is a PDF output of a simple Word document with your page border and logo watermark. The [output file] is just the path to the file you want created of course. Except for the “do shell script” which is Mac only, the rest applies the same to the Windows command prompt.
I would also add a cleaning step to delete the [input file] after use to avoid cluttering your drive or your server’s.
One final trick, the report you have created in FM, MUST have a transparent background colour. Think about what PDFtk does, it puts your report on top of the watermark pdf page which in turn must be replicated for each page of the input report, hence the use or “multibackground” in the command above. If you don’t make the background in FM as transparent then this won’t work. If you need a background colour for any reason then put this colour in the stamp file mask instead. And voila, problem solved!
All the client needs to do is to install PDFtk on their computer or installed on the server and that should be it. You should take into account the different Operating Systems as well. This solution will not work with FM Go locally stored database files.