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.
Sample data – FileMaker Developer Conference 2014
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
If you don’t have a UUID field already, create one
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
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
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?
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
Address and send.
Adding the events to your Calendar application
Select the appropriate Calendar to import the event(s) to.
Events in Calendar
.ics file events displayed in Apple’s Calendar file.
New feature in FileMaker Pro 13 – the ability to calculate UTC offset
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
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.