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

Groups > comp.databases.filemaker > #2075 > unrolled thread

How to Search for a Year in a Script

Back to article view | Back to comp.databases.filemaker

Contents

  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

#2075 — How to Search for a Year in a Script

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?

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [next] | [standalone]

#2076

On 3/11/2015 1:10 AM, Charles H. Sampson 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?

Essentially as text, just like you were entering it manually.  Set a 
variable to
	"*/*/" & $TheYear
and then use THAT variable in your find request.

[toc] | [prev] | [next] | [standalone]

#2083

Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:

> On 3/11/2015 1:10 AM, Charles H. Sampson 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?
> 
> Essentially as text, just like you were entering it manually.  Set a 
> variable to
>       "*/*/" & $TheYear
> and then use THAT variable in your find request.

Didn't work for me. In setting up the Find criterion, I got the message
that the date has to look like 1/1/2005.

In my post, I neglected to mention that I'm still on FMP8. Is your
solution something that came along in a later version?

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2085

Charles H. Sampson <csampson@inetworld.net> wrote:

["*/*/" & $TheYear]
> Didn't work for me. In setting up the Find criterion, I got the message
> that the date has to look like 1/1/2005.
> 
> In my post, I neglected to mention that I'm still on FMP8. Is your
> solution something that came along in a later version?

Might be. Try the script step insert calculalted result instead of set
field (the field needs to be on the layout for that purpose).

You might also try the expression

Date ( 1 ; 1 ; 2015 ) & "..." & Date ( 12 ; 31 ; 2015 )
-- 
Christoph Kaufmann
http://clk.ch

[toc] | [prev] | [next] | [standalone]

#2096

On 3/11/2015 12:00 PM, Charles H. Sampson wrote:
> Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:
>
>> On 3/11/2015 1:10 AM, Charles H. Sampson 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?
>>
>> Essentially as text, just like you were entering it manually.  Set a
>> variable to
>>        "*/*/" & $TheYear
>> and then use THAT variable in your find request.
>
> Didn't work for me. In setting up the Find criterion, I got the message
> that the date has to look like 1/1/2005.
>
> In my post, I neglected to mention that I'm still on FMP8. Is your
> solution something that came along in a later version?

If you can type it in a manual find, then you can use Set Field to set 
it from a script.  If you are putting the criteria directly into a 
Perform Find script step, then you cannot directly put a calc into that 
dialog.  You must first set the calc result to a variable, and then use 
that variable name in the Perform Find step.

If $TheYear = 2014 and you set $searchDate = "*/*/" & $TheYear,
then the result of $searchDate should be "*/*/2014".  Again, if you can 
use that in a manual find in that field, then you should also be able to 
use it from a script.  This date search format was introduced (I'm 
pretty sure) in FM7.

Howard

[toc] | [prev] | [next] | [standalone]

#2086

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)

[toc] | [prev] | [next] | [standalone]

#2116

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

> 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 []
> ...
Something happened that caused me to overlook this reply. I've now
implemented a form of this, specifically

Enter Find Mode []
Set Field [Date; $Report Year]
Perform Find []
Enter Find Mode []
Set Field [Date Paid, $Report Year]
Extend Found Set []

This appears to be working. (I haven't done the necessary exhaustive
testing yet.) However, for some reason the script pauses at the Find and
Extend steps, displaying a Find Mode window with an active Continue
button. I haven't found a "Don't Pause" checkbox or anything similar
yet.

I haven't digested the rest of your post yet, Harry. I might have
comments or questions on it when I do.

Thanks as usual.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2117

In article <1m1hl39.1hp6kkr1njt975N%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:

> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> 
> > 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 []
> > ...
> 
> Something happened that caused me to overlook this reply. I've now
> implemented a form of this, specifically
> 
> Enter Find Mode []
> Set Field [Date; $Report Year]
> Perform Find []
> Enter Find Mode []
> Set Field [Date Paid, $Report Year]
> Extend Found Set []
> 
> This appears to be working. (I haven't done the necessary exhaustive
> testing yet.) However, for some reason the script pauses at the Find and
> Extend steps, displaying a Find Mode window with an active Continue
> button. I haven't found a "Don't Pause" checkbox or anything similar
> yet.
> 
> I haven't digested the rest of your post yet, Harry. I might have
> comments or questions on it when I do.

If that's really your Script and doesn't have a typo when transferred
to here, then it looks like there is a mistake in it. 

I'm not sure what you're trying to achieve but there are two Enter Find
Mode commands and only one Perform Find, so that Script will finish
still in Find Mode.

