Find problems

[dajamle]

Hi,

For some reason, I cannot get a script to work that will find all records in a table that have a given value, in my case, today's date. I am making a library system, and want to find all loans that are due back today. This is my script so far:

Set Error Capture [On]

Go to Layout ["Loans - Due Today List" (Loans)]

Enter Find Mode []

Set Field [Loans::Date to be Returned; "= //"]

Set Field [Loans::Checkbox Number; "1"] (This just checks if it has already been returned)

Perform Find []

Print [Restore]

Show All Records

Go to Layout ["Main Menu" (Admin)]

For some reason, even though there are several records that are due back today, when you manually enter find mode and type in todays date, it doesn't work either. Any ideas?

If you're confused, I have attached the file.

(Login is admin/admin)

Computing.zip

[comment]

Try removing the space in "= //".

[dajamle]

Nope, that doesn't work I'm afraid. Any other ideas?

[LaRetta]

You have 3 records in your demo. The third is 3/4/2005. It won't find you are correct. But if you type 4/3/2005 it finds it. This is when saved with File Settings upon open. If you save based upon current System Settings, it displays 4/3/2005 but you need to search for 3/4/2005 to find the record.

Did you change your system settings for the file and if so, how? Your Creation Date sets correctly and allows a find.

If you view your DateTakenOut and DateToBeReturned on a table you will see what I mean. After record two, the date format has changed. DateToBeReturned still incorrectly reads the system date format and so will all new records.

