Log in
Hot & Trending on FMT

Document Scanning FileMaker Pro 13

A Macintosh computer, FileMaker Pro 13, a Fujitsu ScanSnap S1300i, Automator, Applescript, a Folder Action and FileMaker’s URL Scheme make a paperless solution for legal waivers a reality.

Since the advent of FileMaker Pro 12, my favorite database development tool has been great for building document management systems. These newer versions of FileMaker have much better handling of container fields, allowing developers to set up the storage for images and PDFs in a separate directory on the server. This keeps the main database file size smaller and easier to backup. With the advent of external storage, it is possible to relatively quickly add document management features to most FileMaker systems. FileMaker Pro 13’s improved URL Scheme makes life even better for developers wanting to run scripts from outside the database, including the ability to send script parameters.

I have a project where we are integrating waiver forms with a FileMaker customer and pass tracking database. In addition to an iPad-based sign-in form, there is still a need to be able to deal with paper forms, downloaded from the company’s web site and signed offsite by parents. The customer is a gym, and their desk space is a premium—squeezing a scanner in is going to be tough. There is space—just big enough for a small portable document scanner.

Developer Level: Intermediate/Advanced

Ingredients

  • A recent Macintosh capable of running Mavericks or Yosemite OS X
  • Automator – included software with all recent Macs
  • A Fujitsu Scanner – I am using the ScanSnap S1300i. Note that this approach could work with any scanner that can be set up to scan to a folder. (Translation: any scanner)
  • FileMaker Pro 13 (upgrade it to the latest version, which at the time of writing was 13.0v5)
  • A working knowledge of FileMaker Pro
  • Some knowledge of Automator and Script Editor
  • An hour or so

See the Speed Test section below for an idea of how quick this routine runs with a fast scanner.

 

Fujitsu ScanSnap Scanner

Fujitsu arguably makes the best portable document scanners. I have one at home and love it. The sad fact is that Fujitsu, though they make many professional document scanners, do not support TWAIN, the industry standard API for software integration.

I went looking for FileMaker integration solutions and didn’t see anything that fit the bill (more on this below in the Other Approaches section).

TL;DNR Version

  • Setup your Scanner to Scan to a Folder
    • Create a Target Folder
    • Set up a Profile to aim your Scanner Software at that Folder
    • Set your Scanning File Quality Parameters
    • Turn off Quick Menus
    • Select the Scan to Folder Profile
  • Paste the ‘Import Document’ Script into your FileMaker database
    • Configure it to point at your Target Folder
    • Set the Insert File to the appropriate container field in your database
  • Open the Automator Script called ‘ScanSnap Folder Processing.workflow’
    • Copy all the elements of the Automator script (Command A, Command C)
    • In Automator, create a New Folder Action
    • Paste the Automator script into the new Folder Action script
    • Attach the script to your Target Folder
    • In the AppleScript portion of the Automator Script
      • Edit the FileMaker Version
      • Edit the database name
      • Edit the script name
  • Test
    • Scan some pages into the target folder
    • Observe if the AppleScript is running
    • Fix
    • Read the full blog to understand what might have gone wrong
    • Fix again

Speed Test

Setting a stop watch on the process of scanning, here is what I found. With the scanner set to single-sided pages and 150 dpi gray scale images, (average 115 kb sized files) it took:

  • 46 seconds –  to scan 10 pages through the scanner
  • 52 seconds –  have all the PDFs imported into a FileMaker container field.

As you can see, most of the time of the process is spent scanning. This may be faster or slower depending on your chosen document size, scanner speed, scanner settings and how fast your computer is running. The Automator Folder Action responds almost immediately to new items being scanned or dropped into it.

Demo File

Download Demo File

ScanSnap Settings – Setup a New Profile

ScanSnap Manager – Settings – Turn off ‘Use Quick Menu’

  1. Create a new profile, give it a meaningful name, such as ‘Scan to ScanSnap Folder’
  2. Set the Application to run as ‘none’.
  3. Turn off Quick Menu

ScanSnap Manager – Set a Target Folder for your Scans

Click the Browse button to choose a folder that you want your scans to go to.

You can also change the File Name Format on this screen.

ScanSnap Manager – Settings

Because the workflow involves a single page document, I opted for Simplex scanning (single sided) and a fairly low resolution (150 dpi) to keep the file size down.

SnapScan Manager File Options

In the next tab – File Option – I selected ‘.PDF’ for the File Format. There is an Option dialog box here that allows  you to set ‘Generate one PDF file per page’. If you are scanning double-sided, you can change the default to 2 pages/PDF.

Profile now appears in the Profile popup

From the Dock select ‘Scan to ScanSnap Folder’, or whatever you named your ScanSnap Profile. To get back to the Settings for Scansnap Manager, hold down the Control Key when selecting the ScanSnap icon from the Dock.

