8 Replies Latest reply on Mar 30, 2015 12:30 PM by DawnKrueger

    Script to find records this week



      Script to find records this week


      <!-- StartFragment -->

      I have a date field (Date Due).  I'm trying to write a script that finds all records whereDate Due is the current week - Notthe next 7 days.  That is, if todayis Wednesday, I want to see all records from past Sunday through this coming Saturday.  That is, regardless what day of theweek, I want to see all records where Date Due occurs in the current week.  I'm having a lot of difficulty with this.  Can you help?

      <!-- EndFragment -->

        • 1. Re: Script to find records this week
          Kevin Hammond

          Craig, you can search a date range.


          Set Field [YourDateField]

          Get (CurrentDate) & "..." &  Get (CurrentDate) + 6


          Then make the necessary adjustments per your business logic so the first day is Sunday and the last day is Saturday in the date range.  I would use Custom Functions to isolate Sunday and Saturday.

          • 2. Re: Script to find records this week




            Thank you for your post.


            Actually, the script is fairly easy.  There would be three steps:


            1. Enter Find Mode

            2. Enter the Search Criteria

            3. Perform the Find


            Obviously, step #2 is where you are probably having difficulty.



            In order to find records this week, we need to know today's date.  The calculation function used to returnToday's date is: Get (CurrentDate)


            We also need to know what day of the week is today.  If Today is a Wednesday, we want tofind those records Sunday through Saturday.  That is, 3 days before and 3 days after.  For Tuesday, 2 days before and 4 daysafter.


            The calculation function used to return the day of the weekis: DayOfWeek(datefield)


            Below lists the day of the week followed by the number ofdays subtracted to get Sunday for the beginning of that week.  That is, if today is Tuesday, we wantto subtract 2 days.


            Sunday – 0

            Monday – 1

            Tuesday – 2

            Wednesday – 3

            Thursday – 4

            Friday – 5

            Saturday – 6


            Since the DayOfWeek(datefield) returns 1-7 for Sundaythrough Saturday, we need to offset the day by 1.  Therefore, the formula to get Sunday of the current week would be:


            Get (CurrentDate) – DayOfWeek (Get (CurrentDate)) + 1


            The ending of the week (Saturday) is similar to above.  Below lists the day ofthe week and the number of days we want to add to get Saturday for the end ofthat week.  That is, if today isTuesday, we want to add 4 days.


            Sunday – 6

            Monday – 5

            Tuesday – 4

            Wednesday – 3

            Thursday – 2

            Friday – 1

            Saturday – 0


            Therefore, subtract the Day of the week value from 7, andadd that to the current date.


            Get (CurrentDate) + 7 – DayOfWeek (Get (CurrentDate))


            Putting this all together, the script would be as follows:


            Enter Find Mode []

            Insert Calculated Result [Select; Due Date;

               (Get (CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 1) & "..."&

               (Get (CurrentDate ) + 7 - DayOfWeek (Get ( CurrentDate ) ) ) ]

            Perform Find []


            Let me know if you need further clarification.



            FileMaker, Inc.

            • 3. Re: Script to find records this week

              Kevin -  I don't want records in the coming week.  I want the current week.  I'm checking the online user's guide.  I don't see "Custom Functions".  Could you explain that a bit more?


              • 4. Re: Script to find records this week

                TSGal - You ROCK!  That's exactly what I wanted, and it works perfectly.  THANK YOU!


                • 5. Re: Script to find records this week



                  One additional point should be mentioned, that being that the insert script steps assume the target field is on the layout that is currently in focus. If not then filemaker returns a internal error 102, field is missing. Because of this, I would incorporate the following test to verify the script can run safely.


                  If ( PatterCount( FieldNames( Get (FileName); Get ( LayoutName ));"Date Due" )

                  .....insert find script steps from above


                  Exit Script

                  End If


                  Hope This Helps,


                  • 6. Re: Script to find records this week

                         I am working on Filemaker pro 12. I have a file that contains records from different districts. On monthly report it is not a problem to make a report for all the districts using scripts. But how can I work on a certain found set of records (which will change according to which district I would to report at that time) without repeating the found request before every script in the report?

                    • 7. Re: Script to find records this week

                           Sorry I misplaced my post !!!

                      • 8. Re: Script to find records this week

                         Hello I am looking to use this script, but how do I omit jobs that have already been competed from showing up when I run the script?

                        Thank you