Exporting Calendar files (.ics) from FileMaker Pro 13


2017-04-23 Update: Radical Application Development’s page has changed, so here is another link that describes the Base64Encode/Decode technique: FileMaker Hacks.

2014-05-22 Update: If you have problems with the import of .ics events into your calendar, please see the next blog posting on Exporting Calendar files using the Virtual List Technique.

 

Update 2015-11-25: An even easier method of creating vCards that seem to work cross platform is demonstrated by Joe Simpson of Radical Application Development. He uses Base64Encode and then Base64Decode to generate the file in a container field. The same approach should work with .ics files. Then it is a simple matter to export it. Check it out here and see if it meets your needs. Might be even easier to implement than the Virtual List approach below.


I have been working on an event-based database project and my client was requesting some way to get the events to their Calendar application. I looked around and found the basics of how to do this, generating the required text for a calendar .ics file. I also found a good article on FileMaker Hacks that suggested employing a Virtual List, to deal with exporting text file issues in the .fmp7 format. With FileMaker Pro 13, there are some new capabilities — Get(UUID) and Get(CurrentTimeUTCMilliseconds) — and some older file formatting issues seem to have gone away.

I decided to write my own, portable script for exporting .ics calendar files from FileMaker Pro 13 databases and thought I would share it here for others who may find this useful. I built my script using ideas from the above resources. This script should give you the ability to export events from FileMaker Pro 13 into Apple Calendar, BusyCal, Google Calendar, Outlook etc.

To add this capability to your own solution,

1)  you will need a copy of FileMaker Pro 13.

2) In addition, these Required Fields for an event:

  • Start Date – Date field
  • Start Time – Time field
  • End Time – Time field
  • Title – Text field
  • UUID field – Get(UUID) is a new FileMaker Pro 13 feature
  •  gExport field (Global Storage)

In addition, you can include these Optional Fields:

  • Location – Text field
  • Description – Text field

3) Once you have the required fields in place, to utilize this script in your own solution, simply copy the script called ‘Export Calendar Files’ and paste it into your file.

4) Open the script and edit the portion of the script where the required fields are referenced to match those in your solution.

5) Edit the gExport field near the end of the script.

Download Demo File
Zip File – Mac/Windows
.FMP12 File – Direct download to iPhone/iPad

Sample data – FileMaker Developer Conference 2014

media_1400130880945.png

I imported the events from the FileMaker Developer Conference 2014 into my demo database. To test the demo, launch the file in FileMaker Pro 13 and click the ‘Export Calendar’ button. You may want to select the ‘One’ record option, or select a small subset of the data, rather than clicking the ‘Batch’ option. Otherwise, you may end up cleaning out 83 records from your Calendar file.

Copy this script

media_1400131594200.png

If you don’t have a UUID field already, create one

media_1400132450446.png

The benefit of employing a UUID is that you can update your calendar event. The .ics format uses a UUID to identify each unique event record. If something changes in FileMaker, you simply re-export that event and your calendar application should recognize the UUID and update the event.

If you are attempting to recreate this script in FileMaker Pro 12, you will need to use a Custom Function to generate a UUID. There are a few listed on Brian Dunning’s Custom Function web site.

Edit these fields

media_1400131572863.png

Edit the Required and Optional fields portion of the script, replacing the fields from the script with the appropriate matching event fields in your own database.

Test the script – click ‘Export Calendar’ button

media_1400130931601.png

You will be prompted to select either export the Current Record, or the Found Set. Again, when testing, use the ‘One’ option, or select a smaller sub-set of records to avoid having to clean up a large batch of records.

Open the File or Email it?

media_1400130940591.png

Next, you will see a dialog box asking to Open or Email the file. If you click ‘Open’, the file will attempt to import the selected calendar event records into your default calendar application. If you click Email, an email will be generated in your default mail client with the .ics file attached.

