A frequently occurring problem is the repetition of appointments in Filemaker databases.
What is meant are appointments as already known from other calendar tools:
Repeats of the appointment – i.e. resubmissions in the future – can be assigned to an appointment.
The great Javascript FULLCALENDAR plugin by Adam Shaw has a very clever integration of such
recurrences, we will follow a much simpler approach here.
(By the way, in a future blog article I will show how to integrate this plugin into a Filemaker database).
To achieve a date recurrence in Filemaker you can choose different approaches,
here I use a multikey with a list of recurrences as the date, which for me is the simplest
and most universal solution to access or view the recurrences.
So let’s code.
We need a few fields, 3 custom functions and a script to enter the repetitions as a date list in a multikey field.
First the field list:
‚Date’ [Date] The date of the appointment
‚Recurrence’ [Text] Here you can choose the recurrence from options ( None/Daily/Weekly/Monthly/Yearly/Customized)
‚Recurrence’ [Text] Here the recurrences are stored as a date list.
‚Recurrence_End’ [Date] The end date of the recurrences.
‚Recurrence_repeatingDays’ [Number] Every how many days the recurrence should occur again.
‚Recurrence_Error’ [Calculation] shows an error message if necessary
Next the 3 custom functions:
The customfunctions calculate recusively with a while function the next date on which the appointment would appear again and checks if this date is before the end date. Then it is added to the list of recurrence dates.
Not very complicated, but a bit fiddly 🙂
Recurrence_Year
// Recurrence_Year ( Start ; End )
While (
[
out = "" ;
i = 1 ;
t = Day ( Start ) ;
m = Month ( Start ) ;
j = Year ( Start ) ;
d = Start
] ;
Date ( m; t ; j+i ) ≤ End ;
[
d = Date ( m ; t ; j+i ) ;
out = List ( out ; d ) ;
i = i + 1
] ;
out
)
Recurrence_Month
// Recurrence_Month ( Start ; End )
While (
[
out = "" ;
i = 1 ;
t = Day ( Start ) ;
m = Month ( Start ) ;
j = Year ( Start ) ;
d = Start
] ;
If ( Month (Date ( m +i ; t ; j ) ) > m+i ; Date ( m +i +1; 1 ; j ) -1 ; Date ( m +i ; t ; j ) ) ≤ End ;
[
d = If ( Month (Date ( m +i ; t ; j ) ) > m+i ; Date ( m +i +1; 1 ; j ) -1 ; Date ( m +i ; t ; j ) );
out = List ( out ; d ) ;
i = i + 1
] ;
out
)
Recurrence_Days
// Recurrence_Days ( Start ; End ; Days )
While (
[
out = "" ;
i = 0
] ;
Start + i + Days ≤ End ;
[
i = i + Days ;
out = List ( out ; Start + i )
] ;
out
)
For monthly and yearly recurrence I used a separate function, we call the ‘Recurrence_Days’ function with 1 for daily, with 7 for weekly and with the field content (Customized recurrence).
Last, the relatively simple script:
Trigger Recurrence
If [ Recurrence::Recurrence = "None" or Recurrence::Recurrence = "" ]
Set Field [ Recurrence::Recurrence_MK ; Recurrence::Date ]
Set Field [ Recurrence::Recurrence_repeatingDays ; "" ]
Set Field [ Recurrence::Recurrence_End ; "" ]
Exit Script [ Text Result: ]
End If
#
Set Variable [ $Dif ; Value: Left ( Recurrence::Recurrence ; 1 ) ]
#
If [ $Dif = "C" and Recurrence::Recurrence_repeatingDays ≠ "" and
Recurrence::Recurrence_End ≠ "" ]
Set Field [ Recurrence::Recurrence_MK ; List ( Recurrence::Date ;
Recurrence_Days ( Recurrence::Date ; Recurrence::Recurrence_End ;
Recurrence::Recurrence_repeatingDays ) ) ]
Exit Script [ Text Result: ]
End If
#
If [ $Dif = "D" and Recurrence::Recurrence_End ≠ "" ]
Set Field [ Recurrence::Recurrence_MK ; List ( Recurrence::Date ;
Recurrence_Days ( Recurrence::Date ; Recurrence::Recurrence_End ; 1 ) ) ]
Set Field [ Recurrence::Recurrence_repeatingDays ; "" ]
Exit Script [ Text Result: ]
End If
#
If [ $Dif = "W" and Recurrence::Recurrence_End ≠ "" ]
Set Field [ Recurrence::Recurrence_MK ; List ( Recurrence::Date ;
Recurrence_Days ( Recurrence::Date ; Recurrence::Recurrence_End ; 7 ) ) ]
Set Field [ Recurrence::Recurrence_repeatingDays ; "" ]
Exit Script [ Text Result: ]
End If
#
If [ $Dif = "M" and Recurrence::Recurrence_End ≠ "" ]
Set Field [ Recurrence::Recurrence_MK ; List ( Recurrence::Date ;
Recurrence_Month ( Recurrence::Date ; Recurrence::Recurrence_End ) ) ]
Set Field [ Recurrence::Recurrence_repeatingDays ; "" ]
Exit Script [ Text Result: ]
End If
#
If [ $Dif = "Y" and Recurrence::Recurrence_End ≠ "" ]
Set Field [ Recurrence::Recurrence_MK ; List ( Recurrence::Date ;
Recurrence_Year ( Recurrence::Date ; Recurrence::Recurrence_End ) ) ]
Set Field [ Recurrence::Recurrence_repeatingDays ; "" ]
Exit Script [ Text Result: ]
End If
#
Set Field [ Recurrence::Recurrence_MK ; "" ]
The script tests the first letter from the Recurrence option field and calls one of the custom functions depending on the ‘Daily/Weekly/Monthly/Yearly/Customized’ recurrence and then writes the date list into the Mulktikey field.
Not very exciting !
Now we bring it all together in a layout that provides a simple user interface with a few conditional formatting and ‘hide object‘ settings:
Now we can set the repetition defaults on the left side of the layout and see how the multikey fills up on the right side.
If you like to have an open version of the file, just send me an email.
You have any questions? Feel free to send me an email, I’ll try to answer as soon as possible.