Jump to content
Sign in to follow this  
Peter Knowles

Searching with current date

Recommended Posts

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.