If you are on an iOS device (iPhone or iPad), I recommend that you email the file to yourself, because in FileMaker Go 13, although you can save the .ics file to the FileMaker Go Documents folder and cause it to auto-open, there does not seem to be the ability to add the calendar event directly to your calendar app.  This is a strange oversight by Apple (or possibly FileMaker?) that the .ics file doesn’t automatically bring up the option of importing to the iOS Calendar file.  If instead you send it by email to yourself, you can then add it to your calendar from the Mail client.

Emailing the file

media_1400130964195.png

Address and send.

Adding the events to your Calendar application

media_1400130996175.png

Select the appropriate Calendar to import the event(s) to.

Events in Calendar

media_1400131070188.png

.ics file events displayed in Apple’s Calendar file.

New feature in FileMaker Pro 13 – the ability to calculate UTC offset

media_1400131877106.png

FileMaker Pro 13 has a new Function called Get(CurrentTimeUTCMilliseconds), which gives you GMT. Subtracting this time from your CurrentTimeStamp gives you the UTC offset required to schedule your .ics event correctly.

If you were to redo this file in FileMaker Pro 12, you would probably have to use a global field to set your utc_offset, or hard code it into the script. If all your events occur in a single time zone, which never changes, you could consider hard coding the utc_offset into the script, but using this approach, I believe solves issues with Daylight Savings Time.

Thanks to Jeremy Bante who posted his UTCOffset Custom Function to Brian Dunning’s Custom Function collection. The only modification I added was to divide by 3600 seconds to get the offset in hours. UTCOffset returns the offset in seconds.

Customize and Improve

ics-file-in-text-editorAbove is a single event in a .ics file open in a text editor.

This demo is offered freely for your use and customization. HomeBase Software takes no responsibility for its accuracy or completeness. Please do your own testing.

I tested the file on Mac, iOS and Windows (through Parallels) and had good results. I haven’t moved through different time zones, (or Daylight Savings Time changes) so I am not sure what will happen there, but hopefully, because of the dynamic calculation of the time zone, the dates and time for the event should dynamically update.

Ideas for improvements or changes:

  • To avoid littering your Desktop with .ics files, modify the Path to save the calendar .ics file in your Temporary folder  — Get(TemporaryFolder)
  • Re-do the file for FMP12, replacing Get(UUID) with a Custom Function and hard coding the UTC offset number, rather than calculating it.
  • Re-create the script for FileMaker 11 and earlier by employing the Virtual List technique suggested by Kevin Franks in FileMaker Hacks.
  • Add a recipient email address for each record and email the updated file automatically
  • Investigate the .ics file format and add more features, such as Calendar, Alarms, Travel Time etc.

I believe that the script is sufficiently commented that you should be able to quickly find the area you would like to change and make your own. Thanks to the Internet, Google and the FileMaker development community specifically for the ideas contained herein. Let me know if you find any bugs or have suggestions for improving the demo file.