If you are trying to find any records with Date = $Report Year or any
records with Date Paid = $Report Year, then the Script should be
something like this:

    Enter Find Mode []
    Set Field [Date; $Report Year]
    New Record / Request
    Set Field [Date Paid; $Report Year]
    Perform Find []

As with manual Finds, the New Request creates an "OR" find (i.e. find
records matching request criteria 1 OR records matching request
criteria 2.


By the way, it's probably not a good idea to have a Field named "Date"
- it could be easily confused with the Date function.

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#2122

On 3/20/2015 12:18 AM, Helpful Harry wrote:
> In article <1m1hl39.1hp6kkr1njt975N%csampson@inetworld.net>, Charles H.
> Sampson <csampson@inetworld.net> wrote:
>
>> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
>>
>> Something happened that caused me to overlook this reply. I've now
>> implemented a form of this, specifically
>>
>> Enter Find Mode []
>> Set Field [Date; $Report Year]
>> Perform Find []
>> Enter Find Mode []
>> Set Field [Date Paid, $Report Year]
>> Extend Found Set []
>>
>> This appears to be working. (I haven't done the necessary exhaustive
>> testing yet.) However, for some reason the script pauses at the Find and
>> Extend steps, displaying a Find Mode window with an active Continue
>> button. I haven't found a "Don't Pause" checkbox or anything similar
>> yet.
>>
>
> If that's really your Script and doesn't have a typo when transferred
> to here, then it looks like there is a mistake in it.
>
> I'm not sure what you're trying to achieve but there are two Enter Find
> Mode commands and only one Perform Find, so that Script will finish
> still in Find Mode.
>
> If you are trying to find any records with Date = $Report Year or any
> records with Date Paid = $Report Year, then the Script should be
> something like this:
>
>      Enter Find Mode []
>      Set Field [Date; $Report Year]
>      New Record / Request
>      Set Field [Date Paid; $Report Year]
>      Perform Find []


If Date Paid is a stored/indexed field, then I agree with Harry that you 
should do it all in one Find.

But Harry's missing the Extend Found Set[] script step in his older 
versions of FMP, and it sometimes makes sense to do an initial find 
(i.e. on the Date field) to limit your found set on an indexed field, 
and then to constrain or extend the found set on an unindexed or related 
field.

Howard

[toc] | [prev] | [next] | [standalone]

#2123

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

> In article <1m1hl39.1hp6kkr1njt975N%csampson@inetworld.net>, Charles H.
> Sampson <csampson@inetworld.net> wrote:
> 
> > Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> > 
> > > 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 []
> > > ...
> > 
> > Something happened that caused me to overlook this reply. I've now
> > implemented a form of this, specifically
> > 
> > Enter Find Mode []
> > Set Field [Date; $Report Year]
> > Perform Find []
> > Enter Find Mode []
> > Set Field [Date Paid, $Report Year]
> > Extend Found Set []
> > 
> > This appears to be working. (I haven't done the necessary exhaustive
> > testing yet.) However, for some reason the script pauses at the Find and
> > Extend steps, displaying a Find Mode window with an active Continue
> > button. I haven't found a "Don't Pause" checkbox or anything similar
> > yet.
> > 
> > I haven't digested the rest of your post yet, Harry. I might have
> > comments or questions on it when I do.
> 
> If that's really your Script and doesn't have a typo when transferred
> to here, then it looks like there is a mistake in it. 
> 
> I'm not sure what you're trying to achieve but there are two Enter Find
> Mode commands and only one Perform Find, so that Script will finish
> still in Find Mode.
> 
> If you are trying to find any records with Date = $Report Year or any
> records with Date Paid = $Report Year, then the Script should be
> something like this:
> 
>     Enter Find Mode []
>     Set Field [Date; $Report Year]
>     New Record / Request
>     Set Field [Date Paid; $Report Year]
>     Perform Find []
> 
> As with manual Finds, the New Request creates an "OR" find (i.e. find
> records matching request criteria 1 OR records matching request
> criteria 2.

That's a good thing to know. I don't think the Pogue book tells me that
and I don't really want to search it because its index is so poor, but I
had assumed that when you specify a find with values for two fields, you
got an "AND" find. Because of unclear result with my probe testing, I
just decided to do things step-by-step.

> By the way, it's probably not a good idea to have a Field named "Date"
> - it could be easily confused with the Date function.

It probably depends on your background. Mine is one in which so-called
overloading is available and acceptable. (Overloading refers to the
ability to have two different meanings for a name simultaneously.
Deciding which one is in use at a particular time is called
disambiguation.) I don't have any real problem with it but it can get
confusing if you're not careful.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2124

In article <1m1ntek.tz2lk8pf0e7aN%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:

> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> 
<snip>
> > 
> > If you are trying to find any records with Date = $Report Year or any
> > records with Date Paid = $Report Year, then the Script should be
> > something like this:
> > 
> >     Enter Find Mode []
> >     Set Field [Date; $Report Year]
> >     New Record / Request
> >     Set Field [Date Paid; $Report Year]
> >     Perform Find []
> > 
> > As with manual Finds, the New Request creates an "OR" find (i.e. find
> > records matching request criteria 1 OR records matching request
> > criteria 2.
> 
> That's a good thing to know. I don't think the Pogue book tells me that
> and I don't really want to search it because its index is so poor, but I
> had assumed that when you specify a find with values for two fields, you
> got an "AND" find. Because of unclear result with my probe testing, I
> just decided to do things step-by-step.

Separate requests = OR find 
(i.e. find records matching field in Request1 OR field in Request2)

One request = AND find 
(i.e. find records matching field1 AND field2 in the request)

The Omit checkbox = NOT find
(i.e. find record NOT matching field1)


You can of course mix these in various ways.


I didn't know which your Script was trying to do (I guessed that Extend
was to add more records). 

If you are trying to find records where Date = Report Year (and Date
Paid can be anything) OR records where Date Paid = Report Year (and
Date can be anything), then you need the Script above using the New
Record / Request command.

If you are trying to find records where both Date = Report Year AND
Date Paid = Report Year, then you can do that within one Find Request,
e.g.

     Enter Find Mode []
     Set Field [Date; $Report Year]
     Set Field [Date Paid; $Report Year]
     Perform Find []


As Howard (I think it was) said, there may well be good reasons to use
Extend or Restrain, but unless you've got a massive number of records
and / or a very slow computer / external storage / network, there's
really little point in using them and making things more complicated
than they need to be.




> > By the way, it's probably not a good idea to have a Field named "Date"
> > - it could be easily confused with the Date function.
> 
> It probably depends on your background. Mine is one in which so-called
> overloading is available and acceptable. (Overloading refers to the
> ability to have two different meanings for a name simultaneously.
> Deciding which one is in use at a particular time is called
> disambiguation.) I don't have any real problem with it but it can get
> confusing if you're not careful.

Some names aren't allowed to be used for fields, but applications
usually warn or stop you using them. For example, my old version Adobe
InDesign will happily let me define colours called "Red", "Blue",
"Green", but for some reason won't allow "Yellow".

In the old days of real programming you couldn't use data variable
names that were command or function names, so I always tend to make my
field names unique (i.e. InvoiceDate rather than just Date).

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#2125

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

> In article <1m1ntek.tz2lk8pf0e7aN%csampson@inetworld.net>, Charles H.
> Sampson <csampson@inetworld.net> wrote:
> 
> > Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> > 
> <snip>
> > > 
> > > If you are trying to find any records with Date = $Report Year or any
> > > records with Date Paid = $Report Year, then the Script should be
> > > something like this:
> > > 
> > >     Enter Find Mode []
> > >     Set Field [Date; $Report Year]
> > >     New Record / Request
> > >     Set Field [Date Paid; $Report Year]
> > >     Perform Find []
> > > 
> > > As with manual Finds, the New Request creates an "OR" find (i.e. find
> > > records matching request criteria 1 OR records matching request
> > > criteria 2.
> > 
> > That's a good thing to know. I don't think the Pogue book tells me that
> > and I don't really want to search it because its index is so poor, but I
> > had assumed that when you specify a find with values for two fields, you
> > got an "AND" find. Because of unclear result with my probe testing, I
> > just decided to do things step-by-step.
> 
> Separate requests = OR find 
> (i.e. find records matching field in Request1 OR field in Request2)
> 
> One request = AND find 
> (i.e. find records matching field1 AND field2 in the request)
> 
> The Omit checkbox = NOT find
> (i.e. find record NOT matching field1)
> 
> 
> You can of course mix these in various ways.
> 
> 
> I didn't know which your Script was trying to do (I guessed that Extend
> was to add more records). 
> 
> If you are trying to find records where Date = Report Year (and Date
> Paid can be anything) OR records where Date Paid = Report Year (and
> Date can be anything), then you need the Script above using the New
> Record / Request command.
> 
> If you are trying to find records where both Date = Report Year AND
> Date Paid = Report Year, then you can do that within one Find Request,
> e.g.
> 
>      Enter Find Mode []
>      Set Field [Date; $Report Year]
>      Set Field [Date Paid; $Report Year]
>      Perform Find []
> 
> 
> As Howard (I think it was) said, there may well be good reasons to use
> Extend or Restrain, but unless you've got a massive number of records
> and / or a very slow computer / external storage / network, there's
> really little point in using them and making things more complicated
> than they need to be.
> 
> > > By the way, it's probably not a good idea to have a Field named "Date"
> > > - it could be easily confused with the Date function.
> > 
> > It probably depends on your background. Mine is one in which so-called
> > overloading is available and acceptable. (Overloading refers to the
> > ability to have two different meanings for a name simultaneously.
> > Deciding which one is in use at a particular time is called
> > disambiguation.) I don't have any real problem with it but it can get
> > confusing if you're not careful.
> 
> Some names aren't allowed to be used for fields, but applications
> usually warn or stop you using them. For example, my old version Adobe
> InDesign will happily let me define colours called "Red", "Blue",
> "Green", but for some reason won't allow "Yellow".
> 
> In the old days of real programming you couldn't use data variable
> names that were command or function names, so I always tend to make my
> field names unique (i.e. InvoiceDate rather than just Date).

In spite of my happy experience using overloading, I'm rethinking and
will probably move toward your idea, Harry. I'm probably going to rename
my date fields, so that Date becomes Date of Service, for example.
However, I'll leave the fields' labels on layouts as Date, where the
context makes it clear what date is being specified.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2126

>>
>> In the old days of real programming you couldn't use data variable
>> names that were command or function names, so I always tend to make my
>> field names unique (i.e. InvoiceDate rather than just Date).
>
> In spite of my happy experience using overloading, I'm rethinking and
> will probably move toward your idea, Harry. I'm probably going to rename
> my date fields, so that Date becomes Date of Service, for example.
> However, I'll leave the fields' labels on layouts as Date, where the
> context makes it clear what date is being specified.
>
> Charlie
>

Charlie, although filemaker does accept field names with spaces, I would 
rather advice names without. Use 'CamelCase' as Harry showed.
Names with spaces will probably lead to all kind of problems.

-- 
Keep well, Erik

[toc] | [prev] | [next] | [standalone]

#2127

ursus <ursus.kirk@gmail.com> wrote:

> >>
> >> In the old days of real programming you couldn't use data variable
> >> names that were command or function names, so I always tend to make my
> >> field names unique (i.e. InvoiceDate rather than just Date).
> >
> > In spite of my happy experience using overloading, I'm rethinking and
> > will probably move toward your idea, Harry. I'm probably going to rename
> > my date fields, so that Date becomes Date of Service, for example.
> > However, I'll leave the fields' labels on layouts as Date, where the
> > context makes it clear what date is being specified.
> >
> > Charlie
> >
> 
> Charlie, although filemaker does accept field names with spaces, I would
> rather advice names without. Use 'CamelCase' as Harry showed.
> Names with spaces will probably lead to all kind of problems.

I was appalled when I first came across a programming language that
allowed spaces in identifiers. It was unlike anything I had ever seen
before and I was known professionally as a "computer language expert".
However, as you see, I have been using them and I see no problem so far.
The  only issue (for me) is that I have to do something special when I
want a reserved word (usually "not") to be part of the identifier.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2118

Charles H. Sampson <csampson@inetworld.net> wrote:

> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> 
> > 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 []
> > ...
> Something happened that caused me to overlook this reply. I've now
> implemented a form of this, specifically
> 
> Enter Find Mode []
> Set Field [Date; $Report Year]
> Perform Find []
> Enter Find Mode []
> Set Field [Date Paid, $Report Year]
> Extend Found Set []
> 
> This appears to be working. (I haven't done the necessary exhaustive
> testing yet.) However, for some reason the script pauses at the Find and
> Extend steps, displaying a Find Mode window with an active Continue
> button. I haven't found a "Don't Pause" checkbox or anything similar
> yet.

Pilot error in the extreme. Not only are there Pause checkboxes for the
Perform Find, Constrain Found Set, and Expand Found Set commands, which
ScriptMaker conveniently turns on by default, I still had a couple of
Pause commands inserted while debugging still in my script. My script
runs very nicely now.

Sorry to have wasted people's time.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2087

what about a calculated field that uses Year(theDate) and just search on that?

[toc] | [prev] | [next] | [standalone]

#2088

wonfuji@gmail.com schreef op 12-3-2015 om 14:54:
> what about a calculated field that uses Year(theDate) and just search on that?
>

In itself nothing wong with that, but on a large database with multiple 
searches you could end up adding a zillion extra calcs, you probably 
don't really need, just for searching. And for faster searching all 
those calcs need to be indexed as well, adding file size. And since 
calcs using related data can't be indexed, that would be a bummer also.

Ursus

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web