Searching date range by day, month & multiple years range

[davedz]

Assumption: Data with valid dates from 1890 through today in mm/dd/yyyy enforced format.

Search example: I would like to find all items that fall between October 1 and November 30 for each year from 1956 to 1974. Stated another way for clarification: find all items from 10/01/1956 thru 11/30/1956, find all items from 10/01/1957 thru 11/30/1957 and so on through 1974.

I've tried all I know and have not been able to devise such a search.

Thank you in advance for any help.

Dave

[Vaughan]

You'd have to do it by entering multiple find requests:

10/1/1956...11/30/1956 (or)

10/1/1957...11/30/1957 (or)

... (or)

10/1/1974...11/30/1974

But that wouldn't be much fun.

Things could be massively simplified if the find works on *whole months* only.

Make a calc field to pull the month out of the date as a number.

Make a calc field to pull the year out of the date as a number.

The find criteria will then be:

month = 10...11 (and)

year = 1956...1974

These calc fields could be stored and indexed, which would make the finds very fast.

[Ocean West]

enter find mode

find */*/1956...*/*/1974

Find

10/*/*...11/*/*

Constrain Found Set

[Ender]

I think that can be done with a Find, but you'll need to break out the year or the month into a separate field. If you add a Year calc so that

Year (calculation, number result) = year(date)

Then you can use two requests:

Date: 10/*/* AND Year: 1957..1974

Date: 11/*/* AND Year: 1957..1974

Though it might be easier on the user if you added a MonthName calc. Then you could search for the month name:

MonthName: October AND Year: 1957..1974

MonthName: November AND Year: 1957..1974

[comment]

I think one request should be enough, with no need for added calculations:

{10..11}/*/{1956..1974}

[Ender]

Wow, hadn't seen that type of search. Good stuff!

[davedz]

Ender, your search provided the desired results. Thanks to you and the others for their suggestions. I did learn the hard way, the brackets are not optional.

Dave

Edited May 23, 2007 by Guest

[The Shadow]

The braces are part of the syntax, you must type or use setfield to set:

"{10..11}/*/{1956..1974}"

as your find as shown in comment's post above.

Edited May 23, 2007 by Guest
Clarification

[Ocean West]

wow you learn something new every day : thanks comment.

[comment]

Hey, it is in the help.

People reading this should be warned that this type of search is dependent on the date format in use.

[Ender]

Hey, it is in the help.

So that's where you get all your smarts. All these years, I've just been asking my Ouija board.

[The Shadow]

If localization is a concern, then you should use Japanese format for your search, so instead of:

    {10..11}/*/{1956..1974}

do the search as:

    {1956..1974}+{10..11}+*

the + indicates a Japense date (regardless of file locale), and it is always in ISO date order of YY+MM+DD. This would work for any date search you wanted to construct in a script regardless of the user's format settings or locale the file was created with.

[comment]

Wow, now that's new - and undocumented to boot. I was thinking of using something like:

Substitute (

Date ( 11 ; 22 ; 3333 ) ;

["11" ; ] ;

["22" ; ] ;

["3333" ; ]

)

to force the local format.

Another interesting point: if you select Modify Last Find, you will see that your search criteria have been converted to the local date format.

[The Shadow]

Well, not completely undocumented, the "Finding ranges of information" help for 8.5 gives one example using +:

That occur on December 31st between 1930 and 1940 in the Japanese Emperor Year era of Showa

S{5..15}+12+31

but it doesn't really give much of an explanation of what the + is for - I would guess the Japanese help file probably goes into it a little more, since that's who the feature is for.

[comment]

Yeah, I have read that, but it's Greek to me...

[The Shadow]

There's actually not too much to it, but I can't say it will be too useful to anyone here. Much like we'll shorten a year to '89, the Japanese will sometimes refer to the year as an offset from the start of the reign of a new emperor, the ranges are:

  Meiji : 1868 -> 1912

  Taisho: 1912 -> 1926

  Showa: 1926 -> 1989

  Heisei: 1989 ->

so, using a year of "h20" you just add 1989 to get the actual year of 2009.

Again, probably not very useful for other countries, but I suspect you don't get a lot of use out of the "Furigana" field options either. :

One of the big benefits of FileMaker 7 moving to unicode storage was that there is no longer any code difference between the Japanese version of FileMaker and other versions, which reduces the extra effort expended by engineers and testers, which theoretically should yield faster and larger FileMaker updates for everyone. That's the reason for the smattering of Japanese specific features that are visible here and there.

[comment]

I don't know about useful, but it's certainly interesting, and deserves a note in my 'Things not in the Manual' KB (which needs to be renamed to Things not in the English Manual'?). Thanks.

Incidentally, it's a pity FMI didn't use the opportunity of moving to Unicode to also implement the bi-di algorithm - I'd thought that would be easy compared to supporting 2-byte languages.

[Vaughan]

This has ben the most amazing thread.

"... to also implement the bi-di algorithm..."

A quick look on Wikipedia reveals that this refers to bi-directional text.

For a moment there I though it was going to be something rude.... ;-)

[comment]

Sorry to disappoint - I'll try to do better next time...

[The Shadow]

I think the FMI management would disagree; that release took so long that the name had to be changed to FMP 7, and a subteam was split off to put out a compelling version to call FMP 6 in the meantime.

The switch to unicode, while necessary, was a *huge* pain that was under-estimated by everyone involved; basically the entire storage system and indexing and text field class needed to be rewritten. The text field in particular was a big problem as the person who was working on it literally *died* during the project, and it was difficult to find someone to replace them.

[comment]

Well, I didn't say it had to be there from day one. But it's been a long time since. In general, it seems FMI is concentrating more on adding new features than on perfecting them.

[Allan H]

I think The Shadow is underating him/herself with an 'intermediate' skill level. That was some pretty fancy find footwork.