Chivalry Software, LLC

Scripting FileMaker and iCal

This article was originally written by Chuck (with the assistance of Scott Love of The Moyer Group) for Macworld Magazine, and appeared in the November 2003 issue. However, it doesn't appear to be available online anymore, so we're placing it here in the hopes that it will prove useful to some developers. The article was originally written in the days of FileMaker 6, but the concepts should be applicable to FileMaker 7.

FileMaker Pro is easy to use and supremely scriptable via AppleScript; it lets you design databases that do almost anything, including interact with data in other programs. This sample project is a case in point. With it, you can tract a project's to-do items via a system created in FileMaker Pro (800/325-2747, www.filemaker.com). And it lets you push those to-do items to a handheld device via Apple's free iCal application and iSync utility. (For an AppleScript primer, see "Amazing AppleScript," December 2002.)

Needed: Two Databases

You can use FileMaker to create massive database systems, but for this example, we needed to create two small databases. (If you don't want to create these files or to type out the scripts, you can download them from www.macworld.com/downloads/fm-ical.sit.) Set up one database, named ToDos.fp5, that contains three Text fields (Task, Calendar, and Priority) and one Date field (DueDate). The other database, Calendars.fp5, should contain one Text field (CalendarName). In the ToDos.fp5 database, format the Priority field as a pop-up list with an attached value list containing four options: None, Very Important, Important, and Not Important. You should also format the Calendar field as a pop-up list with a value list containing the contents of the field CalendarName in the Calendars.fp5 file.

First Script: Sync Calendars

The first think you'll do is write a script that synchronizes your iCal calendars with the contents of the Calendars.fp5 database; if you create new calendars within FileMaker, they'll be added to iCal, and if you add new calendars to iCal, FileMaker will pick them up automatically.

You use ScriptMaker, FileMaker's built-in automation system, to embed an AppleScript within FileMaker. While ToDos.fp5 is active, select Scripts>ScriptMaker. Type a new script name, iCal Sync, and click on Create. FileMaker will pen a script-definition dialog box with a pane to the left with your script choices. (Delete the items it has placed in the right pane by default.) Since your script requires that the Calendars.fp5 file also be open, scroll to near the bottom of the Files subsection and double-click on Open. Then click on the Specify File button and choose your Calendars.fp5 file.

To embed any AppleScript code in FileMaker, you need to scroll almost to the bottom of the left pane and double-click on Perform AppleScript; then click on the Specify button. In the resulting window, you can type or paste your AppleScript code:

set iCalList to {}
tell application "iCal"
  repeat with theCal in every calendar
    set iCalList to iCalList & {title of theCal}
  end repeat
end tell
set FMCalList to every record of database "Calendars.fp5"
repeat with theCalendar in iCalList
  if theCalendar is not in FMCalList then ¬
    create new record at database ¬
      "Calendars.fp5" with data theCalendar
end repeat
repeat with theCalendar in FMCalList
  if theCalendar is not in iCalList then ¬
    tell application "iCal" to ¬
      make new calendar with properties ¬
        {writable:true} & ¬
        {title:theCalendar}
end repeat

The script stores the available calendar names from iCal and FileMaker is a few list variables (iCalList and FMCalList), and then loops through each list and compares the calendars in FileMaker and iCal. If calendar names that appear in one list are missing in the other, the script supplies the missing data.

While this script explicitly targets the iCal application, it doesn't target FileMaker by name. Because the script is stored within FileMaker itself, the program assumes that any commands not targeted elsewhere are meant for it.

To make this script run every time your database file opens, select FileMaker Pro>Preferences>Document. In the When Opening 'ToDos.fp5' section, select the Perform Script option and then select iCal Sync.

When creating the new record in FileMaker, the script uses the with data qualifier for create new record. In this case, your record has only one field, so the data is a simple string (stored in the theCalendar variable). However, if the database contained multiple fields, you could accomplish the same type of operation by using a list: the data's position in the list would determine which field received the data, with the creation order of the fields in FileMaker setting the order in which AppleScript populates them. You can also set individual fields (called cells in FileMaker terminology) in a record by using a command such as set cell "CalendarName" of current record of database "Calendars.fp5" to theCalendar.

Second Script: Export Events

The previous script used iCal data to add items to FileMaker. Now you can create a new iCal To-Do item from a FileMaker record. (Use the method described earlier to create a new ScriptMaker script — this one called Create ToDo — in the ToDos.fp5 database, with a Perform AppleScript item within that script.)

tell current reocrd of database "ToDos.fp5"
  set theDueDate to cell "DueDate"
  set theSummary to cell "Task"
  set thePriorityName to cell "Priority"
  set theCalendar to cell "Calendar"
end tell
set theDueDate to date theDueDate
set thePriority to 0
set prioritiesList to ¬
  {{priorityName:"None", priorityNumber:0}} & ¬
  {{priorityName:"Very Important", priorityNumber:1}} & ¬
  {{priorityName:"Important", priorityNumber:5}} & ¬
  {{priorityName:"Not Important", priorityNumber:9}}
repeat with currentPriority in prioritiesList
  if thePriority is priorityName of currentPriority then
    set thePriority to priorityNumber of currentPriority
    exit repeat
  end if
end repeat
tell application "iCal"
  set theCal to ""
  repeat with i from 1 to count of every calendar
    if title of (item i of every calendar) is theCalendar then ¬
      set theCal to item i of every calendar
  end repeat
  if theCal is "" then
    set theCal to make new calendar with properties ¬
      {writable:true} & ¬
      {title:theCalendar}
    tell application "FileMaker Pro to ¬
      create new record at database ¬
        "Calendars.fp5" with data theCalendar
  end if
  make todo at end of todos of theCal with properties ¬
    {due date:theDueDate} & ¬
    {summary:theSummary} & ¬
    {priority:thePriority}
end tell

This script extracts the data from the current FileMaker field and stores each item in a variable (theDueDate, theSummary, and so on). Note that we saved a good deal of typing by using the tell block that b egins with tell current record of database "ToDos.fp5", rather than repeating the same information in all four lines.

The line set theDueDate to date theDueDate may seem repetitive, but it's necessary. FileMaker returns the contents of a Date field as a text string. This line converts that text into a proper AppleScript date object.

Next, the script instructions create the variable prioritiesList, which maps a list of priority names to their iCal-equivalent values. For example, Very Important gets converted to an iCal priority level of 1.

Finally, the script tells iCal what to do. Since your database allows the user to choose a calendar for each to-do item, the subsequent repeat loop matches the selection to the calendar in iCal and sets that calendar in the variable theCal. If you entered a calendar that isn't already in iCal (and you haven't run the iCal Sync in the meantime), the initial value of theCal will be empty — in AppleScript terms, "". If that's the case, the script creates a new calendar in both iCal and in Calendars.fp5, storing a reference to the iCal calendar within the theCal variable.

Finally, with the code beginning make todo at end of todos, we tell iCal to add the FileMaker to-do item to iCal.

The Final Touch: Add a Button in FileMaker

You can run your script from FileMaker's ScriptMaker menu, but to make it even easier to use, wire it to a FileMaker button. In Layout mode, draw a button with the button tool. When the Specify Button dialog box appears, click on the Perform Script step in the list on the left, and select Create ToDo from the Specify pop-up menu on the right.

Back in Browse mode, try entering some data in the record fields, and then click on the button. Switch to iCal, and you should see your new to-do. Now you're free to take your project to-dos on the road, secure in the knowledge that they agree with your FileMaker data at the office.