3 Replies Latest reply on Oct 9, 2012 12:43 AM by Sorbsbuster

    Search for Date range



      Search for Date range


           Good morning

           I have created a DB, where the records contain Week and Year fields.

           Currently these records range from 2004 to 2013, and a seperate record for all week numbers for these years.

           I need to build a script that shows only the records that match the following

           Year >= Current Year
           Week >= Current Week

           I can't find a search setup that gives me this result .... using the search options i have for scripts

           Currently on FM 10


           anyone ? ;-)


           Cheers  Nils.

        • 1. Re: Search for Date range

               How did you calculate the week number?  Or is that just entered data - if so, what is the format?  (Filemaker has funny ideas about what the week number is.)

               In its simplest form you could use set the fields to be:
               ">=" & Year ( Get (CurrentDate ) )


               ">=" & WeekOfYear ( Get (CurrentDate ) )

               but I would use WeekOfYearFiscal ( Get (CurrentDate ) ; 2 )

          • 2. Re: Search for Date range


                 The Week and Year numbers are just numbers. not Calculations. They live in separate WEEk and YEAR fields in the DB
                 They are the result of a very nice script that PhilModJunk described for me here at the forum

                 See below ...


            Work Shifts Calender
            Forum post posted October 2, 2012 by Nils Morten Alexandersen
            24 Views, 1 Comment
            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 ...


            •           posted October 2, 2012 by PhilModJunk
              •                     Actions                     
                •                               Report Abuse
            •           Permalink

            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.



                 I already have the WeekOfYearFiscal ( Get (CurrentDate ) ; 2 ) as a field in the DB, to get the NOW value for week.

                 What I need is to make a list that shows FUTURE SHIFTS assignments ONLY ...   where the YEAR is Greater or equal to NOW and the week number is greater than NOW, but will also include al week numbers that are entered for the next year ...

                 At the moment (i.e. if the NOW week number is 42) i get Week 42-52 of the current year, and week 42-53 of the next year .... no the full next year


                 The results are shown in a Portal, and the Portal setup is part of the problem here

                 The Relationship is like

                 GET YEAR (Current Date) <= YEAR
                 USERNAME = USERNAME

                 If i include WEEK  i.e. GET WEEK (CurrentDate) < WEEK NUMBER  into the relationship i only get Higher than NOW WEEK records shown in the Portal , regardless of the year number.


                 Hope you understand my problem ;-)



            • 3. Re: Search for Date range

                   Sorry, my fault.  Why not combine the year and month into one field, like '201205', and then search for

                   ">=" & Year ( Get (CurrentDate ) ) & Right ( "0" & WeekOfYearFiscal ( Get (CurrentDate ) ; 2 ) ; 2 )