Data selection within a timeframe

[Dutchy]

Need help on this one.

To be able to get a nice overview of current projects and urgent deliveries I would like to make a button, which will switch to a new window or layout, that shows all projects (table view or something similar) that have a closing date within one week of the current date. Is this possible and if so How!? Don't know how I would start scripting this one? Can somebody help me out?

Thanks,

Dutchy

[jamesducker]

Yes, you'd need a script. Something like:

new window

go to layout [name-of-the-nice-layout-you've-made]

adjust window [resize to fit]

move/resize window [,,wherever,wherever]

enter find mode

set field [due in the coming week, "Y"]

perform find[]





Let us know how you get on... -)

* "due in the coming week" would be a new calculation field defined as something like:

if(get(currentdate) - closing date 

[LaRetta]

Why create a calculation here (and add to the number of your fields, which grows too large as it is)? The calculation can reside only within the script as:

"<" & Get ( CurrentDate ) + 7

You didn't indicate if any projects OLDER than the current date should also show. If not, you can produce a range as:

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

If your record number is quite large, you might also want to consider using Go To Related Record[]. In this way, it will remain quite fast and also provide other functionality (such as filter portal) for display of various Project status' and date ranges.

LaRetta :wink2:

[Dutchy]

Hello James,

I can't seem to get the "due in the coming week" calculation right. what do I fill in for "Y" is that the closing date field?!?

Other little problem is that the closing date's that will be entered into the database will be European dates (day/month/year) and current date in set as month/day/year. Where can I change the date settings for the current date. Can't seem to find it in "date format"

Thanks for your help so far,

Dutchy

Edited March 6, 2007 by Guest

[jamesducker]

Just put in, literally, "Y" - short for "yes". The new field will then contain "Y" if the closing date is in the next week, and will be empty if it's not.

The script I gave you will search for records which have "Y" in that field - in other words, records which are due in the next week, which is what you want to see.

Don't worry about the date formats. As long as it's a FileMaker date field, FileMaker will deal with the formatting internally. "Format date" only changes the way the date is displayed on a layout, and doesn't affect the data stored at all.

James

[Dutchy]

Thanks James,

It works great now.

Dutchy