Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2086

Re: How to Search for a Year in a Script

Show key headers only | View raw

In article <1m13hlr.134v3c4oahfczN%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:
> 
> How do I execute a Find in a script to find records where a date field
> has a specified year? Executing the Find manually, I'd just enter the
> year in the date field (in Find mode), FileMaker would change that to
> */*/year, and off we'd go. In my script, the year is in a local
> variable, say $The Year. Constructs like Date ("*"; "*"; $The Year)
> don't work. What to do?


FileMaker is generally fairly forgiving about using the wrong data type
in a field, but the exception is when performing Finds with Date or
Time fields with the wildcard symbols ("*" or "@" or "!") in scripted
finds simply doesn't work (at least in older versions of FileMaker, not
sure about the newest versions).

There's two methods to achieve what you want ...

1.  Separate Year Field
    You can create a separate field which only stores the 
    year as a number
    i.e.
       YearField = Year(DateField)
    and perform the scripted Find on that using the Set Field
    command
    i.e.
       Enter Find Mode []
       Set Field [YearField, "2015"]
       Perform Find []

    If you've got MANY MANY records, the YearField could be a
    bit slow as a Calculation field. It may be better to use 
    a normal Number field with an Auto-enter by Calculation 
    option of Year(DateField) ... but you then have to 
    remember to change it yourself if you change the original
    DateField data, unless you're using a newer version of 
    FileMaker that has the option to recalculate Auto-enter 
    values or run Scripts on exiting a field.


2.  Date Range Find
    You can use the Range symbol "..." to find all records
    with in the range 1 January to 31 December of the year ... 
    but there is an added problem. (Weirdly the Range 
    symbol doesn't have the same issues as some of the 
    wildcard symbols.)

    BUT there's a "bug" in the Set Field command (again, at
    least in older versions of FileMaker) which means it 
    refuses to set Date fields with non-date information, 
    and has similar problems with Time fields. You can try
    using the command
         Set Field [DateField, "1/1/2015...31/12/2015"]
    but in older versions of FileMaker Pro (and may still 
    not be fixed) it only gives the result "1/1/2015" and 
    ignores the rest. You can use a calculation to get Set
    Field to give the full result, but, as you found out, 
    FileMaker then won't recognise it as an actual date
    when performing the Find.

    Instead you have to use an Insert command, which 
    annoyingly means the field must be on the current 
    layout and allow user entry.
    e.g.
       Enter Find Mode []
       Insert Calculated Result [Select, DateField, 
                                 "1/1/2015...31/12/2015"]
       Perform Find []




Rather than hard-coding the year "2015" into the Scripts, it's usually
easier to use a Global field (or variable in newer FileMaker versions)
which stores the wanted year - that way you only have to change the
Global field in Browse Mode "next year" or whenever you want a
different year.
e.g.
       Insert Calculated Result [Select, DateField, 
                                 "1/1/" & g_Year & "..." & 
                                 "31/12/" & g_Year]

If the Find is only ever for the current year, then you could use the
Year and Current Date functions instead.
e.g.
       Insert Calculated Result [Select, DateField, 
                                 "1/1/" & Year(Get(CurrentDate))
                                 & "..." & "31/12/" & 
                                 Year(Get(CurrentDate))]

(There is a further bug here which means you sometimes have to also
muck about with TexttoDate and DatetoText functions to get FileMaker to
perform scripted Finds with dates, but the Insert Calculated Result
scripts above work in FileMaker 5.5, so should work in any newer
version too.)


By the way, remember to make sure you turn off the "Restore" options
for both the Enter Find Mode *and* Perform Find commands - this can be
a simple mistake that is easily overlooked and cause a lot of
frustration in scripted Finds.

Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-11 01:10 -0700
  Re: How to Search for a Year in a Script Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2015-03-11 02:22 -0700
    Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-11 12:00 -0700
      Re: How to Search for a Year in a Script clkaufmann@gmx.ch (Christoph Kaufmann) - 2015-03-11 20:39 +0100
      Re: How to Search for a Year in a Script Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2015-03-12 20:55 -0700
  Re: How to Search for a Year in a Script Helpful Harry <HelpfulHarry@BusyWorking.com> - 2015-03-12 18:30 +1300
    Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-19 22:59 -0700
      Re: How to Search for a Year in a Script Helpful Harry <HelpfulHarry@BusyWorking.com> - 2015-03-20 19:18 +1200
        Re: How to Search for a Year in a Script Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2015-03-20 21:27 -0700
        Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-22 00:40 -0700
          Re: How to Search for a Year in a Script Helpful Harry <HelpfulHarry@BusyWorking.com> - 2015-03-23 09:03 +1200
            Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-24 01:21 -0700
              Re: How to Search for a Year in a Script ursus <ursus.kirk@gmail.com> - 2015-03-24 09:45 +0100
                Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-24 09:54 -0700
      Re: How to Search for a Year in a Script csampson@inetworld.net (Charles H. Sampson) - 2015-03-19 23:51 -0700
  Re: How to Search for a Year in a Script wonfuji@gmail.com - 2015-03-12 06:54 -0700
    Re: How to Search for a Year in a Script Ursus <ursus.kirk@ziggo.nl> - 2015-03-12 17:27 +0100

csiph-web