Help needed with finding records in a date range

[beckham]

My database has a date field showing (example) "deadline" (15/8/2011) etc

I would like to be able to script a find that will show all records that state todays date + the next 7 days?

I have tried the following (//...7) but it shows me results from today until July (7).

Can someone please advise?

Many Thanks

[Quartekoen]

If by "Script a find" you mean that this find is going to be hard-coded into a script, try setting the field in find mode to

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

[beckham]

If by "Script a find" you mean that this find is going to be hard-coded into a script, try setting the field in find mode to

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

Thanks for the reply, but as a novice I am still struggling to work this out.

I will try to explain better what I am hoping to do.

I have a Date field on my records named "deadline" which shows a calendar date 15/8/2011 etc.

I would like to format a button which could run a script that will find me all the records with this date field that equals the current date + 7 Days ahead.

So my records are filtered to only show me what deadlines I need to hit in the next week.

As a novice I need help or advise on how best to format fields and calculations to show this.

Thanks again

[Quartekoen]

Thanks for the reply, but as a novice I am still struggling to work this out.

I will try to explain better what I am hoping to do.

I have a Date field on my records named "deadline" which shows a calendar date 15/8/2011 etc.

I would like to format a button which could run a script that will find me all the records with this date field that equals the current date + 7 Days ahead.

So my records are filtered to only show me what deadlines I need to hit in the next week.

As a novice I need help or advise on how best to format fields and calculations to show this.

Thanks again

In that case, here are the basics:

First of all, make sure that the Deadline field is set up in your database as a "Date" type. What we're taking advantage of here is the fact that, when given a date range, FileMaker matches a field to all dates within that range. You can specify a date range when searching by using three periods (...) between two dates.

The second thing we're taking advantage of is FileMakers Get functions. Here, Get( CurrentDate ) always returns today's date. Lastly, while FileMaker displays dates in a readable format (such as 5/17/2011), in the background, these dates are actually stored as numbers. Now we can add a number to our date, such as Get( CurrentDate ) + 7, and FileMaker will give us a result of a new date. Get( CurrentDate ) + 7 will result in a date 7 days from now.

Now, we can combine these two ideas to search for a date range. When you search for these dates, you have to have some way of getting the field to say "5/17/2011...5/24/2011". We can build this string by using the ampersand (&) to combine the result of our Get( CurrentDate ) functions with the "...". The calculation to combine these would be

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

If we make a simple script that sets a field to this value, then running the script will find all records with a Deadline between today and 1 week from today.

Enter Find Mode[]

Set Field[ MyTable::Deadline ; Get( CurrentDate ) & "..." & Get( CurrentDate ) + 7 ]

Perform Find[]

Now create a button on your layout, assign this script to it, and click it whenever you need to.

[beckham]

In that case, here are the basics:

First of all, make sure that the Deadline field is set up in your database as a "Date" type. What we're taking advantage of here is the fact that, when given a date range, FileMaker matches a field to all dates within that range. You can specify a date range when searching by using three periods (...) between two dates.

The second thing we're taking advantage of is FileMakers Get functions. Here, Get( CurrentDate ) always returns today's date. Lastly, while FileMaker displays dates in a readable format (such as 5/17/2011), in the background, these dates are actually stored as numbers. Now we can add a number to our date, such as Get( CurrentDate ) + 7, and FileMaker will give us a result of a new date. Get( CurrentDate ) + 7 will result in a date 7 days from now.

Now, we can combine these two ideas to search for a date range. When you search for these dates, you have to have some way of getting the field to say "5/17/2011...5/24/2011". We can build this string by using the ampersand (&) to combine the result of our Get( CurrentDate ) functions with the "...". The calculation to combine these would be

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

If we make a simple script that sets a field to this value, then running the script will find all records with a Deadline between today and 1 week from today.

Enter Find Mode[]

Set Field[ MyTable::Deadline ; Get( CurrentDate ) & "..." & Get( CurrentDate ) + 7 ]

Perform Find[]

Now create a button on your layout, assign this script to it, and click it whenever you need to.

[beckham]

Thank you so much, that worked great!!

Your time and patience most appreciated!

[Quartekoen]

Happy to help!

If you'd like to know more about how to write scripts and calculations, I suggest you bookmark the Filemaker 11 Function Reference and the FileMaker 11 Script Step Reference for quick access. Even though functions and scripts are only a part of the power behind filemaker, knowing what sort of tools you have at your disposal is very helpful. These references have certainly helped me countless times!

[brian rich]

Happy to help!

If you'd like to know more about how to write scripts and calculations, I suggest you bookmark the Filemaker 11 Function Reference and the FileMaker 11 Script Step Reference for quick access. Even though functions and scripts are only a part of the power behind filemaker, knowing what sort of tools you have at your disposal is very helpful. These references have certainly helped me countless times!

You might also like to take a look at this blog: http://sixfriedrice.com/wp/the-secret-life-of-find-mode-requests/ which helped me sort out just what you could do with finds, especially with dates and times.

Brian