FileMaker Container Fields and Microsoft's OLE
FileMaker Pro .fp7 file formats on the MS Windows platform, allow the user to "insert object" when saving PDFs into container fields. To view the embedded PDFs, double clicking in the container field will open the PDF in Adobe Acrobat Reader. One problem users and developers face when converting from the .fp7 to .fmp12 file format is that FileMaker has deprecated the ability to embed objects into container fields as an OLE object types.
A bigger problem exists when converting files to the new file format, the OLE objects seem to appear nonexistent within the container fields and users cannot export or interact with the contents of the container fields; a disconcerting fact if you have thousands of PDFs. The only way to export the contents of the container field is by manual repetitive process or thru automation.
When I posed the OLE PDF container field problem to several developers, they recommended learning MS Visual Basic, Autoit and Auto Hot Key for automating repetitive tasks. These software programs, have scripting languages which allow you to write and execute scripts that automate tasks amongst other things. When I first looked into these, I have to admit that I was lazy because I didn't want to learn another language just to export PDFs out of a FileMaker container field. So I tried using several Macro Recording programs. These, often free, programs allow you to press a record button to capture your keyboard and mouse clicks. You may then save your recording and press a playback button to repeat the task that has been recorded. Long story short, while they held promise for their ease of use they lacked what I required most which was finesse and control.
What is Auto Hot Key?
To solve the problem of exporting a large volume of PDFs using repetitive keyboard commands, I decided to code an Auto Hot Key script to automate the process.
What is Auto Hot Key? From the web site:
"AutoHotkey (AHK) is a free, open-source macro-creation and automation software for Windows that allows users to automate repetitive tasks."
AHK has an excellent tutorial that I worked through in an afternoon to understand how it could help me automate keyboard and mouse clicks in context to my problem.
Preliminary Steps for Success
Install the AHK Software Program
Install the AHK macro software program on your computer per instructions found on their website. I recommend that you install the 64 bit version as it will run scripts much faster than the 32 bit version.
Use a Text Editor
I use Notepad++ , a free text editor to read/write/edit my .ahk scripts.
Create a Folder for Saving PDFs
Create a folder named "OLE PDFs your file name" and save it to your desktop or documents folder. This folder will contain the exported PDFs.
Preparing the FileMaker Pro File
- On your local computer, save a copy of your FileMaker Pro file with the OLE PDF objects. Name it to include the word "Master". E.g., "YourFileName Master.fp7"
- Open the master copy and set auto-login using [Full Access] privileges.
- Turn off or remove any scripts that are triggered to run on opening the file.
- Remove any file references in External Data Sources.
- Create a new calculation field named something like "container_file_name" that uses the "GetAsText( )" function. The calculation, GetAsText( your_container_field_name ) should return the file name in the container field, and for those container fields that have an OLE object embedded in it it returns a "?" ( empty container fields should return a Null or blank value).
- Perform a find using the wildcard character "*" (asterisk) in the container_file_name field you defined above. This will return all the records that have an object embedded in the container field.
- Optional - Delete the omitted records (they won't have object in their container field) in the find above. This can be handy when you're working with thousands of records.
- Create a new layout named OLE PDF Export based on the table occurrence with the container field in it. ***IMPORTANT: This is the layout the AHK script will be using to export the PDF files.
- On the OLE PDF Export layout add only these 2 fields:
- a unique record Id field which has a value that was generated as an auto-enter, serially incrementing number ( i.e., something that can uniquely identify the record with the container field in it).
- the container field
- Set the tab order so that the unique Id field is 1 and the container field is 2.
- Save a copy of the Master file. Name it something like "YourFileName Working.fp7". This is the file you will be performing the automated tasks using the AHK script. If something happens to this file, e.g., crashes and needs to be recovered, or container contents is removed or a record gets accidentally deleted you'll have a prepped file ready to go again!
Preference Settings for Adobe Acrobat Reader
Adobe Acrobat Reader will be used to open and save the embedded PDFs. It too will be called from the AHK script. Make sure to include these settings in Adobe Acrobat Reader preferences section for optimal results. (I made some changes from the default settings, but couldn't remember what they were during testing, so I've included screen shots of my current settings.)
.ahk - Anatomy of a Automated Repetitive Task Script
The purpose of the AHK script (FMP_OLE_PDF_export.ahk) is to Loop through the found set of records in the FileMaker file, copying the Unique Id value, opening the OLE PDF objects (saved in FMP container fields) in Acrobat Reader and saving the PDFs to a folder. It's that simple.
The AHK script that I've included in the post has been commented fairly well. Below is a high level description of what it does.
Anything in between "/*" and "*/" or following ";" are comments.
The AHK script that I've included in the post has been commented fairly well. Below is a high level description of what it does.
- line 15: "Esc" key is set as the abort key. Pressing this key stops the script.
- line 17: "Windows" + "j" key executes the script.
- line 19: The Clipboard variable is cleared of its contents.
- line 20: WinActivate, FileMaker Pro Advanced FileMaker Pro Advanced is activated. The program has focus now. (***Important*** you should change this to FileMaker Pro if you have this version)
- line 28: The number of times the Loop script step is set to loop and it begins the process of looping through the FileMaker pro records.
- line 33: Tab to the Unique Id field.
- line 36-41: Copy the Unique Id field value.
- line 45: sets the PDFfileName variable to what was copied in the clipboard in lines 36-41.
- line 54: sends a second tab to go to the container field.
- line 60: sends a right click and selects Open Acrobat Document
- line 68: Adobe Reader has focus now and the PDF file that has been opened in FileMaker is in displayed.
- lines 91-98: interacts with the save window in Acrobat Reader sends a paste command and saves the PDF document.
- line 105: closes the Acrobat Reader program.
- line 113: FileMaker Pro (Advanced) is activated and now has focus
- line 118: send go to next record keys
- line 120: ends the loop
Anything in between "/*" and "*/" or following ";" are comments.
Running Auto Hot Key Script with FileMaker
- In your FileMaker database, perform a find to return a found set of records that contain PDFs. Use the File Name field to perform your search, or another method, to retrieve the found set.
- Go to the first record, copy the name of the Unique Id field.
- Right click on the container field and select Open Acrobat Document.
- In Acrobat Reader, save the PDF file to the folder "OLE PDFs your file name" you created above. This will set which folder Acrobat Reader will save the PDFs to when running the AHK script.
- ***Important*** Switch back to FileMaker and make sure to Omit the record you just manually processed.
- Note the total number of records in the left side of the status tool bar area. Use this value to set the Number of FMP records to loop thru line of code in the Export OLE PDF from FMP.ahk script.
- Open the Export OLE PDF from FMP.ahk script using a text editor.
- Edit the found set of records on line 28 Loop n, where n is the found set of records to loop thru.
- Save the AHK script.
- Double click the AHK script to start it. It should now be running. You may confirm this by opening Windows Task Manager to see if the process is running.
- Switch to FileMaker and go to the first record in the found set. The AHK script works on the records in Form View.
- Press the "Windows" + "j" keys. This auto-hot key combination will start the process of exporting, naming and saving your PDFs.
If everything goes well, you should be able to walk away while your script does all the repetitive keyboard commands. If you notice a problem, you may abort the process by clicking the "Esc" key. This will stop the script.
Importing PDF Files into the Converted FileMaker File
Once all of the PDFs have been exported and saved to the PDF folder, OLE PDFs your file name, the files in the folder can be imported into a new FileMaker Pro file. The new FileMaker Pro file needs to be in the .fmp12 file format and at the time of this writing you may use FileMaker Pro versions 12, 13, 14 and 15.
- Open FileMaker Pro in one of the versions mentioned above.
- Create a new file/database and name it to something like OLE PDFs
- Define 4 fields:
- Image (container)
- File Name (text)
- File Path (text)
- Image Thumbnail (container)
- Go to File > Import Records > Folder...
- Click the Specify... button and select the OLE PDFs your file name folder.
- Click OK and then Continue...
- Although we defined 4 fields in our new file, the only values that need to be imported into the new file are the Image and the File Name field. The other two are optional.
- Make sure the field names are matched up and then click Import.
You now have a FileMaker file with PDFs that originated from the original .fp7 version. The File Name field will contain the Unique Id value used in your original pre-converted file. The File Name field will be used as a match field when importing the PDFs into your final converted file.
If you haven't already done so, convert your original file (.fp7) to the new file format (.fmp12). and import the PDF files using the File Name field (in the file OLE PDFs) and the Unique Id field (final converted FileMaker Pro file) in both files as the match key and performing an update.
- Open FileMaker Pro files OLE PDFs and the final converted file.
- Go to a layout and Show all records in the OLE PDFs file.
- In the final converted file, go to File > Import Records > File...
- Open the OLE PDFs FileMaker Pro file.
- In the Import Field Mapping Window, for the Import Action, select "Update Matching records in found set".
- Use the Field Name field in the OLE PDF Import file and match it with the Unique Id field in the final converted FileMaker file. Here's how to do an import update: Methods of Importing Data into FileMaker
- Click Import
The records in your final converted file should now have the PDF files embedded in them. You should be able to open and view the PDFs, depending how you've configured the field for PDF interaction.
Some Thoughts About the Process
Testing and macro execution were done on Windows 7 OS. I shared my code with another FileMaker developer who used the setup in Windows 10. They claimed success in processing over 3000 PDFs embedded in container fields using the AHK script.
I found that the 64 bit version is faster than the 32 bit version. Therefore if you can, install the 64 bit version of AHK.
I haven't tried running this process on a file hosted on a server, if anything I'm hypothesizing that there may be some latency with the process because of the connection to the server.
I found that the 64 bit version is faster than the 32 bit version. Therefore if you can, install the 64 bit version of AHK.
I haven't tried running this process on a file hosted on a server, if anything I'm hypothesizing that there may be some latency with the process because of the connection to the server.
You may speed up or slow down the execution of the process by tweaking the "Sleep" script step in the AHK script. "Sleep" is the equivalent to the "Pause/Resume Script [for duration]" script step in FMP. The Sleep script step values are set to milliseconds. I've observed that setting lower values in the "Sleep" script step, thereby speeding up the script, may cause the process to trip up on itself. During the testing phase, I empirically determined where to place the Sleep script steps in the script and set the values accordingly. The process has been tuned and optimized for my computing environment. Yours may be different and I encourage you to experiment.
I've processed thousands of PDFs and discovered that processing 200-300 records at a time works best.
I've processed thousands of PDFs and discovered that processing 200-300 records at a time works best.
I've also learned that the health or integrity of the file is important. Records which began their lives in .fp3, .fp5 files and were converted to .fp7 files were more difficult to process. FileMaker applicatoin would crash and the file would become damaged, this is why I strongly recommend having copies of your original file to work on. The AHK script had a difficult time opening the embedded files in Adobe Acrobat in older records. These records were singled out and exported manually. Sometimes container fields had corrupted files embedded in them. This would cause FileMaker to crash using the AHK script.
