Find By Month

[Fred in Thailand]

Now I really feel stupid.

Need to do a find on a date field for a given month.

The date field is an auto enter date created.

I do not want to have to enter a date range as my users would never be able to understand Begindate...enddate.

What i would like to do is Hit button, Enter Find mode, Enter Month, Perform find.

The month part is what I am having trouble with.

Thanks

[MarkWilson]

Let( [

YYYY = Right( "0000" & Year( Get( CurrentHostTimeStamp ) ) ; 4 ) ;

MM = Right( "00" & Month( Get( CurrentHostTimeStamp ) ) ; 2 ) ;

DD = "01" ;

D2 = Right( "00" & Day( Date( MM + 1 ; DD ; YYYY ) - 1 ) ; 2 ) ] ;

MM & "/" & DD & "/" & YYYY & ".." & MM & "/" & D2 & "/" & YYYY

)

[Genx]

Alternatively, if you actually want to search for the a single month e.g. January:

Table::MonthField & "/*/" & Year(Get(CurrentHostTimeStamp))

Where MonthField holds the month number you want, or if you just want to run a manual search:

[MonthNumberHere]/*/2007



PS. Mark I'm pretty sure date fields don't care about leading zero's 1/1/07 is the same as 01/01/2007 .. and even if Year( Get( CurrentHostTimeStamp ) ) returned a 2 digit year, 

Right( "0000" & Year( Get( CurrentHostTimeStamp ) ) ; 4 )

would produce "0007"

[LaRetta]

"Hit button, Enter Find mode, Enter Month, Perform find."

Allowing Users to be THIS lazy will bite you. If the current month is December and the User wants to find January - next month? Or the current month is January and the User wants to find December - last month?

I suggest you don't give Users what they want; give them what they need. Use 1) popup for month and year, 2) provide a regular year field next to the month field, 3) provide true range search option by using dropdown calendars on start and end global dates with a Go button or 4) standard start and end date fields for the search. If you provide only a month, you'll be changing it soon anyway, I almost guarantee it. :wink2:

[LaRetta]

Right( "0000" & Year( Get( CurrentHostTimeStamp ) ) ; 4 ) would produce "0007"

Will it? FM sees years as 4-digit even if you only put 07 but it will not see 7 as a year.

[Genx]

Will it? FM sees years as 4-digit even if you only put 07 but it will not see 7 as a year.

I never said that 7 would be accepted as a valid year, I was implying that even if Year() was to return a two digit figure e.g.:

Right( "000007" ; 4 ) would return 0007...

I'm not sure what you're getting at...

[LaRetta]

I might have misunderstood you then.

You said "Right( "0000" & Year( Get( CurrentHostTimeStamp ) ) ; 4 ) would produce "0007"

... and I was saying it wouldn't. It will produce 2007. I think you might have misread the first part in bold then ...

Also "I'm pretty sure date fields don't care about leading zero's " - FM DOES care about leading zeros if you mean you can omit it. That was my reference to 07 vs 7.

Edited December 9, 2007 by Guest
Added sentence

[Genx]

Right, I probably should have made mention of the exception of year - I was referring to months and days as you can probably see in the example, but not so much in the statement:

date fields don't care about leading zero's 1/1/07 is the same as 01/01/2007

My Bad.

In any case, the point I was trying to make is that performing text operations on returned dates in this case, especially on months and days is redundant...

Edited December 9, 2007 by Guest

[LaRetta]

... not to mention that text dates can break depending upon the OS settings. Yeah, sorry I misunderstood then ... you had the 'will produce 0007' after the text-date example on the year.

[Genx]

On an entirely unrelated note, was just experimenting with something, and an odd ball fact, that seems to work anyway:

Using "*" as the day, will always produce the last day of the previous month e.g. Date(3 ; "*" ; 2007) produces 28/02/07 or 02/28/07 in American speak.

[edit]Nvm... I guess it just assumes, anything thats not a number is 0, I seem to continue doing silly things like this argh ... and Date(3; 0 ; 2007) would do the same thing, sigh... 2am in the morning, with this irrelevant post, i bid you goodnight.

Michael, don't yell lol.[/edit]

Edited December 9, 2007 by Guest

[LaRetta]

That IS cool, if used in a calculation but it doesn't seem to like it in a search. Drat!

There are other cool things not documented (that I can find) such as just finding 2007 in a date field, finds the whole year. I'm quite pleased with FM for the expanded search options.

Also, Date ( "*" ; 30 ; 2007 ) produces 12/30/2006! ROCKIN!!

... yeah, Michael, don't yell LOL. We're just playin' :yep:

Edited December 9, 2007 by Guest
Added sentence(s)

[LaRetta]

0 works in the month in place of "*" there as well, Alex. You seem to be right on the reasoning of it. But if true, it would see Date ( 0 ; 30 ; 2007 ) should produce the last day of year or 12/31/2006 but it doesn't. Not quite sure how to resolve this 'apparent' inconsistency.

Correction ... it should see last month of year (and not DAY) so I suppose this is logical after all.

Stranger still ... Date ( 0 ; 0 ; 2007 ) produces 11/30/2006. Now I'm at a loss for words so I'll stop.

Edited December 9, 2007 by Guest

[comment]

It's not oddball at all, if you consider the order of evaluation, and the fact that Date() function parameters must be numbers:

Date ( 12 ; 15 ; 2007 ) = Dec 15, 2007 ;

Date ( 12 ; 3*5 ; 2007 ) = Date ( 12 ; 15 ; 2007 ) = Dec 15, 2007 ;

Date ( 12 ; "1" & "5" ; 2007 ) = Date ( 12 ; GetAsNumber ("15") ; 2007 ) = Dec 15, 2007 ;

Date ( 12 ; "*"; 2007 ) = Date ( 12 ; GetAsNumber ("*") ; 2007 ) = Date ( 12 ; 0 ; 2007 ) = Nov 30, 2007 ;

Date ( 12 ; "abc15"; 2007 ) = Date ( 12 ; GetAsNumber ("abc15") ; 2007 ) = Date ( 12 ; 15 ; 2007 ) = Dec 15, 2007

[comment]

Stranger still ... Date ( 0 ; 0 ; 2007 ) produces 11/30/2006. Now I'm at a loss for words so I'll stop.

It's funny how the mind works. If I told you I needed the last day of the month before the preceding month, you would, without hesitation, write:

Date ( m - 1 ; 0 ; y )

and you wouldn't find anything strange in it. In fact, for any date in January this would return Date ( 0 ; 0 ; y ), therefore Nov 30 of the previous year - just as specified. But for some reason, when you see the intermediate result, it seems strange to you.

[Fred in Thailand]

"Hit button, Enter Find mode, Enter Month, Perform find."

Allowing Users to be THIS lazy will bite you. If the current month is December and the User wants to find January - next month? Or the current month is January and the User wants to find December - last month?

I suggest you don't give Users what they want; give them what they need. Use 1) popup for month and year, 2) provide a regular year field next to the month field, 3) provide true range search option by using dropdown calendars on start and end global dates with a Go button or 4) standard start and end date fields for the search. If you provide only a month, you'll be changing it soon anyway, I almost guarantee it.

I only have one date field to search on. How do i structure it to so the popup searches for Month and Year in the same field

[LaRetta]

I only have one date field to search on. How do i structure it to so the popup searches for Month and Year in the same field

Hi Fred,

There are as many methods to achieve this result as there are types of cars on the road and everyone prefers a different method. Instead of giving you the standard of: create two globals to hold the month and the year, create value lists of each, dealing with improper sorting in the value list etc ... I decided to give you my favorite approach because 1) it uses the least amount of fields/value lists etc and 2) it provides a start-date which comes in handy throughout your solution in many other ways.

This demo only has one hidden secret. Under theDate field is another exact copy of theDate field. In the first date field (on top), allow entry in Browse but not find so regular work can be done in it[color:red]*. On the second copy (underneath), allow entry in Find but not Browse AND attach your value list to it. The value list is based upon data in the field and turns all the dates into the first of the month. The script modifies the find request to produce a range as 5/1/2007 .. 5/31/2007 etc. Also, because the value list is based upon actual field values, you won't have to 1) hard code the years or months and 2) will ALWAYS find records because those are the only ones you can search anyway. It never needs to be modified or updated.

Others may wish to provide alternate methods; this just happens to be the one I find the simplest overall.

[color:red]* I usually provide a separate layout for finding; you may wish to as well. But I've recently switched to providing visual queue by using a button in the background which is same color while in browse but turns yellow in find. You can do this in prior versions using a global container but, as a background, it would flash a lot. By colorizing the layout while in Find Mode, Users don't make mistakes thinking they are in Find and change real data. I realize you have 8.5 so can't use that part but I decided to include it for those with vs. 9.

finddates.zip

[Fred in Thailand]

Thanks, LaRetta

It still amazes me how helpful the people are on this forum.

Only one thing wrong with your demo... it only returns one result for the find. Pick 5/01/2007 and it returns one record even though there are 5 that should be in the found set. Same with the other dates.

Every thing else I understand. Neat about the hidden field. Like that and will use it often. Also like the scripted find but being very (VEEEEEEEEEEERRRRRRRRRRRY) new to scripting I can't figure out why it doesn't return the full found set.

Thanks again for the help and looking forward to your reply.

Fred

Edited December 17, 2007 by Guest

[LaRetta]

Hi Fred,

You can't use the menu to perform the find - you must use the button. Click the button, select 5/1/2007 from the popup and hit [enter] ... just as you would with any scripted find. 5 records will be returned.

If not, post back because it surely works for me time and again. If your Users don't want to type a range, or don't want to perform the find themselves, a script must be used instead of menu. :wink2:

[comment]

It doesn't work for me either, until I clone it. I am not sure exactly why: if I pause the script before the Set Field[] step and check the entered date, it is a valid and correct date. Still, the Set Field[] does nothing.

This is the second time I have seen 'Always use system formats' fail, and I believe it's a bug.

[LaRetta]

I'm intrigued ... I've created the files again; once in 8.5 and once in 9.3 (I confess that the file you had was created in 9.0. :

I can't make it break in any versions back to vs. 7 but then, it's not me who breaks anyway, I assume it's those with different OS settings. Could you see if they also break for you, Michael? I can't imagine that this behavior has been there all this time (since 7?) and we just didn't notice it.

BTW, 9.0v3 recently corrected misinterpretions in value lists. That's why I'm particularly including 9.0v3.

tests.zip

[comment]

I tested 8.5 against 8.5, and it's the same bug. It works if I choose file's settings, but then dates look wrong, of course. I haven't installed .0v3 yet.

[Fred in Thailand]

As usual, Comment is right. The new files also break. If i clone the file it works like a champ. I am using a European date format in my system settings. If I change to US English, your test file works great. Change back and it doesn't. Same when I clone the file. Clone enter data no problem. Change OS date format Break. Must be a bug.

[Oldfogey]

It breaks for me, too. I'm on European (= Australian) date formats, using 8.5V2. The Set Field does nothing.

1. When I change to Use File's Format, it works fine.

2. When I use System Format, it breaks BUT it doesn't break when I use Insert Calculated Result instead of Set Field. (I vaguely remember that Set Field does have problems with date ranges; works 100% with >= and <=.)

[LaRetta]

(I vaguely remember that Set Field does have problems with date ranges; works 100% with >= and <=.)

I appreciate you stepping in. I don't recall ANY posts about Set Field[] having problems using ranges except in vs. 7 when attempting to search for a number with decimals (it became confused when using two elipsis instead of three) which still breaks, by the way (three are always required).

AFAIK, this break with Set Field [ ] on date range is limited to OS file setting alone. If not, I'd really like to know for SURE because using Set Field [] with date ranges is a staple of the FM diet and it breaking would be very serious!

LaRetta

[Oldfogey]

Sorry, Laretta, I can't remember how it broke. I think it just didn't work - as with your sample file. I just used 'Insert Calculated Result' instead and it worked fine. Saw no point in posting as I assumed it was my problem. (I'm sure I've been told by an expert that Set Field should not be used with date ranges.)

I rarely use date ranges anyway, preferring to use a calculation.

Out of interest, I re-created your script in a file I happen to be working on. It works fine - almost. Date(1; 0; 1996) results in 31/01/1996 (or in funny notation - 1/31/1996). Of minor interest, you have "..", which I'd copied; FMP dutifully changed it to "..." when executing the Set Field. I just wish it would fix all my other errors as readily!

Reminder, I use Oz date notation.

[LaRetta]

Hi Paul,

Two elipses isn't an error - it is even in FM Help and says you can use it. As for Set Field[] being in error with date ranges, I still don't buy it. You prefer to use a calculation intead of date ranges? I don't get that at all. As for the date 1 ; 0 ; 1996, it should produce 31/12/1995 which is total legal date.

Oh. And Insert Calculated Result must be on the layout; Set Field[] has no such limitation. And I don't know what you mean by Oz date notation. If you mean Oz notation, I see no connection here. If you are referring to the fact that you use international dates then okay ... I've just never heard it described that way and I like it!

LaRetta :wink2:

Edited January 9, 2008 by Guest

[Oldfogey]

Hi Laretta,

As for Set Field[] being in error with date ranges, I still don't buy it.

OK, don't. I'm just telling you what happens on my system. The 'Set Field' just sets the field to the first date. If you change the script step to 'Insert Calculated Result', it works. Maybe it's a bug, maybe not. (And, yes, I hate ICR and try to avoid it because of its problem.)

You prefer to use a calculation intead of date ranges?I don't get that at all.

Maybe I'm missing something but I've seldom had a need for any complex date range Finds and find it easier to set up a (binary) field 'Date in Range' and find on that, using the dates (usually) supplied by the user.

As for the date 1 ; 0 ; 1996, it should produce 31/12/1995 which is total legal date.

Yep, 31/12/1995 is a totally legal date; no argument on that score. BUT that is not what I get from Date(1;0;1996); I get 31/1/1996. I haven't come across any formal FMI statement re the use of 0 in Date calcs. Is it just a quirk that someone discovered or is it part of the specs for the function? If the former, then I wouldn't use it in a fit; if the latter, then I guess I've found a bug but I'm not losing any sleep over it.

Sorry, if I don't sound too upset about the overall problem, which seems to be related to 'Use System Settings'. We, up here on Oz, are so used to problems arising from dates in US software that, after the dummy spit, we just learn to put up with it and move on.

Glad you like Oz dates. Maybe you could get your Congress to change to them?

Cheers,

Paul

Edited January 11, 2008 by Guest
Fix result of Date(1;0;1996)

[comment]

Using a zero as one of the parameters for the Date() functions is perfectly legitimate - see:

http://www.fmforums.com/forum/showtopic.php?tid/177012/post/207066/#207066

I would be VERY worried if I were getting "31/1/1995" as the result of Date(1;0;1996). That is an absolutely incorrect result, and it might indicate a more serious problem with your file and/or system.

Regarding Set Field[] vs. Insert Calculated Result[] - I see the same thing in LaRetta's file, but I believe that is just another symptom of the file's being "confused" about which date format is in effect. This bug aside, I am not aware of any reason why Set Field[] shouldn't be used with date ranges.

[LaRetta]

Hi Paul,

On the sample file above, it breaks (for everyone with oz dates) because it doesn't put anything in the field. You even agreed that my file doesn't set the field at all. But ...

I'm just telling you what happens on my system. The 'Set Field' just sets the field to the first date...Maybe it's a bug, maybe not.

This is different than the break described above. So is this standard behavior for you if the file is NOT produced on US system? A bug is a bug only if it is reproducible. So let's reproduce it. We have an opportunity here to identify true bugs or debunk misinformation. There are others with oz dates - lets see what they get (on a file of yours or testing new files). And maybe you can test one of these newly created files as well and see if your results remain the same. It will need to be someone with oz dates testing someone with oz dates so that leaves me out unfortunately.

Yep, 31/12/1995 is a totally legal date; no argument on that score. BUT that is not what I get from Date(1;0;1996); I get 31/1/1995.

And the same here - it should be tested so we know! And no, Congress doesn't listen to me and I ignore them as well.

LaRetta

[Oldfogey]

Thanks, Comment, for that really interesting info. (At least I was in good company there for a while.) Live and learn! Again.

Hopefully I'll get my apparent bug sorted before I find a need for a month of zero.

[Oldfogey]

OK Laretta, I'll check on a few of my other files and see how they behave and also try another machine. I've been hoping another Aussie would butt in and confirm, or contradict, my experience.

[Oldfogey]

Comment, The result of my date(1;0;1996) was 31/1/1996 not 1995. I've corrected my original post. (I only ever make really important typos.) However, I now realise this is still a problem.

[Oldfogey]

The saga continues.

1. I cannot reproduce Date(1;0;1996) giving 31/1/1996. Maybe it was the mushrooms. Unfortunately, I've discarded my original data.

2. I've now carried out a few tests. Three files - Laretta, Dates01 and Dates02 - all doing much the same. Laretta and Dates02 created with US system dates. Dates01 created with OZ system dates.

Tried each with both "file's format" and "System's format" in both OZ and US system regional settings. (Same computer, different settings.)

Laretta's fails when I 'Use System format'; otherwise it's fine with "Use file's format" in either OZ or US system.

Dates01 fails in every situation.

Dates02 file OK with "File's format" in both US and OZ system. Fails with "System's format" in OZ system.

It looks to me as though there are two problems - one with "Sytem's format" and the other with OZ format.

My two files are attached.

TwoDateFiles.zip