Exporting Data to iCal, Outlook, Google Calendar, etc.

Demo file: 2010-12-13-export to ical

The other day I needed to export some appointments from FileMaker to iCal. I’d never done this before, but I did a bit of reading on Wikipedia and elsewhere, and it turns out to be fairly straight forward. I don’t claim that what follows is in any way authoritative, just that it works… and not just with iCal, but with Outlook, Google Calendar and any other program that recognizes the iCalendar format.

Let’s start with a basic table of appointments, like this.

We’re going to create an “ics” file, which is a text file with three distinct elements. At the beginning of the file is the header, which looks like this:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//FileMaker Pro//NONSGML yourSolutionNameHere//EN

Next we have one or more body entries that look like this:

BEGIN:VEVENT
SUMMARY:Marketing Meeting
UID:00188BD54F5D-63427112000-1000028
DTSTAMP:20101206T180000Z
DTSTART:20101206T180000Z
DTEND:20101206T193000Z
END:VEVENT

And at the very end of the file comes the footer, which looks like this:

END:VCALENDAR

Let’s take a closer look at the body section. The BEGIN, SUMMARY and END entries seem self-explanatory, but what’s going on with those “DT” entries? Well, as you’ve probably guessed, they’re timestamps. First we have the date formatted as YYYYMMDD, followed by a T, followed by the time in 24hour format, hhmmss, followed by a Z.

One important observation about the time portion: it is represented as UTC time, not local time. So the first row in our appointments table, with a starting time of 10:00 Am, will be converted to the UTC equivalent of 180000 when we export it, because the database is located in the Pacific time zone, which is 8 hours offset, i.e., UTC-08. During Daylight Savings Time, the offset value would be -7, but we’re on Standard Time now, so the correct value is -8.

Clearly DTSTART is the beginning time for the appointment, and DTEND is the ending time, but what’s DTSTAMP? That is the “creation time” of the appointment, and apparently is required. Since I don’t care about that, I’m just making it the same as DTSTART.

And finally we come to the UID entry, which is simply a unique serial number. OK, I lied. Ideally it’s some form of “UUID” which means that heroic measures have been taken to attempt to guarantee its absolute uniqueness. But as long as it’s unique within your calendar, you’ll be fine.

The UID is very important, because if you import the same appointments more than once, the calendar will be smart enough to update existing entries where there is a matching UID, rather than allowing them to appear twice. That’s right… if an appointment changes in your database, you can export it, and reimport it into your calendar without having to worry about duplicates.

So, let’s add a UID field (auto-enter, text) and a UTC offset field (global, number).

Now that we understand the design of a minimal “ics” file, we can define a calculation field, ical_basis, in our database to assemble and format the data accordingly. Reminder: the first thing we do when we define a calculation field is, of course, make sure the result type is correct: in this case we want the result type to be text. Also, let’s set the storage type to “unstored”.

This calculation is the heart of the whole operation. Its main purpose is to generate one body segment per appointment record. Additionally, it will create the header at the beginning of the first record, and the footer at the end of the last record with the help of the “rn” and “fc” variables.

Note how the conversion to UTC is handled:

  1. transform a date and time into a timestamp
  2. add 8 hours (8 * 3600 seconds)
  3. break it into individual components (year, month, etc.)
  4. pad the components with leading zeros if necessary
  5. reassemble them as YYYYMMDDThhmmssZ

Let’s reduce our found set to three records, go into preview mode and see how that calc evaluates. Looks like we’re home free doesn’t it?

We simply export our found set of appointments to a text file with an “ics” extension and declare victory, right? Well, not quite. If we try that, here’s what we get:

…a text file with three lines, each a mile long. While inconvenient, this makes sense if you think about it. When you export to a text file, FileMaker uses ASCII 13 as the record delimiter, i.e, puts a hard return between the data from each record. To avoid confusion, any existing hard returns from within the source data are converted to a “vertical tab” character (ASCII 11).

But we’re not going to let a puny ASCII character defeat us, are we? (Yes, I know, these are Unicode, not ASCII, codes… but the values are the same either way, and it’s easier to refer to ASCII codes than Unicode “code points”.)

Broadly speaking, there are two different approaches we can take to solve this problem:

A. Generate the text file, and then post process the file using some sort of external technology.

B. Parse each row of ical_basis into a separate record, and then export those records.

Let’s look at option A first. There are various ways the post-processing can be accomplished, including AppleScript on the Mac, and VB Script or PowerShell on the PC, but it can also be done via a 3rd-party FileMaker plug-in, Troi File. (There may be other plug-ins that can accomplish this as well, but Troi File is the one I’m familiar with, having used it since 1998.)

What I like about the TroiFile method is, a) it works the same regardless of whether you’re on a Mac or PC, and b) how incredibly easy it is. One little command…

TrFile_Substitute("-IgnoreCase";$fileSpec;"";Char(11);Char(13))

…and no more pesky problem.

Method B involves creating a special table to facilitate the generation of the ics file. As with method A, there are any number of ways to accomplish this, but the one I want to look at here is Bruce Robertson’s Virtual List technique. It’s a highly versatile tool to add to your box of tricks, and it also happens to score a perfect 10 on the coolness scale.

Here’s a basic explanation of how Virtual List can be implemented to solve this particular problem, and don’t worry if it doesn’t make immediate sense. You don’t have to fully understand it to reap the benefits, and the act of implementing it in one of your solutions will help you understand it better.

  1. Create a new table in your solution. Name the table VL_Utility, and don’t create any records yet.
  2. Define a number field, serial_number, as an auto-enter serial number with an initial value of 1.
  3. Define a calculated text field, virtual_list, as
    GetValue ($$virtual_list;serial_number)
    …and set the storage type to unstored.
  4. Create “more records than you’ll ever need” in this table. If that sounds unhelpfully vague, why not start with 10,000? You can always add more later.
  5. Back in your Appointment table, locate the found set you wish to export.
  6. Using a looping script, walk the records and populate the $$virtual_list variable.
  7. Define a variable, $vc, as ValueCount($$virtual_list) .
  8. Locate records in VL_Utility where serial_number <= $vc, and make sure these records are unsorted.
  9. Export these records to a text file with an “ics” extension, e.g., appointments.ics

The result looks like this and is ready to import into your calendar program.


For Google Calendar, you can import entries by clicking the “Add” button at the left of the calendar. On the Mac, you can import an “ics” file to iCal by double clicking it, and for Outlook choose Import and Export from the File menu.

Here’s what we see after importing.

Really we’ve just scratched the surface of what can be accomplished using the iCalendar format. There are many optional properties that can be included, for example “description” (a.k.a. note). If we want to include notes in our export, we can modify our ical_basis calculation as follows:

And here’s how an appointment with a note looks in iCal:

