Creating .zip archives from FileMaker using AppleScript

All FileMaker developers have their own habits about how they develop, what tools they prefer to use and how they setup their working folders and backups. I develop on locally stored files as well as files hosted by FileMaker Server (I do a lot of FileMaker Custom Web publishing work using the FileMaker PHP API which requires the files to be hosted by FileMaker Server). I’ve traditionally developed using locally stored files except for my CWP/PHP work and have used a companion database for each new project that I start that I use to create compressed timestamped backup archives of my master database file/s and any associated php files.

I use a mix of backup plans to keep known good copies of my files locally as well as off-site (this post doesn’t go into the details of what constitutes a comprehensive backup plan). Whilst I’m developing I like to close my files periodically (usually every 20-30 minutes or after a major chunk of work has been done) and make a quick .zip of the files before proceeding. I’ve been working this way for so long now (over 10 years from memory) that it’s become a habit for me. I’ve also learnt the hard way too – there’s nothing worse than having to re-do a major chunk of work because FileMaker crashes, you accidentally click cancel instead of OK, there’s a blackout and you haven’t got a UPS etc etc. I go to great effort to ensure that files I’m working on for my clients have never crashed before they are deployed into production – if FileMaker Pro Advanced did crash whilst I had any client files open I simply trash them and revert to the previous backup I had made. I also like to confirm that the files that I’m about to copy into a .zip archive are in fact closed first.

I use a simple FileMaker database that I duplicate for every new project that I start – the link to download this is below. It’s Mac only as it uses AppleScript – I do 99% of my development on the Mac platform so this makes sense for me. It has 4 fields that I use to store the locations to:

the source folder containing the master database file/s that I’m working on (always stored on my local hard disk)
the destination folder where I wish to save a backup .zip archive of these into (usually on an external hard disk)
the source folder containing the master PHP files that are associated with the current project (if applicable)
the destination folder where I wish to save a backup .zip archive of these PHP files (usually on an external hard disk)

Getting the Path to the Source and Destination Folders

Before I can create the .zip archive I first need to populate these fields with the path to my selected source and destination folders. To do this I perform a very simple AppleScript:

set theFolder to choose folder
copy POSIX path of theFolder as text to cell "zResult_g" of current record

which prompts the user to select a folder using the familiar Mac OS X dialog box:

If I haven’t already created the folder in the appropriate location I can click the New Folder button to create it as I go, all from within the same dialog. Assuming I click the Choose button and not the Cancel button the path to the selected folder will be returned to the FileMaker via the 2nd line of the AppleScript which copies this to the zResult_g field (N.B. all the fields in this example are global fields and there is only one table so I don’t need to specify which table or record for AppleScript to reference). Note that I’m instructing AppleScript to return the POSIX path to the selected folder – this is important as the POSIX path is required by the shell script to create the .zip archive in the 2nd AppleScript. The POSIX path should look something like this:

/Users/andrew/Documents/FileMaker/Projects/Example Project/

If I didn’t specify the POSIX path it would return the AppleScript path which looks something like this:

Macintosh HD:Users:andrew:Documents:FileMaker:Projects:Example Project:

The syntax for returning the AppleScript path would be:

copy theFolder as text to cell "zResult_g" of current record

Once I’ve selected the path to the source and destination folders I’m using some additional FileMaker calculation fields to retrieve the path to the parent folder of the selected folder and also the name of the selected folder. I need to refer to these folders when running the shell script that makes the .zip archive as I want it to run from the location of the parent folder and tell it the name of the folder to archive (this involves performing the change directory command later on to switch to the location of the parent folder). This is not strictly necessary but results in a much neater .zip archive when expanded that doesn’t include all of the folder paths to the source folder – this is because by default AppleScript is running the shell script from the root directory. I could also generate these paths within AppleScript without too much effort but I’m a novice when it comes to AppleScript and can do this quite easily in FileMaker Pro as well.

The field ProjectDatabaseFolderParent_c does a simple calculation to get the path to the parent folder of the selected folder (still using the POSIX format):

/Users/andrew/Documents/FileMaker/Projects

and the field ProjectDatabaseFolder_c calculates the name of the selected folder:

Example Project

Creating the .zip archive/backup

There are 2 buttons to create the .zip backup for both the FileMaker databases and the PHP files. Both buttons perform the same script but pass in a different parameter to the script. The FileMaker script that is attached to these buttons use native FileMaker script steps to check for any mandatory requirements (e.g. that I’ve selected a source and destination folder, that there are no other FileMaker files open etc) and then perform an AppleScript that creates the .zip archive. The AppleScript executes a few processes in order as follows:

