A script to search for records between two dates

[Pete Markham]

Here is the situation:

I work in a school. We are trying to create a database to store our daily announcement list. Each record has a start date fiedl and an end date field. We would like to write a script that will select a group of records examining each record to see if the current date falls between the start date and end date values. Once it has selected the group of records, we want it to switch to a different layout and display the records.

Here is the one sticky point: The database will be hosted on a Filemaker Server, and the documentation we have found so far, says that in order for it to get the current date, the database must be closed and then re-opened each day AT THE SERVER. Obviously, this isn't going to work.

Anyone have any ideas?

BTW - We eventually will be using this database with Lasso and our webserver to accomplish a web version of this also.

Thanks for your help!

Pete

[Vaughan]

That current date stuff only applies if you've used the TODAY function in a file somewhere (calculated field, script etc).

[Robert Kidd]

Create a calculation field called say "status" in the database containing the records which you want to find which is -

Case(StartDate <= Status(CurrentDate) and EndDate >= Status(CurrentDate),1,0)

In the Storage options you will have to set this field as unstored, calculate only when required.

Your script performs a search on the status field being 1

ie

Enter find mode ( no pause, no restore find requests )

Set field ["Status", "1"]

perform find [replace found set] ( no restore find requests )

you could do it all with a single perform find step using rhe restore find requests but the method above saves commenting

[Pete Markham]

Thanks Robert. Your info works perfectly and solves my problem.

Thanks again!

Pete