14 thoughts on “Exporting Data to iCal, Outlook, Google Calendar, etc.

  1. rebecca

    Thank you so much. I knew there must be a relatively simple way to do it, but this is downright elegant. Nice job!

    Reply
  2. David

    Thanks for this excellent article. I spent a fair amount of time Scripting the creation, export (with auto-open/push option) and re-import/syncing of these little ical files. For exporting, instead of virtual lists and what-not, I just loop the actual Events table to build a local variable with header, all the individual entries, then footer…then place result into global field to Export Field Contents – allowing ‘automatic open’ if User requests in export setup dialog. I also have a pref’s field that allows user Time Zone selection (referenced in UTC). For importing, I suck the ics file into multiple records and loop through them with big Script that figures out which entries already exists in event Table for updating (UID!), and which are new for adding to table. It also ‘handles’ attendees (which are stored in related table in FM). Send a note if you’d like to look at it in action. Cheers!

    Reply
    1. Simon

      I think the .ics file just has a different format. You just need to adapt it. Mine looks like this:

      BEGIN:VCALENDAR
      VERSION:2.0
      PRODID:-//Apple Inc.//iCal 5.0.3//EN
      CALSCALE:GREGORIAN
      BEGIN:VEVENT
      DTEND;TZID=Europe/London:20130317T100000
      TRANSP:OPAQUE
      UID:F82EE227-0127-4E89-AAAA-D84B4241AAAAA
      DTSTAMP:20130225T132000Z
      SEQUENCE:0
      X-APPLE-EWS-BUSYSTATUS:BUSY
      SUMMARY:Filemaker Test
      LAST-MODIFIED:20130225T131957Z
      DTSTART;TZID=Europe/London:20130317T090000
      CREATED:20130225T131957Z
      END:VEVENT
      END:VCALENDAR

      Reply
  3. Benjamin

    I can confirm that there’s a issue with Mac (I’m using OS 10.8.2). It looks like one event is deleting the other. I tested a list with 3 events and only the 3. got into iCal.
    After testing one-by-one, it figured out that every new added event was deleting the previous.
    Strange, since the UID’s are unique.

    Despite that issue, it’s a excellent work.

    Reply
  4. Benjamin

    I detected differences between the UID’s by Kevin’s calculation and that created with the new FM12 function “Get(UUID)”.

    I changed the Field
    uid [Formula] = Get (UUID)

    and everything works fine √

    Kevin’s result:
    UID:58B0357F85E8¶
    58B035F57E5A-63506417259-1000038¶

    FM 12 get(UUID):
    UID:FF026612-CE30-4146-85A3-7B0A6EAA4BE7¶

    Reply
  5. Sean

    Does anyone know of a good way to have Outlook auto import the ics file? For my purposes it may be cumbersome to have employees remember to import a file every day, and then the data isn’t actually synced if someone hadn’t imported the data recently and something had changed.

    Reply
  6. nicklightbody

    Hi Kevin
    Just found this and read with interest. The parsing to icalendar format is very similar to what I was doing a few years ago then reversing the process to update FM from iCal.
    What I never worked out how to do however was how to automate this process elegantly? I guess that an AppleScript would do it or perhaps a folder action if Gcal will accept an ftp in?
    I am interested in exploring this further if anyone has any good ideas that avoid using any unreliable middleware?
    Cheers Nick

    Reply
  7. Laurent Ades - Paoma

    Very good post! Helped me create a custom calendar which provides a custom calendar for each user of a FileMaker solution running on server. The nice thing is that with not too much work, i was able to serve it with php and access it from OS X/iOS Calendar through webcal subscription and now each new/updated record comes through transparently.

    Reply
  8. Mathias Priebe

    Thank you, that really helped a lot for a certain project. There was only one little thing I was missing: I needed a line break within various notes (DESCRIPTION) that come from related data. One might tell something more in a calendar entry: a phone number, a contact person, anything else. A private HRS-Hotel booking brought the solution after reading their .ics as .txt.

    It’s simply “\n” at the end of the line: “DESCRIPTION:” & FieldA & “\n” & FieldB & “\n” & … . To get a nice format some notes might end with two entries of “\n\n”. Hopefully they’ll never change the .ics structure ;-)

    Reply
  9. Francis

    What if your event starts at 9 pm? Adding your offset, the time portion will be 21 + 8 = 29. There are only 24 hours in a day. What will happen when you import an .ics file with data like [Today’sDate]T293000Z? Your code does not appear to compensate for the date and time in this instance. That goes also to say what will happen if your event starts at 9 pm on the 31st of the month, and keep in mind that not all months end with 31. Carry that to its logical conclusion of the end of the year, and you see where I’m going with this.

    Reply
  10. Kevin Frank Post author

    Hi Francis,

    FileMaker intelligently handles date, time and timestamp “overflows” automatically, e.g., Date ( 1 ; 32 ; 2016 ) is a perfectly valid construction which equates to Feb 1, 2016.

    Regards,
    Kevin

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s