26 Responses to “Exporting Calendar files (.ics) from FileMaker Pro 13”

  1. Thanks Douglas,

    Nice work !

  2. Jens Rasmussen May 22, 2014 at 1:05 am

    Nice.
    However, a test record fails to import into my calendar (version 6.0, OS X 10.8.5). Looking at the files in TextWrangler, I can see that the issue seems to be escaping of commas in the DESCRIPTION section.

    • Turns out the UTF-8 issue is still a problem with anything less than Mavericks. It appears that Mavericks Calendar can import the UTF-16 file that FileMaker Pro is exporting. Earlier versions of Calendar need UTF-8. I will be posting an update shortly with the Virtual List technique method.

      • Tobias Sjögren May 28, 2014 at 2:26 am

        I’m too having the UTF-16 problem when outputting from FM – so what is the “Virtual List technique”?
        I’ve made an AppleScript some time ago to convert a text file from UTF-8 into UTF-16 and I’m thinking I should do it in the reverse direction to solve this, but maybe I should take a look at the Virtual List thing you’re talking about?…

      • Tobias Sjögren May 28, 2014 at 8:04 am

        I got the Virtual List thing working now. Though, everything after a comma in the Location field doesn’t make it to Calendar (I’m on 10.8.5) – I need to write a backslash before the comma to have it function in Calendar. Maybe the FM script should substitute every comma with a backslash plus a comma then?

      • @Tobias : look at http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/ or search Google for “Filemaker virtual list”

        Have a great afternoon !

  3. Thank you for sharing this information.

  4. T we can synchronize the calendar with google calendar

  5. Thank you so much for posting this elegant solution and sample file. You’ve saved me a lot of work, and I really appreciate that. 🙂 The only thing missing from the instructions above is that one must create a gExport field (Global Storage) and then edit the script for every instance where gExport is referenced. I am deeply grateful for your work here.

  6. hi and thanks for the software, i have one question though ? How can i make all day events? i know your drop downs are time based but there is no option for all day.
    The reason is when you just put in times it shows up nice big blocks in week views but in month views it only shows as a dot . Unless you make an ALL DAY entry then you get a colour code bar. Its hard to see entries if you have many people on the same calendar. Any thought greatly appreciated.
    Thanks

    Paul

    • Hi Paul. Here are the steps I would take to find how to do this: Create an all day event in your Calendar. Export it as an ics file. Open that file with a text editor. Find the text that makes it an all day event. Add that text to the export script.

  7. Hi Douglas,.

    this really looks amazing, but I have a little problem here.
    The demo file creates an appointment in iCal at 17:30. So far so good, but the inside off the appointment says starttime 15:30.
    How can I change this? Is it the local time here in Holland?

    • See the first blog post about exporting calendar files. There is a discussion there about the time zone calculation. You must be running FileMaker Pro 13 or higher. If it still doesn’t work you could try hard coding the time zone offset.

  8. Hello , first of all good work and congratulations . My only issue is that I do not save events in Ical with filemaker Go. There would be some solution for this?

  9. Just wondering if you’ve found a solution to the .ics file not being able to create the appointment on FileMaker Go?

    • Because of iOS’s sandboxing, it is tricky getting files from one app to another. The only solution I came up with is in the reply right above: Email the ics file to yourself. Then tap on the .ics file in the email message and at the bottom of the screen tap on the ‘Open in Calendar’ button.

  10. I’m trying to set this up in an existing database. Your sample works perfectly on my computer and the server. I am missing something in implementing it to an existing database I have on the server. I am well, I guess intermediate at scripting. Not a programmer background.

    I get an error that says no fields were selected for export when it gets to the export step. I could not find where the gExport field is referred to in the script. I’m not sure if that has anything to do with it? I’ve gone through debugger and everything appears to function identical to your database up to the point of exporting.

    Also, I’m CST and your times come in an hour off. We are no longer on Daylight savings. I adjusted the calculation to fix that for now at least, but I probably need to go look at that more closely. (I adjusted 3600 to 3000, which works for now)

Trackbacks/Pingbacks

  1. Exporting Calendar files (.ics) from FileMaker Pro 13 | Filemaker Info - May 22, 2014

    […] See on hbase.net […]

  2. Export Calendar files from FileMaker Pro – Virtual List Technique | HomeBase Software - May 22, 2014

    […] my last post about exporting Calendar files, I glossed over the UTF-8/16 issue that is still present in FileMaker Pro. If a script employs […]

  3. How to Export Calendar Files from FileMaker 13 | Homebase Software - May 23, 2014

    […] via Exporting Calendar files (.ics) from FileMaker Pro 13 | HomeBase Software. […]

  4. Export Calendar files from FileMaker Pro - May 27, 2014

    […] my last post about exporting Calendar files, I glossed over the UTF-8/16 issue that is still present in FileMaker Pro. The problem is that […]