Working With External Files Using Plug-ins
by Beverly Voth (lyceum@best.com)
RATING: Intermediate
VERSION: FileMaker 3, 4 & 5
PLATFORM: Macintosh & Windows
BONUS FILES: FILEREAD.FP3
At some point, you may be asked to create and populate a new or existing FileMaker Pro database system with data from an older legacy system. These files might be in a format that FileMaker Pro already understands; or the data can easily be saved or exported as a common file format that can be imported into FileMaker Pro.
There are quite a few built-in capabilities to exchange data with other systems. The common formats are: Tab-Separated Text (.TAB or.TXT), Comma-Separated Text (CSV or.TXT), SYLK (SLK) or Symbolic link for exchanging with spreadsheets, DBF (.DBF) for exchanging with dBASEIII and dBASEIV files, DIF (DIF) or Directory Interchange Format for spreadsheets such as Excel, WKS (.WK1 or.WKS) for Lotus 1-2-3 and other spreadsheets, BASIC (.BAS) for exchanging with Microsoft Basic, Merge (.MER) is similar to Comma-Separated Text, but includes field names in the first record.
Another new and important way to transfer data between applications is ODBC (Open DataBase Connectivity). ODBC provides an API (Application Programming Interface) to allow importing from other data sources or database systems. In addition, there are graphic formats that can be imported and files that can be opened with specific applications for viewing externally. All these formats require that the sending and receiving systems agree on the format.
It's well and good if you can import data using one of these formats. Sometimes it's just not possible, especially when you are working with a custom application and its unique format. Examples of these may be: email forms from a web page with name and value pairs; EDI (Electronic Data Interchange) for Invoices, Purchase Orders, and Medical Record Billing; precise field length formats (mainframe data); or any data that has a "pattern" but is simply too large to import directly into a field because of the 64,000 character limitation.
Enter File Plug-ins!
These wonders can manipulate text files directly through read, write, append and delete actions. In addition, information about the files, giving them unique names and the ability to move or copy files are often required when working with external files. While text files are probably the most common files with which to work, images files can also be imported with the help of a File Plug-in.
There are two very popular file-type plug-ins. I will review them both. They both have features that are unique as well as similar, and I endorse using them both! They are cross-platform with some subtle differences based on what the Operating System can do natively.
The Troi File Plug-in (overview)
Find it at http://www.troi.com/
Troi File Plug-in by Troi Automatisering, can give you the information about files, such as: Contents, FileSize, DataSize (true number of bytes), Date and Time Created or Modified. The Macintosh version can also return the Resource Fork, File Type and File Creator.
These are called "File Specs" (short for specifications) and can be very important if you are trying to save or open files and need to be specific on the date or time it was last accessed. This is especially important if you have several people with the ability to read or write files or files are written or read automatically by custom applications. The number of bytes in a file is important in order to put the data into FileMaker Pro fields and avoid errors in exceeding the capacity of any one field. You can retrieve portions of a file by specifying from which character to read and how many characters as parameters to TrFile-GetContents().
Files can be created, appended, deleted, copied and moved with this plug-in. In addition, this plug-in will assist you in folder creation, finding a folder and giving you the specifications, or returning the list of files and other folders within a particular folder. A list of files can be useful if you don't know the filenames and need to read them into a field for looping scripts on each file.
Among the new features of the latest version, Launch will allow you to open a file chosen from an open dialog box. This will help for times when you have external documentation, such as Word files and wish to have a "link" to the files. The actual application will launch, rather than import or open the file in FileMaker Pro itself. The possibilities seem endless on this! You can also use the plug-in to Search for a file with "find-like" criteria. If you don't quite know the name of the file, this will assist you in finding it.
Two other new additions to the Troi File Plug-in are: the ability to import images and print multiple files to.PDF format. If you have a folder of images and the path to the file name (or don't even know the filenames!), you can create a batch script to import these. Printing files to.PDF format (portable digital format - used by Acrobat Reader) relies on PDFWriter printer driver being installed. This is available from Adobe in the full retail version of Acrobat.
Documentation and sample files are included with a 30-day demo, downloadable from the web site. http://www.troi.com/
The File Toolbox Plug-in (overview)
Find it at http://www.geocities.com/SiliconValley/Network/9327/
File Toolbox versions 1.0 v1 and 2.0v1 beta are available from Protolight (see url above). This Plug-in also gives you information about existing files, such as: Properties (locked, visible, type or extension, creator), User Path to the file, and the file Size. The Properties can also be Set for a file. As files exported or written are of generic type, setting a files properties might be beneficial for opening with other applications.
Files can be created, appended, deleted, duplicated, moved and renamed. You can also Exchange information from one file to another. While the Troi File Plug-in checked for the existence of a file, File Toolbox has an additional External to do this. One should always check that a file Exists before writing to it!
Writing to the file has additional features with this plug-in. You can specify a particular position in the file. Reading also can be controlled with precision by using ReadLine (and choosing the delimiter for a "line") or specifying a position from the start of a file. To assist you in where you are in a file, you can use Find Line or Find String Position or Set Position and Get Position.
The Substitute works much like FileMaker Pro's Substitute() function, only it works on external files. This is handy when you need to change the end-of-line character. On Windows systems, this is usually Carriage Return and Linefeed. It is just Carriage Return on a Macintosh, and Linefeed on Unix systems. Files export with each systems particular end-of-line. You may need to change this to facilitate reading on another platform.
If you need to work with precise data, you can find documentation and sample files for this plug-in on the Protolight web site.
How FileMaker Plug-ins Work
Plug-ins must be placed in the proper folders. On a Macintosh, this is the "Extensions" folder inside the FileMaker Pro folder. Put your plug-ins in the "SYSTEM" folder inside the FileMaker Pro folder on a Windows system. They must be "activated" by choosing them in the Application Preferences (under the Edit menu). You will then see a list of the options for the Plug-in under the "View External functions" in the calculations dialog box.
After you have them installed and working, you can "call" them by Setting a Field or using them in other calculations. It's very important to test for the existence of any plug-in before performing any of the other Externals() for it. Since the Plug-in can return an error, you may want to create an Error field and use it to test.
Set Field [ "Error", "External ("TrFile-Version", "")" ]
or
Set Field [ "Error", "External("file-Version","")" ]
You can also test for the existence of a file, test to see if your Open or Close or Read or Write were successful, or populate a field with the data you are reading.
Working Outside "The Box"
Sometimes you must work with files that are not compatible with FileMaker Pro directly or you have two applications that cannot work together. The File-type Plug-ins can be used to assist you in working with these kinds of files. FileMaker Pro is truly useful as a ‘translator' in the middle of other file format types that need to communicate.
Check out the Bonus File FILEREAD.FP3 to try it out for yourself. You will need to download the demo versions of both plug-ins and follow the instructions in the bonus file.
Happy FileMaking!
Beverly Voth is a consultant, trainer and developer. Recently becoming a part of The Cinco Group http://www.cincogroup.com/ has expanded her horizons!