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

    Work Shifts Calender

    NilsMortenAlexandersen

      Title

      Work Shifts Calender

      Post


           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
          philmodjunk

               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]
                 Loop
                    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
            NilsMortenAlexandersen

                 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
              philmodjunk

                   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
                davidanders

                A free calendar is available and can be integrated into your database
                     Seedcode Calendar Free -
                     http://seedcode.com/cp-app/prod/calendarfree10/

                     Video about the Free Calendar
                http://www.youtube.com/watch?v=sVgk6NSWah8

                • 5. Re: Work Shifts Calender
                  Sorbsbuster

                       And some years have 53 weeks.

                  • 6. Re: Work Shifts Calender
                    NilsMortenAlexandersen

                         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 ? :-)

                         Nils

                    • 7. Re: Work Shifts Calender
                      Sorbsbuster

                           That sounds pretty much like the ISO Week standard.

                           You may find these links useful:

                           http://help.filemaker.com/app/answers/detail/a_id/3160/kw/ISO

                           http://www.briandunning.com/cf/147

                           http://www.briandunning.com/cf/148

                            

                           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
                        NilsMortenAlexandersen

                             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 ;-)