set tableName to "Backup Template"
set the item_path to get data cell "ProjectDatabaseFolder_c" of table tableName
set the backupFolder to get data cell "BackupProjectDatabaseFolderPath" of table tableName
set the parentFolder to get data cell "ProjectDatabaseFolderParent_c" of table tableName
set the startFolder to the quoted form of (parentFolder as text)

This section retrieves the values from the fields in the Backup Template.fp7 file that the AppleScript needs to reference when running the shell script. Note that the references to the FileMaker fields are hardcoded in strings – if you rename any of these fields you will need to update the AppleScript or it will break (as far as I know you can’t reference FileMaker $ variables from within an AppleScript but I would love to be proved wrong here). The next step is to generate the timestamped filename that will be used when creating the .zip archive:

set aDate to (current date)
set fDate to formatDate(aDate) & ".zip"
on formatDate(aDate)
set {yr, mo, dy, hr, mn, sc} to ¬
{year, month, day, hours, minutes, seconds} of aDate
return ("" & addLeadingZero(dy) & ¬
addLeadingZero(mo as integer) & yr & " " & ¬
addLeadingZero(hr) & ¬
addLeadingZero(mn) & addLeadingZero(sc))
end formatDate
on addLeadingZero(n)
return text -2 thru -1 of ("00" & n)
end addLeadingZero

Essentially all I’m doing here is getting the current timestamp then extracting the various date and time components in the format which I require. This will result in a filename like “10102011 150304.zip” (I’m using the Australian preferred DDMMYYYY HHMMSS format to generate the text timestamp string but you can re-order this any way you like). AppleScript, like FileMaker, has it’s own rules about data types and how date and times are formatted so I have to add any leading zeros where required etc.

Now I’m ready to create the .zip archive by calling a shell script:

tell application "Finder"
set the sourceFolder to the quoted form of (item_path as text)
set zipFile to fDate as text
set the zipFolder to the quoted form of ((backupFolder & zipFile) as text)
set shellscript to ("cd " & startFolder & "; /usr/bin/zip -r " & zipFolder & " " & sourceFolder)
do shell script shellscript
end tell

Note that I’m first changing directories in the shell script to the parent folder before I generate the .zip archive using the “cd” command. As I’m calling more than 1 command in my shell script I’m using the semi-colon character to separate these.

Finally I’m passing the result back to a global utility field in the FileMaker table:

copy result as text to cell "zResult_g" of current record

It returns the the result of the AppleScript which shows the file/s that were compressed and how much compression was achieved for each:

adding: Databases Test/ (stored 0%)
adding: Databases Test/FileMaker Project A.fp7 (deflated 90%)
adding: Databases Test/FileMaker Project B.fp7 (deflated 64%)

I’ve included the zResult_g field on the main layout as I can use that as a quick visual reference as to how the AppleScript performed. From my quick testing there are some Objects in the FileMaker AppleScript dictionary that do require the field to be on the layout – I haven’t found a definitive list of these yet and haven’t completed the results of my testing of these. If you were to remove the zResult_g field it will break the last line of the AppleScript.

The actual AppleScripts were cobbled together from various examples that I found on the Internet as well as making use of the FileMaker Pro Apple Events reference database. I’m very much a beginner when it comes to AppleScript so many of you can probably optimise the actual AppleScripts – for example I’m not bothering to do any error trapping in the AppleScript.

You can download a copy of my backup tool here: BackupTemplate.fp7

If you have any comments and suggestions about how this tool can be improved please leave a comment below and I’ll post an updated version of with any changes. I’m planning on updating this in the future to do some error handling in the actual AppleScript as well as some other tests, such as ensuring the destination folder isn’t the same as the source folder etc. This database was created and tested under Mac OS X 10.6.8 – your milage may vary on other versions of Mac OS X.

Here are some references that you might also find useful if you wish to modify this tool yourself or learn more about AppleScript and FileMaker:

Introduction to AppleScript Language Guide

AppleScript Error Codes

Keeping good backups – a simple snapshot tool (Goya)

A Simple Backup Script (filemakerhacks)

Create Contacts in the OS X Address Book (Mark Banks)

AppleScript Essentials – Introduction to Scripting FileMaker Pro (mactech)

Apple Events Reference database (FileMaker Inc)

Applescript bold & fearless PauseOnError video by Bruce Robertson

AppleScript and POSIX paths