Calendar Events from FileMaker

The other day a client asked me if FileMaker could do something that I had always believed was impossible to do innately from the program, create calendar events in Outlook. I had always thought I needed to use a plugin to get information from a FileMaker database to Outlook.

Now the client didn’t want to use a plugin, they wanted to keep cost down on the system, so it fell on me to find a way to create calendar events through FileMaker based on data in the system. I found a way…

It started with a dinner date. Strange, I know. I was booking a dinner date for my wife and I at P.F. Chang’s online, and I noticed a link to add the reservation to my calendar. Upon clicking the link, I found that I was prompted with an Outlook calendar item with the subject, date and time filled out completely. This sparked my interest, so I started searching online for ways to create calendar events through the web. If the web could handle creating events, then FileMaker should have no problem. As it turns out, it is really easy to create events, or .ics files. The code for the .ics files follows a standard format, and with some basic Google searches, I was able to find some great examples.

** Disclaimer – Windows 7 and Office 2007 were used in this example along with FileMaker Pro Advanced v11.

Now you may be thinking I used a “DDE Execute” or “Send Event” script step to create the event, but instead I went with a simpler method. I decided to use a global field, and write a script to set the field to the code for the .ics file, and export that field contents. Finally, have FileMaker set a variable for the temporary path location, and export with the option to automatically open the file. The file will open with the defaulted Calendar program, in most cases Outlook, and the user is free to cancel the event or save it.

First thing I needed to learn about .ics fil was the format. Take a look at the following code:

BEGIN:VCALENDAR

VERSION:2.0

PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN

BEGIN:VEVENT

UID:

DTSTAMP:20101111

DTSTART:20101111

DTEND:20101112

SUMMARY:Sameer’s Workshop

END:VEVENT

END:VCALENDAR

You can see that it is not too complicated. There are some rules about the format of date and time to discuss, but on the whole, you can see where we would input certain information.  DTSTAMP, DTSTART, and DTEND all track info as Timestamps, if you input dates only, as I have done, you will see the event tracked as an “All Day Event.”

**Time can be input as well, though calculating the time is a bit more complicated. See below for more information and for the resources used in this blog.

One way of viewing more detailed calendar events would be to create an event in Outlook, and before saving the event, click on the File menu (or the round button in outlook 2007) and select Save As. Then open the file using Notepad. You will see a similar format as above but with some additional tags, such as created by, last modified, UID Information, as well as how the time formats work.

Finally it was time to write the script to set the code appropriately and export the contents of the global field. Since we are still at a basic level for .ics file development, I was not too concerned with getting details of time, attendees, location, etc.

Script:

• Set Field [Event::g_Data ; "BEGIN:VCALENDAR¶VERSION:2.0 ¶PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN¶BEGIN:VEVENT¶UID:¶DTSTAMP:" & Year(Event::start) & Month(Event::start) & Day(Event::start) & ¶ &"DTSTART:" & Year(Event::start) & Month(Event::start) & Day(Event::start) & ¶ & "DTEND:" & Year(Event::end) & Month(Event::end) & Day(Event::end) & ¶ & "SUMMARY:" & Event::Title & "¶END:VEVENT ¶END:VCALENDAR"]
• Set Variable [$path ; Get(TemporaryPath) & “event.ics”]
• Export Field Contents [Event::g_Data ; “$path” ; Automatically Open]

This is a simple way of creating the event, and with a little research, you will find that there is a good deal that can be added to the Event using simple coding techniques.

Resources:

• How to create a .ICS file with multiple VEVENTs to be imported into an existing Outlook Calendar
• .ICS File extension