0 Replies Latest reply on Oct 24, 2012 7:15 AM by MikeBradshaw

    Creating an iCal Event from FM Data

    MikeBradshaw

      Title

      Creating an iCal Event from FM Data

      Post

           I spent yesterday scratching my head and searching for a way to address an issue that I had. 

           I have a database used for order processing. I wanted to create events in iCal related to invoice due dates. I found a few 'paid for' solutions which were really overkill for what I wanted.

           Basically I have a record which has fields like the Invoice Number, Customer, Amount Due, Date Due etc. Everything that I need to create a meaningful event.

           I found a Post in another forum which showed how to use a field to prepare text for exporting to an .ics file which iCal should then be able to read. I foillowed this but could not get iCal to read the file, even though it looked correct. It turns out that FM exports the file using UTF-16 whereas iCal wants UTF-8. There is a really easy way to make the resulting text file UTF-8 using an AppleScript. 

           As I spent so much time yesterday and have gained so much knowledge from this forum I thought it only fair to share, so here goes.

           In the relevant FM table create a text field (mine is called g_event). I only wanted a reminder so entered a 10 minutes event from 09:30 to 09:40 - if you want to use other fields or different time feel free :-). You then create a script as follows - replacing text in italics with text or fields as required:

           ----

           Set Field [TableName::g_event; BEGIN:VCALENDAR¶VERSION:2.0¶PRODID:-//Apple Inc.//iCal 5.0.3//EN¶BEGIN:VEVENT¶UID:" & some field(s) that will make the event unique otherwise each time you run the script it willl overwrite the previous event & "@domain.com¶SUMMARY:EventName " & with any fields that make it meaningful  & " any other text to complete the even name - this can be modified it is just here to help with the definition" & "¶ORGANIZER:Filemaker¶DTSTAMP:" & Year (Get(CurrentDate)) & Right ("00"&Month (Get(CurrentDate));2) & Right ("00"&Day (Get(CurrentDate));2) & "T" & Right("00"&Hour (Get(CurrentTime));2) & Right("00"&Minute (Get(CurrentTime));2) & Right("00"&Seconds (Get(CurrentTime));2) &"Z¶DTSTART:" & Year ( fieldname that stores the actual date you want to use for event) & Right("00"&Month ( fieldname that stores the actual date you want to use for event);2) & Right("00"&Day ( fieldname that stores the actual date you want to use for event );2) & "T093000Z¶DTEND:" & Year ( fieldname that stores the actual date you want to use for event ) & Right("00"&Month ( fieldname that stores the actual date you want to use for event);2) & Right("00"&Day ( fieldname that stores the actual date you want to use for event );2) & "T094000Z¶TRANSP:TRANSPARENT¶END:VEVENT¶END:VCALENDAR"

           Copy [Select; TableName::g_event]

           Perform AppleScript ["do shell script "pbpaste > ~/Desktop/FileName.ics" "]

           Perform AppleScript ["set dstfile to "~/desktop/FileName.ics" do shell script "open" & dstfile & " "]

           -----

           And that is it!

           There is a lot of 'padding' around the date and time as iCal needs the format yyyymmddThhmmssZ and if you don't use the padding you can get single digit months, days, hours etc. If you replace the 093000 with a field do not forget you will need to pad that too.

           Explanation of the Script:

           So the first part (SetField) prepares the text for the ics file which iCal will then read. The Copy/Paste command takes the text that you have prepared and then pastes it into a file that will be encoded using UTF-8. The last AppleScript then opens the file which triggers iCal lto ask if you want to import the event.

           I am sure there is much more that you can do to automatically select which calendar etc, but this simple script does a great job, simply - and it doesn't require any plugins or XSL templates etc.

           Hope it helps.

           Mike