Searching date range by day, month & multiple years range

Recommended Posts

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

Share on other sites

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.

Share on other sites

enter find mode

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

Find

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

Constrain Found Set

Share on other sites

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

Share on other sites

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

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

Share on other sites

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

Share on other sites

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 by Guest

Share on other sites

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 by Guest
Clarification

Share on other sites

wow you learn something new every day : thanks comment.

Share on other sites

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.

Share on other sites

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.

Share on other sites

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.

Share on other sites

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.

Share on other sites

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.

Share on other sites

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

Share on other sites

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.

Share on other sites

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.

Share on other sites

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.... ;-)

Share on other sites

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

Share on other sites

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.

Share on other sites

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.

Share on other sites

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...