FileMaker to iCal

[Kevin Cheesman]

Is there a way to export a set of records to iCal?

[Josh Ormond]

I have never done much with iCal...but I remember coming across this some time last year.

http://developer.filemaker.com/solutions/detail/?item=solution.10000000471&web=

[Lee Smith]

There is a way to do this, check out the downloads at www.seedcode.com

Lee

[gephry]

You can export directly to iCal or to a file (*.ics) file. I've been doing the .ics file for a long time due to the easier synchronization of event updating by ID. I haven't figured out how to properly UPDATE an already existing FM event in iCal through direct iCal manipulation with Applescript....even when you have the proper iCal UID. So I've been sticking to the *.ics file.

A while ago I ended up reverse engineering iCal Calendar exports. Here's the basics of a .ics file:

START CALENDAR

CALENDAR ENTRY 1

CALENDAR ENTRY 2

CALENDAR ENTRY 3

CALENDAR ENTRY 4

etc...

END CALENDAR

For the START CALENDAR, the basic format is:

BEGIN:VCALENDAR

VERSION:2.0

PRODID:-//Apple Computer, Inc//iCal 2.0//EN

X-WR-RELCALID:82E4B0D7-9C52-4B62-BA31-AEE2A7DF0BE4

X-WR-TIMEZONE:US/Eastern

CALSCALE:GREGORIAN

METHOD:PUBLISH

BEGIN:VTIMEZONE

TZID:America/New_York

LAST-MODIFIED:20070502T145341Z

BEGIN:DAYLIGHT

DTSTART:20050403T070000

TZOFFSETTO:-0400

TZOFFSETFROM:+0000

TZNAME:EDT

END:DAYLIGHT

BEGIN:STANDARD

DTSTART:20051030T020000

TZOFFSETTO:-0500

TZOFFSETFROM:-0400

TZNAME:EST

END:STANDARD

END:VTIMEZONE

Naturally you'd change EST to Mountain or PST or whatever timezone you're in, etc.



For the entries that appear in the middle I created a custom function to compile record data into an iCal format.  NOTE:  This function creates a somewhat random UID that iCal normally makes.  The UID is dependent on your record serial number (aka record ID).  The UID will allow you to update events rather than adding duplicates later.



Export_to_iCal ( Category ; DateStart ; TimeStart ; DateEnd ; TimeEnd ; AllDay ; Title ; Location ; Description ; ContactName ; ContactEmail ; DateOfEntry ; RecordSerialNumber )

Let([

DOE = If( IsEmpty( DateOfEntry ); Get( CurrentDate ); DateOfEntry );

Date_End_MOD = If( IsEmpty( DateEnd ); DateStart; DateEnd )

];



If( not( IsEmpty( DateStart ) or IsEmpty( TimeStart ) or IsEmpty( TimeEnd ) );



"BEGIN:VEVENT" & ¶ & "

DTSTAMP:" & 



Year( DOE ) & If( Month( DOE ) < 10; "0"; "" ) & Month( DOE ) & If( Day( DOE ) < 10; "0"; "" ) & Day( DOE ) & "T120000Z" & ¶ & "

X-WR-CALNAME:" & 

If( Category = ""; "Unfiled"; Category ) & "¶" &

"UID:" & 



RecordSerialNumber & Right( "EINWODKN"; 8 - Length( RecordSerialNumber ) ) & "-BABA-411E-8635-" & RecordSerialNumber & Right( "5606E0131B22"; 12 - Length( RecordSerialNumber ) ) & ¶ & 



"SEQUENCE:4" & ¶ & "

STATUS:CONFIRMED" & ¶ & "

DTSTART;TZID=America/New_York:" & 



Year( DateStart ) & If( Month( DateStart ) < 10; "0"; "" ) & Month( DateStart ) & If( Day( DateStart ) < 10; "0"; "" ) & Day( DateStart ) & 



If( AllDay = "Yes" or AllDay = "All Day"; ""; "T" & If( Length( Hour( TimeStart ) ) = 1; "0"; "" ) & Hour( TimeStart ) & If( Length( Minute( TimeStart ) ) = 1; "0"; "" ) & Minute( TimeStart ) & "00" ) & ¶ & 



"SUMMARY:" & Title & ¶ & 



"LOCATION:" & Location & ¶ & "

ATTENDEE;CN="" & ContactName & ""mailto:" & ContactEmail & ¶ & "

DTEND;TZID=America/New_York:" & 



Year( DateEnd ) & If( Month( DateEnd ) < 10; "0"; "" ) & Month( DateEnd ) & If( Day( DateEnd ) < 10; "0"; "" ) & Day( DateEnd ) & 



If( AllDay = "Yes" or AllDay = "All Day"; ""; "T" & If( Length( Hour( TimeEnd ) ) = 1; "0"; "" ) & Hour( TimeEnd ) & If( Length( Minute( TimeEnd ) ) = 1; "0"; "" ) & Minute( TimeEnd ) & "00" ) & ¶ & 



"DESCRIPTION:" & Substitute( Description; "¶"; "n" ) & ¶ & 

"END:VEVENT" & ¶;

"" )

)

Then I use Applescript to copy the values out of the Global fields to an iCal.ics file on the desktop:

tell application "FileMaker Pro Advanced"

	copy cell "ECal::g_iCal_Title" of current record to fileName

	copy cell "ECal::gJewel_Holder" of current record to exportMe

	do shell script "echo -n " & quoted form of exportMe & " > ~/Desktop/" & quoted form of fileName

	--  -n prevents a final line feed from being added

end tell

And then the very end of a *.ics file is very simple; it's just:

END:VCALENDAR

Again, this is my manipulation of iCal *.ics files for my use in a basic FM calendar. It's not 100% complete/full-featured but it gets the job done for me pretty well.

Hope it helps!

Edited August 29, 2008 by Guest

[Kevin Cheesman]

Hi All,

Thanks for all that!

Kevin

[Breezer]

This looks great. Has anyone done this for Windows?