Searching with current date

[Peter Knowles]

I have a couple of databases that all require the same search process, but I'll describe the simplest example here and I'll try to work up from there for the others. Our school has begun listing scholarship applications in FMP 4.0 and we want to be able to search for scholarships which are still available for students to apply. Each record has a "duedate" field, as well as a "currrentdate" field. There's also a "status" field which uses a calculation (If[duedate>currentdate "A", "X"]) where A = an Active scholarship and X = one whose due date has passed. Ideally, a search of the database which is restricted to records with status="A" will only show those scholarships whose due dates have not yet passed.

The problem (I think) comes from how I've set up the "currentdate" field. Initially it was a calculation field (=Today) but that only entered the date the record was created. It didn't change as today's date changed. Then I changed it to a date field with autoenter calculations (of various types) but couldn't get it to work. I'm starting to wonder if I need to run a script of some sort to update the "currentdate" field to today's actual date before recalculating the status field. But I don't know.

Any ideas? I thought I had this working right off the bat, but I guess that would have been too easy. Hoping someone out there can help out.

[Vaughan]

You don't *need* a Currentdate field but if you wnat one, don't use Today. (There is a plague of Today-related posts!) The Today function only calculates when the file is first opened. It has been functionally replaced by the Status(CurrentDate) function.

If you want to keep the currentdate field, make it UNSTORED and change it to Status(CurrentDate). That's all!

If you want to do without the currentdate field, change the status calculated field to If[ duedate > Status(CurrentDate), "A", "X"] and make it unstored as well.