8 Replies Latest reply on Jan 22, 2014 12:22 PM by JenniferBartley

    date ranges



      date ranges


           I am brand new to Filemaker and need help creating a sort by date ranges. I created a report that will be ran weekly so I will need to be able to change the dates for this sort weekly. Any help?

           Thanks Jennie

        • 1. Re: date ranges

               I know that your new to FileMaker as am I, but it sounds to me like your Sort won't change for that report.  It sounds more like you are looking to apply a Filter so you might want to create a Script that applies the Filter to the proper Data set, Runs the Report, and then possibly returns to the dialog form from which you launched the Script.  You would then attach that Script to a Button or Menu where one is most likely to run the Report.

               That's from one Novice to another Novice.  Maybe the experts will think differently.

          • 2. Re: date ranges

                 Thank you for your quick response. I read up briefly on scripts but do not know how to create one.

            • 3. Re: date ranges

                   You might find the scripted finds in this thread helpful. Several show how to specify date ranges in the find in different ways--including scripts that calculate the dates to be used from the computer's system clock (Get (CurrentDate ) ). Scripted Find Examples

              • 4. Re: date ranges

                     Thank you. Do I need to have Global fields in place before I can create these scripts to only find data by a weekly date range?

                • 5. Re: date ranges

                       Only if you will need a user to input criteria used in the find. If the script only uses dates calculated from the current date, then you don't need any global fields.

                  • 6. Re: date ranges

                         Perfect! Thank you

                         Currently my report shows 300,000 records (every clock in and out from employees for 3+ years) I need to only show last week (1/13/14 -1/17/14)

                         Would that look like this:?


                         Set Field [Production Activity report::Start Date; Get (Current Date)]

                    • 7. Re: date ranges

                           If it is part of this scripting:

                           Enter Find mode []
                           Set Field [Production Activity report::Start Date; Get (Current Date)]
                           Set Error Capture [on]
                           Perform find[]

                           It will find all records with today's date. That won't be all records for the current week. You'd need to specify a range of dates for that.

                           In place of Get ( CurrentDate ) in the above set field step, you might use:

                           Let ( [Today = Get ( CurrenDate ) ;
                                     Sunday = Today - DayOfWeek ( Today ) + 1 ; // this is the date for Sunday of the same week as today's date
                                     Saturday = Sunday + 6
                                     Sunday & "..." & Saturday
                                  ) // Let

                           The functions that I have used, Let, Get and DayOfWeek can be looked up in FileMaker Help or any training materials you have. And dates are really numbers so adding a number to a date computes a new date the added number of days in the future.

                      • 8. Re: date ranges

                             Thank you I am going to try that now