Import Calendar Items into FileMaker
Today I had a talk with a client who needed the following script to import events from a calendar. The idea is that he makes this appointment in the Calendar app on the iPhone and later on the Mac he wants to import all new events:
# Calendar name/id is passed as parameter
Set Variable [ $calendar ; Value: Get(ScriptParameter) ]
If [ $calendar = "" ]
Exit Script [ Text Result: ]
End If
# Date Range fixed
// Set Variable [ $startTimestamp ; Value: Timestamp(Date ( 1 ; 1 ; 2015 ); Time ( 0 ; 0 ; 0 )) ]
// Set Variable [ $endTimestamp ; Value: Timestamp(Date ( 7 ; 1 ; 2012 ); Time ( 0 ; 0 ; 0 )) ]
# Date Range relative to today and three years back
Set Variable [ $endTimestamp ; Value: Get(CurrentTimestamp) ]
Set Variable [ $startTimestamp ; Value: $endTimeStamp - 365 * 3 * 24 * 60 * 60 ]
# Query events
Set Variable [ $events ; Value: MBS("Calendar.Events"; $startTimeStamp; $endTimeStamp; $calendar) ]
If [ MBS("IsError") = 0 ]
# go to the layout
Go to Layout [ “Shoot” (Shoot) ; Animation: None ]
# loop over events
Set Variable [ $count ; Value: ValueCount ( $events ) ]
Set Variable [ $index ; Value: 1 ]
Set Variable [ $RecordsCreated ; Value: 0 ]
Loop
Set Variable [ $id ; Value: GetValue($events; $index) ]
Set Variable [ $uid ; Value: MBS("Calendar.Item.GetUID"; $id) ]
# Check for duplicates
If [ MBS("FM.ExecuteFileSQL"; ""; "SELECT count(*) FROM Shoot WHERE CalendarItemID=?"; 9; 13; $UID) ≠ 1 ]
# New ID, so create record
New Record/Request
Set Field [ Shoot::Date ; GetAsDate(MBS("Calendar.Item.GetStartDate"; $id)) ]
Set Field [ Shoot::Time ; GetAsTime(MBS("Calendar.Item.GetStartDate"; $id)) ]
Set Field [ Shoot::Location ; MBS("Calendar.Item.GetLocation"; $id) ]
Set Field [ Shoot::Event ; MBS("Calendar.Item.GetTitle"; $id) ]
Set Field [ Shoot::CalendarItemID ; $uid ]
Commit Records/Requests [ With dialog: Off ]
Set Variable [ $RecordsCreated ; Value: $RecordsCreated + 1 ]
End If
# next
Set Variable [ $index ; Value: $index + 1 ]
Exit Loop If [ $index > $count ]
End Loop
Show Custom Dialog [ "Done" ; $RecordsCreated & " of " & $count & " items imported for " & $calendar ]
End If
As you see, we got a duplicate check via SQL to skip all IDs we had before. Of course you can change it to synchronize in one direction if needed.
Using MBS FileMaker Plugin you can import, create and modify calendar items and reminders. Please note, that we have two ways to do this: Calendar functions works for 32 and 64-bit macOS, so you can use it in FileMaker 12 (32-bit) for example. The newer Events functions are for 64-bit macOS and for iOS SDK and should be preferred for new projects unless you need older FileMaker versions.