Setting up the Automator Workflow

  • Click on the ‘Info’ icon (the small ‘i’ in the top right corner of the main screen in the demo file.
  • Save the Workflow file from the Info box in the demo file.
  • Open it in Automator.
  • Copy the entire script and paste it into a new Folder Action script.
  • Set the Target Folder

Launch the Folder Action Workflow

Click the Open with Automator button.

Automator Routine

Here is what the Automator routine does:

  1. Set the Folder Action to attach it to the folder you are targeting in ScanSnap Manager
  2. This step captures the path of any new files dropped into the folder. It seems to catch each path individually.
  3. Set a variable, based on the path
  4. Gets the variable to feed into the AppleScript
  5. The AppleScript accepts the variable as an {input}
    1. The AppleScript then grabs just the name of the file
    2. It hands the name of the file off to FileMaker using the GetURL command to run a script in FileMaker and pass it the File Name variable
  6. We go back to the original path statement
  7. Using that Path we move the file to a ‘Processed’ folder
  8. Loop causes the workflow to keep going until there are no more files in the folder.

Select All and Copy the entire Automator routine into the clipboard.

Create a New Automator Routine – Folder Action

Select New Automator Workflow and select the type ‘Folder Action’. Paste the script from above into the new Folder Action.

Select a Folder to apply the Folder Actions to

In this case, we will select the ScanSnap Folder on the Desktop.

Edit the FileMaker Version

I work with FileMaker Pro Advanced. When I deploy this solution, it will be running on FileMaker Pro. Set this line of the AppleScript accordingly.

Troubleshooting Tip: When deploying this solution, the location had a copy of FileMaker Pro 11 and FileMaker Pro 13 installed. AppleScript does not seem to have a good method of recognizing the difference between the two. My solution was to zip the FileMaker Pro 11 application to prevent it from opening.

Edit the Folder Action Applescript

Change the variable myDatabaseName to whatever the name of your database is.

Change the variable myDatabaseScript to whatever the name of your import script is. This script in FileMaker Pro must accept a File Name as a parameter.

The Magic Happens Here

What this Automator script is doing is that whenever a new file is added to the target folder (by the ScanSnap scanner, or by dropping the file in the folder), the File Name of the newly added file is handed off to FileMaker via AppleScript, using the ‘FMP’ URL Scheme. FileMaker dutifully performs the script that is called and the parameter that gets passed is the File Name.

This getURL script assumes that the database is open, logged in with the appropriate permissions to run the referenced script.

Advanced Tip: It is possible to embed a user name and password to the script, but that may be seen as a security issue. The syntax is:

FMP://[[account:password@]netaddress]/databasename
[?script=scriptname[&param=scriptparameter][&$variablename=value]]

With a user name and password, the database doesn’t even have to be open to launch FileMaker and run the script. Use hard-coded user name and passwords at your own risk.

Automator Loop Step

Adding this step to the Automator Workflow is what causes the routine to run repeatedly in order to process multiple files at a time. When I first set it up, the default was for ‘Stop after 1 minute’. I noticed that the routine would import about a dozen records and then stop. By pushing the limit to 5 minutes, I was able to handle larger batches of files being dropped into the folder. If you are dropping exceptionally large numbers of files, you may need to move this limit higher. If you are using this primarily with a scanner, 5 minutes should be fine.

Debugging Automator/Applescripts

When an Automator script runs, a small gear icon appears on your top menu bar. To halt a runaway script, click the little x at the right of the popup box.

FileMaker’s ‘Import ScanSnap Document’ Script

This is a fairly basic script, if there is a script parameter (the path), then make a new record and insert the file from the path.

  1. My configuration is a target folder called ScanSnap on the Desktop. If you want to use a different name or location, edit the $filePath variable, changing ‘ScanSnap’ to ‘<<Whatever your Folder Is Called/>>’. The end slash is important.
  2. Another important change is to set the Insert File script to the appropriate container field in your solution.

Features of the Automator/Folder Action Approach

  • Editable: If something changes in your workflow, it is fairly easy to open it up and change things.
  • Flexible: When you get documents from another source than ScanSnap scanner, such as via email or Dropbox, you can still process them using the same routine, ie, drop the files in the folder.
  • Tinker-able: Want to add OCR, or file compression? Open the hood, edit the AppleScript and you can build this into the routine.
  • Free: Also free.

Cons

  • With so many moving parts, this may be inherently breakable. Next OS X release, the entire house of cards could come crashing down. Don’t blame me. Dig into the code and fix it, then send me an update…

The Full Applescript

on run {input}
--this gets the file name from the input variable.

tell application "Finder" to set myFileName to name of first item of input

--debug: if you are having problems, turn on this display dialog 
--to see what file name you are getting.

--display dialog myFileName
--setup the database name and script name of the FMP url here:

set myDatabaseName to "HB-ScanSnap-Integration-Demo"
set myDatabaseScript to "Import_ScanSnap_Document"

try
 --change this to "FileMaker Pro" if that is your target.
tell application "FileMaker Pro Advanced"
activate

--FileMaker 13 supports this type of URL to run a script 
--inside FileMaker, passing a parameter. 
--set up your script to accept as a parameter the path of a File.

getURL "fmp://$/" & myDatabaseName & "?script=" & myDatabaseScript 
& "&param=" & myFileName
end tell
end try

--delay for a few seconds to allow the import to happen in FileMaker. 
--You may need to adjust this delay if your machine is faster or slower.

delay (3)

--go back to the Finder to process the next phase of this Automator 
--action, which is to move the processed file to another folder. 
--I called mine ScanSnap Processed.
-- you could change this last part to move the file to the trash 
--once you are sure it is working well.
-- The Loop at the end of the Automator transaction is to handle 
--when multiple files are put into the folder rapidly.

end run

Workflow Options

The workflow in the demo is sending just the File Name as a parameter. It would make it more modular if instead it sent the entire path, then you wouldn’t have to configure the FileMaker script to reference a particular folder and you could move the folder around to different locations on your drive, without breaking the script. I tried this and it works, sort of, but for some reason instead of just inserting the file, each time it loads a document, you briefly see the Open File dialog. The end result is also that the last step leaves the Open File dialog box open, potentially confusing for the user. There may be a solution to this, but I went with what worked the first time, sending just the file name. If you want to experiment with it, here is the AppleScript code I was using.

Instead of

tell application "Finder" to set myFileName to name of first item of input

I tried:

--get the path from the value of the variable.
--set thePath to first item of input as string

--convert it from : to /
--set myFilePath to the POSIX path of thePath

--Then change
getURL "fmp://$/" & myDatabaseName & "?script=" & myDatabaseScript 
& "&param=" & myFileName

--to 

getURL "fmp://$/" & myDatabaseName & "?script=" & myDatabaseScript 
& "&param=" & myFilePath

In the FileMaker Import Document script, change the $filePath variable to Get(SystemDrive) & Get(ScriptParameter), which will be the full path starting at Users/.

Other Approaches

According to my Googling, there are a some other solutions out there for building a paperless office with a scanner and FileMaker Pro.

Free – there is a free app called Snap2FM, which I was not able to get to work. I ended up employing pretty much the same approach as they use, using the FMP URL script protocol, but instead of compiling it into an app, I have created it as an open Automator action which is more editable.

Use a Bigger Fujitsu Scanner – Another solution is called DocKeep from Productive Computing. It starts at $199 and seems to support mostly higher-end departmental and workgroup scanners.

Use a TWAIN Capable Scanner – Combine a TWAIN scanner (Wirecutter’s second choice was this TWAIN capable Canon scanner or a slightly larger Epson scanner.) and a TWAIN plugin for FileMaker Pro. Monkey Bread Software’s do-everything MBS plugin has TWAIN capabilities. Roll your own system.

Windows Alternatives: MIA

I looked for a bit for ways to do this same thing in Windows, but didn’t come up with anything solid. It is possible to send files to a Folder in the ScanSnap software on Windows. But that’s where the automation ends.

3rd Party Tools

There is a simple tool called Folder Actions, but it seems to be a one-step automator. I couldn’t see how it could do what I wanted, calling a FileMaker URL. I also looked at ‘Actions‘ which seems to be End of Life.

Microsoft

I am surprised to see that Windows still doesn’t have an equivalent to Automator built-in. I thought Redmond was good at copying things Apple did. Steve (Ballmer) would never have let this happen.

MonkeyBread/TWAIN option

The MonkeyBread/Twain solution suggested above might be a route to try. This would not work with the Fujitsu scanner, but another similar TWAIN-compliant scanner would work. This route would skip the intermediate folder and just scan directly into FileMaker.

More Resources

I am not much of an Applescripter, but I do know how to copy and paste from Google searches. Here are some resources I looked at and possibly read in the process of writing this blog post.

No comments yet... Be the first to leave a reply!

Leave a Reply

Search Blog Posting Archives

Read more http://hbase.net/

Douglas Alder

Doug and his comapny HomeBase Software Publishing Ltd., provides consulting, design, and development services for FileMaker Pro-based local area network database applications. We build custom solutions for companies and non-profits organizations. Our main tool is FileMaker Pro database software.

As founding publisher and editor of The Computer Paper, Douglas Alder started Canada Computer Paper Inc., in 1988 and grew the publication from a startup of 30,000 copies a month in Vancouver to the dominant computer publication in Canada, with an estimated readership of over 1,000,000 (circulation 350,000) per month across Canada and millions in sales.

Doug is one of favorite FileMaker Authors and Canadians! To find many more in depth articles from Doug and some must have tools and services from Homebase visit their site at the link below:

Website: hbase.net