Of course foreign date settings confuse me (don't they type month/day/year like regular folks?) ... it's like driving up the wrong side of the road. It gives me a disoriented feeling. I like it.

People pay money for drugs to get the high FM can give naturally.

L

[comment]

Nope, that doesn't work I'm afraid.

It does over here.

Computing.fp7.zip

[Oldfogey]

Here too. (Must confess, I'm amazed it works anywhere!)

We also use the sensible date format (ddmmyyyy) but FMP told me that the file had different date settings from my system settings. Tried letting it use first the file, and then my system, settings; both worked when the blank in front of // was removed and not before. This suggests to me that there is something wrong with your file's settings. One of my customers has a similar problem; for some unknown reason, one file occasionally decides its date formats are different.

[LaRetta]

I've been playing with this thing. It bugs me. Converting the dates to numbers, they are correct.

When asked, if I select System Settings (upon startup) and then manually perform a find in the Date Taken Out for 4/3, it finds the first record. Manual find for 4/17 in the Date To Be Returned field says no records found.

But maybe others with US date formats can test opening file with System Settings and see, because ... If I change the Date Taken Out to 3/14/2005 the Date To Be Returned is 4/4. Then entering 4/4 FINDS it! Even entering // or [space]//.

If I create all new date fields, they work.

The word anomaly comes to mind ... it's mixed up on which date setting it's using. That's why it works when I enter 4/4 ... because that's a valid US date format. 17/4 isn't and it doesn't find 4/17.

I noticed backups are saved using Unicode instead of Windows Ansi (and if the data was imported?) I wouldn't think that would affect the field but Unicode does affect data for finds. However, blanking those fields and typing the dates again doesn't help. It's still backwards.

[comment]

I don't think this has anything to do with format settings. I tried finding today's date in my own file. "=//" finds it, "= //" doesn't. Kinda makes sense, since "=" means 'exact match'.

[LaRetta]

""=//" finds it, "= //" doesn't. "

Agreed, it shouldn't work as =(sp)//. But with US settings =// doesn't find it (without the space) nor does //. And BOTH should! All of the prior tests work fine on the Date Taken Out but not on Date To Be Returned - not under US settings.

2/17 says no records found (based upon 2/3 Date Taken Out) but it's there - 2/17/2005.

If the Date Taken Out is 3/20 and the Date To Be Returned is then 4/3 (and displays 4/3/2005), FM can't find it typing 4/3. But if I type 3/4 in that field it finds it -- and then displays 4/3/2005. The other date fields respond as they should.

Because the finds are not working on that ONE date field as it should work when performing a manual find entering mm/dd or //, I think that one field is funky.

Gee, LaRetta, what did you do all night? Well, I studied Unicode searches, date formats and other such fun things ...

[dajamle]

I feel a right pleb now that it works for you guys and not me, lol.

Anyway, yes, I am using the dd/mm/yyyy date format, as is the same on my computer. I really don't understand it though. I've tried making it work without the script, simply by selecting and copying the date directly from the record, entering find mode, and then pasting it into the field, and it still says it cannot find the record. What really annoys me though is that my other script that finds overdue books works fine, and surely that is slightly more complicated find?

I have previously tried setting the field to the current date in the Admin table, as well as using the Get (Current Date) function.

I had some real fun destroying my mouse last night to perform a simple find.

Surely there is a reason why it works (at least to some degree) for you guys, possibly a problem outside Filemaker? I will have to have a look at my computers settings when I get home.

[Vaughan]

The "=" find operator is for TEXT field searches, not Date searches.

[LaRetta]

Well, I'm not being listened to here. But that's okay. I'll just try again. There is something wonky with that date field on some computers - maybe not all but some. Yours and mine are two of them. What do they share in common?

To prove that your script is fine, I'm reattaching your demo with only two modifications ...

I changed your script to "//" (removing the = and the space)

I changed the find to search your Date Taken Out field instead

BTW, it also works with "=//" (and I suggest you add the = back in and try that also). You will see it works just fine when searching a healthy date field.

Your script is fine and will now work for you. That was my point. Finds work well on every other date field; either manually or using your script. But they will not work properly on that ONE date field (Date To Be Returned) -- at least not for you and I.

Delete that date field, point your script to a new Date To Be Returned field and you will see it works. I hate to think you're wasting time hunting for the problem elsewhere. The problem is the field itself. I am convinced that, if someone else tried this file, they could also reproduce the wonky results. What is different about our systems from Comment's and Paul's? Unsure, but the fact remains.

I have tested this file over 50 times. All other dates work fine ... all other searches on (Get(CurrentDate), //, entering dates manually all work ... They all work whether selecting System Settings or File Settings ... they all work with your script ... all dates work as the calculation pointing to your Date Taken Out field ... all work ... EXCEPT FOR THAT ONE FIELD (Date To Be Returned).

Again ... The field is wonky. Ditch it or you'll have continual problems with it. I stand by that statement. Now try it then come back to this thread and tell me whether you still have a problem.

LaRetta

Computing.zip

[dajamle]

I understand what you mean LaRetta (I wasn't ignoring you). I tried the system on a Mac, as my college uses Mac's, and it worked right out of the box, so it is clearly a problem with my computer at home. It's very strange, but a relief to know that it does actually work... just not on my PC.

Just for reference, the script is working using just "//" being searched in the 'Date to be Returned' field. However, this didn't even involve me altering the field like you suggested, as I downloaded the original attachment up on the first post.

As for your question about what is different about our computers compared to others, I have no idea. I would generally say it is because we are British, but you're not, so you obviously don't use the same date format as me. We are also using the same OS, but then again, so is Paul.

Anyway, thankyou very much for the help you've all given. It has been helpful, especially seeing as I'm new to the Filemaker world.

[comment]

What exactly are you testing? So call me logical again, but there are several issues involved here, and if you really want to get to the bottom of this, I suggest you should start isolating.

For example, what is "//"? I'd never thought of using in it Find[]. It seems to be the equivalent of GetAsText ( Get ( CurrentDate ) ). And that goes thru formatting.

I think you should start by trying to reproduce the problem in a fresh file. Have a regular date field and a calculated one, and see if they behave differently. (They don't in my file).

If you cannot re-create the problem in a new file, clone the problematic file and see if the problem persist.

[Oldfogey]

I notice that, if you click in the field, the leading zero on 03 does not show although the 04 is there.

Also, your fields in the layout where you register the loans have different formats from the one on the 'Loans - Due Today List'.

I'm very doubtful about using "=//" because, as far as I can see, there is no way you can tell what FMP is trying to find. I defined an exra field called NewDate and included a <Set Field [NewDate, "//"]> in your script. This produced a "?" in the field.

I've also tried your script without the "=" (ie just "//") and this works. Does the "=", meaning 'exact match' have any meaning with date searches?

[LaRetta]

Hi Comment,

I assume you're asking what it is I'm testing with it?

So far, it works on 9 systems tested and fails on 3 (two of ours and dajamle's home system). I have 'Save a Copy As' and Cloned it. I have created 15 date calculations in each example and tested them all. I have created new records and tested all 'find combinations.' It fails on manual finds of 4/5 for God's sake! I use a checklist.

The two systems it fails on here are both Windows XP Home Edition set to US Settings. And that ONE date field fails consistently -- has never worked on either. I'm aware it simply isn't the International setting; nor whether it's Mac or Windows but all things happen for a reason and this is no exception. Paul's client is another and I have no doubt there are others.

On both these systems that it fails, when I delete the offending field and create a new one, the problem is gone. My point is that if he keeps that date field, sometime somewhere (when he least expects it, smile), it'll fail for someone using his solution who (unluckily has a similar box as ours that fail).

It takes one second to delete and re-create that field. It doesn't matter whether it works for his friend's system or your system or 20 systems. It fails on some and it isn't because of his find. I also had never used // for a find but again, it performs fine on 85 finds on my system and 36 on another - on ANY date field but that one. But that field also fails for regular finds! The field has mental problems (as do I probably according to some) and needs to be put to rest, as probably does this thread.

But inconsistencies intrigue me. And I plan to hang onto this puppy and continue to test it on every system I get my hands on (documenting their configurations); and some day I'll find the common thread. But I certainly wouldn't keep that field in any solution of mine!

LaRetta

[LaRetta]

Hi Paul,

"I've also tried your script without the "=" (ie just "//") and this works."

But it doesn't for our two systems and it doesn't work for his home system.

Forget the =// ... forget the // ... it fails for us on 4/5.

When the Date Taken Out is clearly 3/22/2005, that field displays 4/5/2005 (Date Taken Out + 14) but won't find it manually by entering 4/5. But it finds it if I type 5/4 and, after finding it on 5/4 (which is wrong), displays 04/05/2005 in the field.

All other date fields or new fields created work correctly. Delete the field and your problem will be gone. I'm done.

[comment]

I assume you're asking what it is I'm testing with it?

No, I am asking what is the purpose of the 50 tests. Do you now know more of the possible cause of the problem than before?

I can't figure from your reply if the problem persists in a cloned file. I gather that you cannot reproduce the problem in a fresh file?

[LaRetta]

"Do you now know more of the possible cause of the problem than before?"

I know what the problem is not (and that is significant). Most discoveries are based upon eliminating what ISN'T more than discovering what IS. It is not because of the script nor is it because of using // or even =//. It is not whether International or US date. It is not because of the platform. None of these issues alone explain it.

"I gather that you cannot reproduce the problem in a fresh file?"

You are correct, I can not reproduce the problem in any other file. Correction: A cloned file breaks on that ONE field. Any other date field (within that same file) works fine. The problem is isolated to this one field in this one file (or a clone of it) -- and it only breaks on some systems. That is why I'm convinced it's the field itself.

But there is a consistency here somewhere on why it works with most systems but not some and that is what intrigues me. If the field was simply broken it still would respond in a consistent manner, ie, broken for everyone. The peculiarity that keeps pointing to a problem in International/US settings is this:

Specific example: Delete date from Date Taken Out. Type 3/22 in that field (and it displays 3/22/2005). Date To Be Returned (the funky field), changes to display 4/5/2005. So far so good. Manually enter Find and type 4/5 in that field. No records found. Manually find 5/4 in that field and surprise ... it finds that one 4/5/2005 record and displays it as 4/5/2005.

Illogical!!! This 'reverse' logic has been repeatedly reproduced through several dates tested (but only on this one field) on both of our systems on which it breaks. Other date fields do not respond with the same reverse logic on those two systems. And the 9 systems on which it works will NOT find (applying the reverse 5/4 logic; as they shouldn't).

I can not test 4/17 because FM refuses to accept 17/4 as valid (of course). But any other date (which can be reversed and still be a valid date), such as 1/3 can be found by searching for 3/1. Only on this field - and regardless whether using System Settings or File Settings. I believe there is an internal mismatch on that one date field, ie, FM thinks it's 1/3 but somewhere it thinks it's International (3/1).

When I create a new date calc, it works fine. That's why I think that field should be deleted. If it is used in other calcs or situations, it can't be trusted. It's a calc for goodness sakes; simple to create a new one.

I suppose I'm the only one that's intrigued by this anomoly. I am also documenting File Options > Text settings, Preferences > Font Settings, FM version and OS version, looking for consistencies (on each system I test). There is logic in this somewhere on why it breaks only on some systems. I may never find it but it won't be because I didn't try.

[comment]

All I say is let's be a bit more systematic in the test process.

I would suggest you take the clone file, and create a fresh new layout that includes the field in question. See if it behaves in the same way on the new layout.

If not - select the original field and explore its formatting.

If yes - go to Define DB and duplicate the field, and see if a copy behaves in the same way.

This, I think, would provide some meaningful information.

I am sorry if I sound like I am giving you instructions, but since I can't reproduce the problem, I can't do it myself.

[Oldfogey]

Laretta,

I think you are making a mountain out of a molehill. You normally use mm/dd/yyyy so I presume you are changing this to play with dajarnie's file? My experience is that FMP does not do a good job of adjusting date formats when you flip-flop between system formats. (Vaughan, any comment? Like me, you must be continually having fun with downloads from the forum.)

Also, dajrnie has different display formats in different places.

[LaRetta]

Oh Paul, you misunderstand me. I AM having fun!! It's precisely these kinds of challenges that make it impossible to sleep. Don't you have a driving need to know and understand? If I can figure out the pattern to this seemingly inconsistent issue, it might save headaches in the future. When I am presented with a piece to a puzzle I can't kick it aside as incidental. In my mind, this is an opportunity.

I'm not changing anything with the dates, Paul. I'm expecting a date field to behave as a date field. It does not on three systems but does on nine. If one reads my examples they will see a date should be found by my attempts (regardless of which setting is used) and should be found predictably on ALL SYSTEMS tested. THAT is the inconsistency that I want to nail down here. And I have the demo in my hot hands and two systems which cause it to break. Rarely is one presented with such a solid exhibit which (without fail) produces the same unexpected results.

Regardless of the setting selected on file startup; regardless of whether you enter mm/dd/yyyy or dd/mm/yyyy, performing a scripted find on a date field of Get(CurrentDate) should ALWAYS work. Guess what? Works on 9, fails on 2 -- but only fails on ONE date field.

Hi Comment,

I dismissed layout issues, duplicating the field etc. because the date field works for you. I assumed that meant there was nothing wrong with the layout or formatting and instead I turned toward the OS side. I think that was flawed logic because I suppose the OS could affect formatting as well. I'll run those tests tonight here at home and then at the office in the morning. Thanks for the ideas. I think that field is just buggy. But why it's buggy on some boxes and not others is astonishing to me.

One thing I just noticed ... in my other FM files, when I create a new date and it's left to default Leave Formatted as Entered and I type 4/5, it displays 4/5/2005. Now however, on creating a new file, it displays them all as 04/05/2005. Even if I go into my existing solution and create dates then exit, it reverts the date on creating new files to 04/05/2005 format. This is the same format as Computing.fp7. I've NEVER had it format that way on default field creation before. Okay, I'm taping "Never Assume" on my monitor and I'll let you know my results with formatting and duplicating file, field, and layouts.

LaRetta

[LaRetta]

Another thing I should mention ... In every test, I always create a new table layout for the fields. I always do that when working - just habit - and I didn't think to mention it but it's important I think. All finds were performed on the tables. And I haven't touched my OS Settings on any of the 11 systems. I have only changed my selection Settings on FM startup. These 11 systems have performed date finds using FM for 18 months without a problem.

I also noticed I inherited a plugin called PC FM Credit Card 'StartScript plugin' but I find no indication of it in a script in the file. Also, there is a backup script (I mentioned before) which exports the Loans table (and Date To Be Returned unstored calc wonky date field) using Unicode(UTF-16). I remain suspicious of importing that date (probably needlessly so).

[Oldfogey]

LaRetta, My apologies. I didn't read all of your posts properly and you are dead right that date fields should behave like date fields, irrespective of the system settings.

As you've pointed out, there is obviously something wrong with the original field in the original file. I'd say your chances of finding out what is wrong are pretty negligible. Let us assume it is some kind of corruption. This may well disappear when you copy the file to some computers but not to others.

LOL!

[LaRetta]

Paul said... I didn't read all of your posts properly ..."

'Tis okay, Paul. Because it indicates that I failed in explaining it properly. If I had, the field anomoly would have been very clear to those (suffering through) reading my (Lord forbid) long posts.

Let us assume it is some kind of corruption.

I too have suggested that dajamle delete the offensive field but dropping it there would take the fun away. Because even if corrupted, Paul, the fact that the field behaves differently on systems is giving me a glimpse of how FM interprets and translates (at least I THINK that's what I'm learning).

It's amazing what I'm finding out about font formatting, sychronizing, OS settings and the like ... things that I wouldn't pay as much attention to until I have a problem. But understanding them now will prevent a problem in the future PLUS it is enhancing my FM knowledge on important issues.

I'd say your chances of finding out what is wrong are pretty negligible.

Thank you! This is exactly what I needed to hear right now!! I'm buried in month-end and I doubted I'd be able to play on this demo tonight but I adore going against odds. My day tests (on the 9 systems) were interesting but I want to run the test series on my home Developer system (ah God, I liked calling my computer that ).

I think I've filtered down that 'negligible factor' you mention and expect results soon. I can taste the Holy Grail now. Stand by for test results, Comment!

LaRetta

[LaRetta]

Ok. Spent all night consolidating my tests, researching the patterns, reading Windows Tech articles. Now I have to go to work. I failed. All I know for sure is this:

The field worked on cloned file except once (I indicated a clone failed but upon repeating it, it worked). I had misread my checklist which had gotten very messy or my eyes were giving out or something because I couldn't cause a repeat break on cloning and I cloned it another 20 times.

In original file, original field breaks no matter what I do; settings, formatting, layouts etc. (on two systems). A duplicated field works in Original file and will not break anywhere. It works on all OS & FM versions tested. Only consistency (ONLY consistency) is that both systems it fails on (original file) are Windows XP Home Edition. I've studied system settings until I'm blind.

Field only fails in the one file. Just one stupid field in one stupid file on two stupid boxes and I can't find out why. I will keep this file in my Unanswered folder and someday I may attack it again.

But for now ... clone the file or delete the field. The field is just plain wonky and so am I.

L

[comment]

IMHO, the field is "wonky" when the file is run on a system with different settings. That is nothing new. The solution is to clone the file. IOW, the field is NOT "wonky" on the originating system.

As for the difference between the systems - who knows (and who cares). The fact is that the original file formatting is mixed with the local system formatting. So perhaps XP Home edition does its thing a bit differently from other Win systems.

I believe that using // to set a date field is not a good practice: instead of wondering why it doesn't work on XP HE, one can just as well wonder why it DOES work elsewhere.

The only thing that bothers me is that earlier you indicated that setting the field with Get(CurrentDate) also fails on some systems. That shouldn't happen, and could be a FMP bug.

[LaRetta]

Comment said ... the field is "wonky" when the file is run on a system with different settings. That is nothing new. The solution is to clone the file.

I don't doubt you (for goodness sakes no) but if true why does FM even give a person an option of using System Settings & File Settings upon startup? Is it only so they can clone it immediately (using Local Settings) before ever using it? FM Help and Manual explains how to clone to internally change the system formatting but not why ... or how important it may be - very little explanation is given at all. If it causes the dates to wonk-out FM should tell us. If using a file (uncloned) by selecting the Settings on Startup isn't wise, FM should open with "This file was created using different settings. Only use a clone of this file (Local System Settings) otherwise you can't trust the dates."

Even FM Help describes how to use System Settings in scripts at startup. What about all those people using the same solution in many countries? Do they have a lot of date problems? It makes me want to clone ANY file I receive just to be safe.

I believe that using // to set a date field is not a good practice: instead of wondering why it doesn't work on XP HE, one can just as well wonder why it DOES work elsewhere.

Can you explain why you don't think this is good practice? I don't believe you say things flippantly and so there are good reasons. But even FM Help uses // as an example find and it appears to be quite valid. FM knows it's a date and so do I (even though I've never used it in a find). I've read many posts using it (successfully) in scripts. But when file/field failed on Get(CurrentDate) which was my control test that's when I went, "Ahhh come on ... this field is BROKEN!"

As for the difference between the systems - who knows (and who cares).

I no longer care. My 7-hour middle-of-the-night dredge through Microsoft version comparisons and tech documents was a (painful) total waste of time. Truly, 11 systems is not sufficient control group to say it fails on XP Home Edition and not others. I can only say that it fails on two of them consistently. There simply are too many variables (I ended up with 130 configurations to compare) for me to pin this puppy down. If it were an FM bug, I would think it would break and work the same no matter which Windows version I was using simply because I was using the same US settings. A bug can also be improper communication between several programs as I suspect is the case here (OS/version/FM communication). But the Microsoft world is a nightmare that I hope to never enter again. I learned a bunch about FM system settings, formatting and OS settings and I'm glad I did it. But I've been re-born and I don't want to hear about another bug.

Paul, I've appreciated your input sir. And Comment? Thank you for being patient with my insatiable desire to understand this issue. Most everyone else quit reading this thread 20 posts ago. And I do still care. I think it's all important stuff. But I've run out of steam at least and I'm moving on (for now). Any input to the above or final comments, comment?

LaRetta (the ex-bug chaser)

[comment]

IMHO, using current system settings on a "foreign" file is only a work-around that enables us to work with the file without going crazy. The dates are displayed and can be entered in the fashion that the local user is accustomed to. But month names, for example, remain to be displayed in the "foreign" format. So for permanent use it is best to clone the file.

You touch on an important point regarding trusting the dates. You can trust the DATES as such. You should be VERY wary of anything that undergoes a transformation to text and back. For example, GetAsDate ( text ) can break very easily when the file is moved to another system.

That's why I don't think using // to set fields is good practice; as I said earlier, I tend to think // is code for GetAsText ( Get (CurrentDate) ). I may be wrong about that, but barring evidence to the contrary I am not inclined to trust it in these circumstances.

BTW, I can say things flippantly and still have good reasons...

[Oldfogey]

Post deleted by Oldfogey

[Vaughan]

"look at a REAL date problem on my post"

Has the rash come back?

[Oldfogey]

Yes! In spades.

I'm about to delete that post. After spending the best part of an hour trying to describe my problem clearly and accurately, I found it isn't a problem at all - well not one with a solution, anyway.