Todo/Reminder Synchronization for FileMaker on Mac using MBS Plugin
Do you have a need to synchronize Todos from your FileMaker database to the Mac, iPad or iPhone?
Today I have a script which may serve as an example on how to synchronize from a table in FileMaker with Todos to the Reminders app on a Mac. Those reminders can than be sychronized via iCloud to your iPhones and iPads. For a Server based solution, you need a Mac (a robot) doing this with FM Pro and share reminder lists from there to your devices.
The script needs to be compatible for us with FileMaker 13, so we use the older Calendar API. For newer projects with 64-bit only (and possibly iOS), you prefer the newer Events API. Be aware that in the older Calendar API in newer OS X versions the DueDate is no longer a timestamp, but just a date. The Events API can do it correctly and use a timestamp. FileMaker 15 is 64-bit only, so you can use both function sets, but please decide which is better for you.
The script can be run regularly, e.g. via Schedule functions. It loops over current set of records. You may want to limit the records to only the ToDos for the next three weeks. If there is an UID saved for the todo, the reminder is checked. If it exists and is newer, we sync back title, notes and completed flag. You can add more fields. A special case here is to allow user to store in notes how long it took to do the job. That value is stored in a field in FileMaker. If the event is newer in FileMaker, we overwrite all values. If we have no UID, we create a new reminder in the right reminder list (calendar). How you map your priorities and completed state, is of course dependent to your database. But please save the UID for later sychronization.
If you build a solution with this scirpt please edit it. Change all fields to your field names and add some more error checking. You may want to implement slightly different rules for synchronization, especially on who wins in case of conflicts.
# Sync ToDos as Reminders from FileMaker to Mac Reminder app# And sync back changes
#
# Please adjust to your fields and your special use case!
# You can optimize to only update fields on each side if changed
#
Go to Record/Request/Page [First]
Loop
# Check if we have an ID for this todo already
If [ToDos::SyncUID ≠ ""]
Set Variable [$c; Value:ToDos::SyncUID]
#Query last modification date
Set Variable [$completed; Value:MBS( "Calendar.Item.GetCompleted"; ToDos::SyncUID )]
Set Variable [$datum; Value:MBS( "Calendar.Item.GetDateStamp"; ToDos::SyncUID )]
If [MBS("isError")]
# Reminder was deleted, so we recreate
Set Field [ToDos::SyncUID; ""]
Commit Records/Requests [No dialog]
Else
#Update
If [$datum > ToDos::Last_Change]
#
#Reminder -> FM
#
#Sync note field
Set Variable [$notes; Value:MBS( "Calendar.Item.GetNotes"; ToDos::SyncUID )]
If [ToDos::To Do Main Text ≠ $notes]
#Special: If note starts with # then move number to duration field
If [Left ( Trim ( $notes ); 1) = "#"]
Set Variable [$duration; Value:GetAsNumber ( LeftWords ( $notes ; 1 ))]
Set Field [ToDos::To Do Completion Time in Minutes; $duration]
Commit Records/Requests [No dialog]
Else
Set Field [ToDos::To Do Main Text; $notes]
Commit Records/Requests [No dialog]
End If
End If
#Sync title if changed
Set Variable [$title; Value:MBS( "Calendar.Item.GetTitle"; ToDos::SyncUID )]
If [ToDos::To Do Headline ≠ $title]
Set Field [ToDos::To Do Headline; $title]
Commit Records/Requests [No dialog]
End If
#Sync completed flag if changed
If [$completed=1]
If [ToDos::flag To Do Completed ≠ "X"]
Set Field [ToDos::flag To Do Completed; "X"]
Commit Records/Requests [No dialog]
End If
Else If [$completed=0]
If [ToDos::flag To Do Completed ≠ ""]
Set Field [ToDos::flag To Do Completed; ""]
Commit Records/Requests [No dialog]
End If
End If
Else
#
# FM -> Reminder
#
# overwrite all values
Set Variable [$r; Value:MBS("Calendar.Item.SetTitle"; $c; ToDos::To Do Headline)]
Set Variable [$r; Value:MBS("Calendar.Item.SetNotes"; $c; ToDos::To Do Main Text)]
Set Variable [$r; Value:MBS("Calendar.Item.SetDueDate"; $c; ToDos::To Do Deadline )]
If [ToDos::flag To Do Completed = "X"]
Set Variable [$r; Value:MBS("Calendar.Item.SetCompleted"; $c; 1)]
End If
If [ToDos::flag To Do Completed = ""]
Set Variable [$r; Value:MBS("Calendar.Item.SetCompleted"; $c; 0)]
End If
If [ToDos::To Do Priority="1 Jetzt"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;1 )]
End If
If [ToDos::To Do Priority="2 Sofort"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;5 )]
End If
If [ToDos::To Do Priority="4 Hat Zeit"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;9 )]
End If
Set Variable [$r; Value:MBS("Calendar.SaveReminder"; $c )]
End If
End If
End If
If [ToDos::SyncUID = ""]
# Create new reminder
#
# Which calendar? (Reminder List)
Set Variable [$CalenderName; Value:""]
If [ToDos::To Do By="PF"]
Set Variable [$CalenderName; Value:"ToDo PF"]
Else If [ToDos::To Do By = "GK"]
Set Variable [$CalenderName; Value:"ToDo GK"]
End If
If [$CalenderName ≠ ""]
Set Variable [$c; Value:MBS("Calendar.NewReminder")]
Set Variable [$r; Value:MBS("Calendar.Item.SetTitle"; $c; ToDos::To Do Headline)]
Set Variable [$r; Value:MBS("Calendar.Item.SetNotes"; $c; ToDos::To Do Main Text)]
Set Variable [$r; Value:MBS("Calendar.Item.SetCalendar"; $c;$CalenderName)]
Set Variable [$r; Value:MBS("Calendar.Item.SetDueDate"; $c; ToDos::To Do Deadline )]
If [ToDos::To Do Priority="1 Jetzt"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;1 )]
End If
If [ToDos::To Do Priority="2 Sofort"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;5 )]
End If
If [ToDos::To Do Priority="4 Hat Zeit"]
Set Variable [$r; Value:MBS("Calendar.Item.SetPriority"; $c;9 )]
End If
If [ToDos::flag To Do Completed = "X"]
Set Variable [$r; Value:MBS("Calendar.Item.SetCompleted"; $c; 1)]
End If
If [ToDos::flag To Do Completed = ""]
Set Variable [$r; Value:MBS("Calendar.Item.SetCompleted"; $c; 0)]
End If
Set Variable [$r; Value:MBS("Calendar.SaveReminder"; $c )]
#store id for update later
Set Field [ToDos::SyncUID; MBS("Calendar.Item.GetUID"; $c)]
Commit Records/Requests [No dialog]
End If
End If
Go to Record/Request/Page [Next; Exit after last]
End Loop
