### Title

Script to find records this week

### Post

<!-- 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 -->
RCraig:

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.

TSGal

FileMaker, Inc.