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:
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: