Introduction – Extending FileMaker with JavaScript
Using a combination of AngularJS, HTML5, CSS, WKHTMLTOPDF, PDFtk, bBox and FileMaker scripting, we can create PDF reports from a FileMaker solution, taking advantage of the design and development flexibility afforded by using JavaScript and web tools to accomplish the task.
Advantages of JavaScript + FileMaker
There comes a time when you’re asked for a report that’s difficult or impossible to create in native FileMaker. Examples include printing rows of data from multiple related tables on a single report, or cross-tab reports. While we have Virtual List techniques to help address these challenges, you may want to reach into the web development toolkit, and pull out a JavaScript-driven technique.
Advantages to this approach:
- Design flexibility: JavaScript and web tools allow you to build a design that can easily adapt to varying numbers of child record/portal rows.
- Version control: Git and github especially are essential tools for managing text-based code, such as HTML, CSS and JavaScript. This allows a developer to build new features, or simply experiment, without fear of breaking existing functionality. While version control is not particularly great for managing FileMaker files, it is indispensable for managing text-based code.
- Separation of data and display: Building reports by integrating web technologies allows for true separation of the data in your FileMaker solution from its display.
- Portability: The approach represented here, in the example file, is fairly modular and can be adapted to any style of report. Most of the FileMaker scripts require minor modification and the HTML/CSS/JavaScript can be adapted to fit your use case.
Example PDF Report
Here is an example report that would be challenging to execute on a typical FileMaker layout:
In this report, the parent record is a Project and the Overview is displaying fields from that record. So far, so good. This is quite doable in FileMaker. The Details section, however, displays child records and grandchild records of the parent. You could use portals to solve this in FileMaker, however you would run into trouble fairly quickly when the number of records exceeds the number of portal rows supplied on the layout. In addition, you can run into issues with page breaks. Portals on print layouts are not built for this type of flexible display. Using HTML/CSS/JavaScript, however, you can display any number of rows, as well as flexibly and programmatically break the page where required.
Developer Requirements
The code needed to generate web-sourced PDF reports from FileMaker is designed to be straightforward and readable. If you can read the code, you will be able to leverage existing solutions to create new reports. The web skills required are fairly modest for this technique. It will help if you have intermediate-level skills in web development and an understanding of web development workflow. That said, the sample file provided goes a long way towards getting you started. If you want to learn web development and JavaScript integration with FileMaker, then this might be a good way to start.
Here are the bits you should have at least an intermediate-level handle on:
- Front end web languages, HTML and CSS: Just enough to be dangerous, as they say. You should be able to build a simple HTML doc and style it with CSS, or at least be able to read these documents and understand what they do. If you can read and modify existing code, then you are well on your way.
- JavaScript: A basic understanding of JS is really all you need. You ought to know what JavaScript variables, objects, properties and arrays look like and how they behave. You should have an understanding of how to structure data using JavaScript Object Notation (JSON).
- AngularJS: Angular is a structural framework for creating dynamic web apps. For our purposes, we only need a tiny slice of Angular – just the bit where you can use Angular’s templating directives to access and loop over data and inject it into an HTML document.
- Text editor: You ought to know your way around one of the professional text editors. Sublime Text 3 is my tool of choice, but you could go for one of the open source text editors such as Brackets or Atom. If you are into self-inflicted pain, try Vim. It doesn’t matter which one you choose. Pick one and stick with it.
- Command Line: You don’t need to be a black belt ninja at the command line to build a PDF reporting workflow into your solution. You should be able to navigate through your directory structure and understand how to issue basic commands. Start learning how to use the command line!
- Perform Script on Server (PSOS): FileMaker’s PSOS script step is used in the sample file, so read up on it if you have not previously used it in development. PSOS is not necessarily required for the technique, but can improve performance of the PDF generation, as well as other FileMaker scripts.
Having a handle on the above resources will get you most of the way there in understanding how to work with this technique. If you want to work more effectively and quickly to adapt the code in the sample file to other solutions, then jump into these topics as well:
- Git: Cowboy coders need not apply. If you are building any sort of web application, implementing robust Version Control into your
development workflow is essential. Git affords
tremendous freedom to experiment without fear of breaking the working version of your code. It will make you a better developer, so endeavor to learn the basic verbs of Git. - Shell scripting: Once you get a bit of skill on the command line you can create shell scripts to combine multiple commands. In particular, I have created a script to output and open the PDF any time one HTML, CSS or JavaScript file is saved. More on this later!
- StackOverflow: Get familiar with using StackOverflow to ask questions and find answers related to your toughest
web development challenges. A good coder knows how to find answers, and StackOverflow is one of the best places to do so.
- Advanced workflow techniques: Web development is full of tools to help you code faster and debug efficiently. Modern browsers all have tools that allow developers to troubleshoot code.
- Refresh the browser: You will want a method to refresh the browser, as you
work. I use a commercial app called CodeKit, on OS X. CodeKit both refreshes the browser every time I save a file, and runs a shell script to output and open the PDF report. Using CodeKit takes most of the pain out of implementing browser refresh.
Solution Parts
Outputting a PDF report from FileMaker involves several interconnected parts. I will list them here and then provide detail on each part.
- HTML files: Generally you will have up to three HTML files: an index file for the report body and, optionally, header and footer files for header and footer parts that can repeat on each page of the final PDF output.
- CSS file: keep all of your styles in one file.
- JavaScript files:
- angular-min.js
- angular-sanitize.min.js
- report-properties.js
- report-controller.js
- wkthmltopdf: A free command line tool for converting a web page to a PDF. Can be used server-side.
- PDFtk Server: PDF tool kit is used for processing PDFs. This is available for free and can be used server-side.
- bBox plugin: I’ve used the OS X-based bBox plugin from Beezwax for this example. Windows support is not provided in the sample file, although it could be adapted for such, for example using the BaseElements plugin for Windows.
- JSON custom functions: See the sample file for which JSON custom functions to use.
- FileMaker ‘CODE’ table: Your solution will need a table with a container field. This will store the files used in your reporting web application.
- FileMaker scripts: Several processes need to be scripted in FileMaker to make this all work. Here is a list of basic scripts that your FileMaker solution will perform on the road to creating a PDF report. The idea here is to use PSOS to offload the heavy lifting to the server:
- Find your data, construct it as JSON objects and output it as a file to the temporary directory.
- Output other code resources (HTML, CSS, JS and graphic files) to the temporary directory.
- Generate the PDF using the wkhtmltopdf command line tool. We use bBox to issue this command.
- Post-process the PDF using PDFtk Server command line tool. Again, bBox is used to issue the command. (Warning: if you are using Mac OS X 10.11 – El Capitan – then please visit this amazing stack overflow question and be sure to use the download link as suggested in the selected answer. The regular download for PDFtk Server will hang FileMaker!)
- Store, and then open the PDF.
Installation and Setup
The best way to learn how this technique works is to get it running locally on your own system. The sample file included in this article will allow you to get up and running fast. I have added plenty of comments in the scripts to help you understand each step. You can also open and pick apart the web code to gain understanding.
Testing for the sample file has been conducted on OS X 10.11, and on FileMaker Server 13 and 14 hosted on OS X. FileMaker Pro Advanced is highly recommended as there are custom functions involved. You will want to step through the scripts to understand what they are doing.
Note: I have provided a variety of links at the bottom of this post to help you locate documentation that may help fill in the gaps of your understanding.
Now, here are the steps for installation and setup:
- Download Beezwax_Angular-FileMaker_PDF_0.4.fmp12 and unzip the sample file. The user is ‘admin’, password ‘beezwax2016’.
- [optional but fun!] Learn how to pronounce “wkhtmltopdf.” Trying to say “WebKit HTML to PDF” every time you need to refer to this tool is a bit painful. Open your terminal and issue the command:
say wah kah hah tah mul too pa duh fa
Now, if you have an hour or so to kill, you can experiment with using different voices. In OS X, these voices are listed in System Preferences/Dictation and Speech/Text to Speech. My favorite:
say -v hysterical wah kah hah tah mul too pa duh fa
- Back to work! Side note: for the next two steps you may get a security warning when installing these files on OS X. This can be overridden by control-clicking (right-clicking) on the package file and selecting ‘Open’.
- Download and install WKHTMLTOPDF. If you are going to host the sample file, then this should be installed on the server. If you care about such things, wkhtmltopdf is installed in /usr/local/bin/ by default on OS X.
- Download and install PDFtk Server. Use this link for the installer on OS X El Capitan (not following this advice is a POOR LIFE DECISION). Again, install this on the server if you intend to host the sample file. If you care about such things, PDFtk is installed in /usr/local/bin/ by default on OS X.
- Download and install the Beezwax utility plugin bBox. Install on the server for a hosted file. bBox is Mac OS X-only, and you should install the latest version (0.81 as of this writing). If you already have bBox installed, not updating the plugin would be another POOR LIFE DECISION.
- Create a directory named ‘report’ on your desktop. Required files will be exported to this directory. This step is not required for server installation as the temp directory will be used there.
- Open the Beezwax_Angular-FileMaker_PDF_0.4.fmp12 file.
- Run the FileMaker script ‘Export PDF Report’. I would suggest letting it run on its own first. The result should be a PDF exported to your desktop and opened automatically.
- Check out the files exported to the ‘report’ directory. Open them up in your text editor. Read the code.
- Then delete all of the files in the ‘report’ directory and run the script again with the debugger on.
- If you can, host the file on FileMaker Server and execute the script from a FileMaker client. In this case, the required files are exported to the temp directory on the server, but the PDF should still be exported to the user’s desktop and opened.
Dissecting the Technique
The most effective path for understanding this technique is to simply read the code. I have placed comments throughout both the FileMaker solution scripts and the web code.
That said, here is a basic outline of what is going on:
Script: Export PDF Report
This script does the following:
- performs basic setup
- executes the main script
- handles errors
- exports the final PDF report
Note that when calling this script, you will probably want to pass the record ID(s) for records that you want to report on. These values are passed to the next script and finally to the ‘Gather Data’ script where they are used to find the record or records to be used in the report. This feature is disabled in the sample file, as we are hardcoding the data in the ‘Gather Data’ script.
Script: Generate PDF
This script is responsible for:
- attempting execution using Perform Script On Server (PSOS)
- calling the data construction script
- exporting web files to the temporary directory
- performing the HTML to PDF conversion using bBox and wkhtmltopdf
- executing a post-processing script that removes extraneous characters from the PDF
One important bit of this technique is that it can be executed via PSOS. This allows the server to manage the heavy lifting and ensures that an end user’s client computer does not need to have the command line tools installed (wkthmltopdf and PDFtk). When running PSOS, however, we want to make sure that the server can handle the script load, so we trap for error 812. This error indicates when FileMaker Server has maxed out its allowable simultaneous script sessions.
The wkhtmltopdf command needs files to act on. Essentially, this tool is a ‘headless’ web browser that reads your HTML, CSS and JavaScript files, constructs the web page and then converts the result to PDF. This script is responsible for exporting the files to a temporary directory where the command can access them.
The PDF generation itself is performed by using the bBox plugin function bBox_bash on a constructed wkhtmltopdf command. If you take a look at the file ‘report_test.sh’ you can inspect the basic syntax of this command. Basically, the wkhtmltopdf command is passed several arguments specifying the report structure and input/output paths. The ‘report_test.sh’ file is found in CODE table in the sample file.
Script: Gather Data
Gathering data is the next task to complete as we build out a PDF. The script constructs your data as JSON objects and then returns that blob of data back to the Generate PDF script. In a certain sense this is the most important step and really the main FileMaker script to require significant customization if you want to use this technique. This is where your understanding of JavaScript objects and particularly JSON will help you create a script that structures your FileMaker data as JSON that can be digested by the web application files. That said, the sample file script simply hard codes the data as text strings in JSON format and provides example of how to encode data using the JSON custom functions.
Script: Process PDF
The next step, after Generate PDF uses the data to create your PDF, is to do some post-processing. The wkhtmltopdf utility creates some extraneous null characters, and it is helpful to remove them. This script uses bBox and the PDFtk command line utility to execute three commands that uncompress, strip null characters and then recompress the PDF file.
Script: Generate PDF
Once PDF post-processing is complete, we end up back in the Generate PDF script where the resulting file is exported and then opened. Voila!
Development Tips and Tricks
Other than the usual web development workflow enhancements such as version control and browser refresh, perhaps the most useful thing you can do is to automate the generation of the PDF report while you are coding. While you can preview your report in a browser, this cannot be trusted as actual PDF output may appear differently. Also note that although the browser can render the index.html page, it won’t integrate the header and footer files. It turns out that it is pretty hard to get a webpage to print with a header and footer appearing on every page using just browser-rendered HTML and CSS. The wkhtmltopdf tool takes care of this easily, outputting a PDF with the header, body and footer.
To automate the PDF generation, I use the paid ($32) app CodeKit to watch my development directory files. CodeKit provides easy browser refresh every time you save a file, and it also will run a shell script. Check out the report_test.sh file in the CODE table in the sample file. I simply add a custom hook to CodeKit to watch for file saves and then run the shell script. If you leave the exported test PDF open it will simply refresh every time you save a file. Sweet!
Thanks for reading! Please send me feedback on this article or the sample file so that I can improve them for future readers. You can reach me at darren_b(at)beezwax.net.
Darren Burgess is a Senior Developer at Beezwax. When his hands aren’t busy with things like code, kids, jazz guitar or ping pong paddles, you can also find Darren on Twitter.
Handy References
If you are ready to go deep on exploring documentation, this should help you get started:
- Installing FileMaker plugins locally
- Installing FileMaker plugins on server (pdf, pages 135-139)
- wkhtmltopdf
- PDFtk Server
- Angular Directives (focus on ngApp, ngController, ngRepeat, ngBindHtml)
Credits
This technique represents the contributions of many past and present Beez and friends of the hive: Brian Schick, Perren Smith, Mary Martinez, Simon Brown, Donovan Chandler, Steve Senft-Herrera, Dan Smith, Jeremy Bante and Vince Menanno.
5 thoughts on “JavaScript-driven PDF Reports in FileMaker using AngularJS”