Re: Find in a date range

In article
<3ae4e065-eecf-454c-9073-8ffd822f6473@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
buck.matthew74@xxxxxxxxx wrote:

On Feb 26, 9:25 pm, Helpful Harry <helpful_ha...@xxxxxxxxxxxxxxxx>
wrote:
In article
<62bbe55f-fc4f-46aa-a539-32c493bf4...@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,

buck.matthe...@xxxxxxxxx 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
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.

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.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.

Relevant Pages

  • Re: Find in a date range
    ... I want to create a script that will select all records in the past ... Due to a bug in FileMaker, you may need to swap Set Field to ... Assuming you mean a calendar month the calculation part of the Set ... Field above has to work out the current month and subtract 1. ...
    (comp.databases.filemaker)
  • Time Calculations
    ... I need a calculation to subtract the start time from the finish time when the ... time is in the format of hours:minutes such as Start time as 1:40, ...
    (microsoft.public.project)
  • Re: Measure between date time A to date time B
    ... Subtract the later from the earlier and format the result as either ... calculation. ... Stewart:) ...
    (microsoft.public.excel.misc)
  • Re: LDAP be killing me. I need a good step by step
    ... I wrote a perl script to parse this and put it into a valid ldif format: ... Thanks for your script, which I shall study. ... For example, I use kmail, which claims to understand LDAP. ...
    (Fedora)
  • Re: LDAP be killing me. I need a good step by step
    ... I wrote a perl script to parse this and put it into a valid ldif format: ... Thanks for your script, which I shall study. ... For example, I use kmail, which claims to understand LDAP. ...
    (Fedora)