« Xojo Optimization | Home | MBS Xojo / Real Studi… »

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

 
If you find a bug, please tell me. Please comment and use it in your solutions.
22 09 16 - 19:11