Find in a date range
buck.ma...@yahoo.com
I want to create a script that will select all records in the past
week, past month, this year.
Please help.
Thank you
Matthew
Howard Schlossberg
>
> I want to create a script that will select all records in the past
> week, past month, this year.
To find everything for the past seven days:
Set Variable [ $Today; get(currentdate) ]
Enter Find Mode []
Set Field [ DateField; getastext($Today-7) & "..." & getastext($Today) ]
Perform Find []
For the last complete week (Monday thru Sunday), change the Set Field
calc to:
let([
x=dayofweek($Today);
start = $Today - (5 + x);
end = $Today - (x - 1) ];
getastext(start) & "..." & getastext(end)
)
Past month:
let([
start = date(month($Today)-1; day($Today); year($Today));
end = $Today ];
getastext(start) & "..." & getastext(end)
)
Last full calendar month:
let([
start = date(month($Today)-1; 1; year($Today));
end = date(month($Today); 1; year($Today))-1 ];
getastext(start) & "..." & getastext(end)
)
I'll let you figure out the year thing.
buck.ma...@yahoo.com
<how...@nospam.fmprosolutions.com> wrote:
This does not work. Let me make sure I have described things
correctly.
When I create an Order I enter into Date_Ordered field the current
date. From this I want to determine the found groups - past week, this
calendar month, this calendar year. Can you explain a better the
solution?
thanks
Howard Schlossberg
What do you mean when you say it doesn't work? It doesn't return the
results you expected? The script doesn't run at all? FileMaker doesn't
even let you exit the calc definition? Which one doesn't work? Or do
none of them work?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles
FileMaker 8 Certified Developer
Member, FileMaker Business Alliance
Helpful Harry
<62bbe55f-fc4f-46aa...@u69g2000hse.googlegroups.com>,
buck.ma...@yahoo.com wrote:
You'll need three Scripts, but they all should be the same as how you
would perform the Find manually ... with a slight hitch that FileMaker
is a little picky about date formats.
Manually to find the "past week" records you would:
- Enter Find mode
- Go to the date field and type in the date 7 days ago
followed by the date range symbol (ie. "...") and
then "today's" date
- Finally perform the Find
The equivalent Script would then be something like:
Enter Find Mode []
Set Field [DateField,
GetAsText(Get(CurrentDate) - 7) & "..."
& GetAsText(Get(CurrentDate))]
Perform Find []
(Note: Due to a bug in FileMaker, you may need to swap Set Field to
another function in some versions of FileMaker, eg. Insert Calculated
Result in the older versions)
For the "past month" and "this year" are slightly more compliced
because you have to play around with the Date functions of Date, Month,
Day and Year, but the basic idea is the same.
For "past month" you have to subtract a "month" from "today's" date ...
but it really depends whether you mean an actual calendar month (eg. 15
October to 15 November) which may vary in the number of days / weeks or
a simpler "4 week" month.
Assuming you mean a calendar month the calculation part of the Set
Field above has to work out the current month and subtract 1. To do
this it needs to pull apart the date into ots separate day, month and
year parts using the appropriate functions, subtract the 1 month, and
then put the parts back together into a date using the Date function.
eg.
GetAsText(
Date(
Month(Get(CurrentDate) - 1), Day(Get(CurrentDate)),
Year(Get(CurrentDate))
)
)
& "..." & GetAsText(Get(CurrentDate))
It's a similar calculation for the "past year", except you are
subtracting 1 from the year instead of the month.
Thankfully you don't have to worry about changeovers from one year to
the previous one (eg. December 2007 ... January 2008) since FileMaker's
date functions will handle this itself.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
buck.ma...@yahoo.com
wrote:
> In article
> <62bbe55f-fc4f-46aa-a539-32c493bf4...@u69g2000hse.googlegroups.com>,
The past 7 days solution works but the past month (30 day solution
does not work) - it returns the same result as the past 7 days
solution. Any idea why?
One query I have concerns the day month year format. For some reason
Americans like to use the Month Day Year format (very illogical
Captain) instead of a year month day or day month year format. Does
this matter to the calculation. My date format displays as; 23 - Feb -
2008 .
Thanks
Matthew
Helpful Harry
<3ae4e065-eecf-454c...@s37g2000prg.googlegroups.com>,
buck.ma...@yahoo.com wrote:
> The past 7 days solution works but the past month (30 day solution
> does not work) - it returns the same result as the past 7 days
> solution. Any idea why?
Ops! Sorry, that's my mistake. :o(
There's a typo in the Calculation for the "past month" - the "-1"
should be outside the bracket / parentheses because you're wanting to
subtract one from the month, not the date.
eg.
GetAsText(
Date(
Month(Get(CurrentDate)) - 1, Day(Get(CurrentDate)),
Year(Get(CurrentDate))
)
)
& "..." & GetAsText(Get(CurrentDate))
BUT,
this calculation goes back one whole calendar month, not 30 days. If
you want 30 days you can use the same calculation as the "past week"
and simply subtract 30 instead of 7.
> One query I have concerns the day month year format. For some reason
> Americans like to use the Month Day Year format (very illogical
> Captain) instead of a year month day or day month year format. Does
> this matter to the calculation. My date format displays as; 23 - Feb -
> 2008 .
Nope. Filemaker stores it's dates internally in an independant format,
it's only when being displayed that the date uses the system format /
field formatting options that you have chosen.
But the Date function must have it's parameters in American order.
ie.
Date (MonthNumber, DayNumber, YearNumber)
If you are giving your databases to other people who may have a
different system date format, then you should add a script that is run
on opening (via the Preferences option) that runs the command "Set Use
System Formats [On]". That way any fields using the default formatting
will look as they want on their own computer.
buck.ma...@yahoo.com
wrote:
> In article
> <3ae4e065-eecf-454c-9073-8ffd822f6...@s37g2000prg.googlegroups.com>,
>
>
>
> buck.matthe...@yahoo.com wrote:
> > On Feb 26, 9:25 pm, Helpful Harry <helpful_ha...@nom.de.plume.com>
> > wrote:
> > > In article
> > > <62bbe55f-fc4f-46aa-a539-32c493bf4...@u69g2000hse.googlegroups.com>,
>
> > > buck.matthe...@yahoo.com wrote:
> > > > FM 8.5
>
> > > > I want to create a script that will select all records in the past
> > > > week, past month, this year.
> > > > Please help.
>
> > > > Thank you
> > > > Matthew
>
> > > You'll need three Scripts, but they all should be the same as how you
> > > is a little picky aboutdateformats.
>
>
> > > - EnterFindmode
>
> > > - Go to thedatefield and type in thedate7 days ago
> > > then "today's"date
>
> > > - Finally perform theFind
>
> > > The equivalent Script would then be something like:
>
> > > EnterFindMode []
> > > Set Field [DateField,
> > > GetAsText(Get(CurrentDate) - 7) & "..."
> > > & GetAsText(Get(CurrentDate))]
> > > PerformFind[]
>
> > > (Note: Due to a bug in FileMaker, you may need to swap Set Field to
> > > another function in some versions of FileMaker, eg. Insert Calculated
> > > Result in the older versions)
>
> > > For the "past month" and "this year" are slightly more compliced
> > > Day and Year, but the basic idea is the same.
>
> > > For "past month" you have to subtract a "month" from "today's"date...
> > > but it really depends whether you mean an actual calendar month (eg. 15
> > > October to 15 November) which may vary in the number of days / weeks or
> > > a simpler "4 week" month.
>
> > > Assuming you mean a calendar month the calculation part of the Set
> > > Field above has to work out the current month and subtract 1. To do
> > > year parts using the appropriate functions, subtract the 1 month, and
> > > then put the parts back together into adateusing theDatefunction.
> > > eg.
> > > GetAsText(
> > > Date(
> > > Month(Get(CurrentDate) - 1), Day(Get(CurrentDate)),
> > > Year(Get(CurrentDate))
> > > )
> > > )
> > > & "..." & GetAsText(Get(CurrentDate))
>
> > > It's a similar calculation for the "past year", except you are
> > > subtracting 1 from the year instead of the month.
>
> > > Thankfully you don't have to worry about changeovers from one year to
> > > the previous one (eg. December 2007 ... January 2008) since FileMaker's
>
> > The past 7 days solution works but the past month (30 day solution
> > does not work) - it returns the same result as the past 7 days
> > solution. Any idea why?
>
> Ops! Sorry, that's my mistake. :o(
>
> There's a typo in the Calculation for the "past month" - the "-1"
> should be outside the bracket / parentheses because you're wanting to
> subtract one from the month, not thedate.
> eg.
> GetAsText(
> Date(
> Month(Get(CurrentDate)) - 1, Day(Get(CurrentDate)),
> Year(Get(CurrentDate))
> )
> )
> & "..." & GetAsText(Get(CurrentDate))
>
> BUT,
> this calculation goes back one whole calendar month, not 30 days. If
> you want 30 days you can use the same calculation as the "past week"
> and simply subtract 30 instead of 7.
>
> > One query I have concerns the day month year format. For some reason
> > Americans like to use the Month Day Year format (very illogical
> > Captain) instead of a year month day or day month year format. Does
> > 2008 .
>
> Nope. Filemaker stores it's dates internally in an independant format,
> field formatting options that you have chosen.
>
> ie.
> Date(MonthNumber, DayNumber, YearNumber)
>
> If you are giving your databases to other people who may have a
> on opening (via the Preferences option) that runs the command "Set Use
> System Formats [On]". That way any fields using the default formatting
> will look as they want on their own computer.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)
This solution works but the find operation halts and then requires
pressing the find button on the tool/palette area to complete the
script. Is there any way to avoid this?
Thank you.
Helpful Harry
<54b88ea4-5a65-4581...@u10g2000prn.googlegroups.com>,
buck.ma...@yahoo.com wrote:
>
> This solution works but the find operation halts and then requires
> pressing the find button on the tool/palette area to complete the
> script. Is there any way to avoid this?
>
> Thank you.
Halting means you've got the Pause option turned on. Check the script
to make sure that the Pause option is turned off for the Enter Find
Mode command.
When you are setting the Find Requests within the script you also need
to turn off the Restore options for both the Enter Find Mode and
Perform Find commands (unless you are wanting to modify a stored Find
request).
The commands should display in ScriptMaker with no options.
ie.
Enter Find Mode []
Set Field [...]
Perform Find []