8 Replies Latest reply on Oct 16, 2012 2:11 AM by NilsMortenAlexandersen

    Work Shifts Calender



      Work Shifts Calender


           Good Morning

           I have used the FM Tasks DB as a template for a Task Management system for my workplace. I want to include the ability to create a Work Shifts list (not sure if my translation is correct here)

           Let me explain ...

           I am the leadere of a small workgroup with 7 people, that does IT support for a Video Production system in the News department of a national broadcaster. We work shifts (one week each). All members are already been registered in the Tasks DB Contacts Table

           What is the best way to set up a DB that does the following

      •           on January the 1th. each year automatically creates 53 new posts (one for each week of the year) for the next year, so that the Shifts calender can be prepeared in advance ...
                  Each post must include Year, Week Nr., Name and Replacement fields. The Year and Week nr. fields must be automatically filled with data.
      •           Let's me create 53 posts for a spesific year, to maintain a history record of Shifts
                  We have old shifts lists that we want to include, so that management can check for awho was in charge on a spesific week on a spesific year


           I hope someone can help me


           Cheers    Nils ...

        • 1. Re: Work Shifts Calender

               By "posts" I must assume you mean Records.

               The following script checks for the existance of records with the current date's year and creates 53 records if no such records are found:

               Enter Find Mode[] ---clear the pause check box
               Set Field[Shifts::Year ; Year ( get ( CurrentDate ) ) ]
               Set Error Capture [on] ---keeps "no records found" dialog from interrupting the script
               Perform Find []
               If [Not Get ( FoundCount ) // no records with this year were found]
                    Set Variable [$Week ; value: $Week + 1 ]
                    Exit Loop If [$Week > 53 ]
                    New Record/Request
                    Set Field [Shifts::Week ; $Week]
                    Set Field [Shifts::Year ; Year ( Get ( CurrentDate ) ) ]
                  End Loop
               End If

               You can use FileOptions to set this script to run each time that the database is opened, of if you are using FileMaker Server to host the file, you can set it to run once a day with a server schedule.

          • 2. Re: Work Shifts Calender

                 Hi again PhilModJunk

                 I have implemented your script, and it works very well. I have limited the week count to 52 ... (my mistake :-)

                 I wonder if you could help med with extending the functionality a little bit :-)

                 Is it possible to extend the script so that it also ..

            •           Auto enter the actual date for Monday of each week in a START DATE field ?


                 Hope it's possible

                 Still on FMP10   Nils


            • 3. Re: Work Shifts Calender

                   It can be done, but which Monday will be the first Monday of the year?

                   A week can begin in December 2012 and end in January 2013--which may require that extra week, BTW.

                   For such a week, is it a week in 2012 or a week in 2013?

              • 4. Re: Work Shifts Calender

                A free calendar is available and can be integrated into your database
                     Seedcode Calendar Free -

                     Video about the Free Calendar

                • 5. Re: Work Shifts Calender

                       And some years have 53 weeks.

                  • 6. Re: Work Shifts Calender

                         Hello guys :-)

                         I agree that this is tricky. From what I understand ... the rules are as follows (keep in mind that norwegian weeks starts at Mondays)

                         in a given year ... if at the end of week 52 ... there are still 4 or more days left of the year .... this year gets a week 53 (because a week is defined as 4 or more days) ...... 2009 is such an example.

                         If there is 3 days or less left of the year at the end of week 52, we go to week 1.


                         Is this enough to make up the math ? :-)


                    • 7. Re: Work Shifts Calender

                           That sounds pretty much like the ISO Week standard.

                           You may find these links useful:





                           Or try this calculation:

                           Substitute the correct date field for MyDateField in the Let function

                           Let (

                           [ Date = MyDateField ] ;

                           If (

                           WeekOfYearFiscal ( Date ; 2) > 51 and Month ( Date ) = 1 ;

                           Year ( Date ) - 1 ;

                           If ( WeekOfYearFiscal ( Date ; 2) = 1 and Month( Date ) = 12 ; Year( Date ) + 1; Year( Date ) )


                           & "-" &

                           Right ( "0" & WeekOfYearFiscal ( Date ; 2 ) ; 2 )


                      • 8. Re: Work Shifts Calender

                             Thanks for the amazing Script.

                             If I have done it correctly, the readout is (in text format) year-week (2012-43)

                             The script provided by PhilModJunk autocreates records with Year and Week numbers for me (fantastic)

                             My goal is to also Auto Create proper Dates into a START DATE field for each monday of each week created.


                             So .. still hopelessly stuck on how to embed such commands into PhilModJunks amazing script ;-)


                             Nils .... more confused than ever ;-)