Find request including wrong records

[Slugbooks]

So I am trying to do a really simple find in a calculation field. I want to find all the records where the qty field is greater than zero.

The problem is no matter how I try to enter the criteria I keep getting records where that field is zero, But it is only finding some of the records where the qty field is zero. On one client it kept finding the same 28 records with a zero qty. On another on it was finding 32 records with a zero qty. I am scratching my head.

I even went so far as creating a boolean field based on if the qty was >0. the field works and displays the correct information. But when i use that field for a search i get the same set of records including those zeros.

I have no idea what the problem is.

[Genx]

try >= 1.

Also, are you sure it's a number field?

[Oldfogey]

Also, does your calculation always get calculated? Have you flagged 'do not calculate if all ....empty'? This can leave you with empty fields and FMP does not treat empty as if it were zero.

[Slugbooks]

Yes I have double and trippled checked that it is a number field. I mean it is a calculation of other fields. I have also gone in and any one that was blank inserted a zero. So there are no blank ones. I also tried just >1 and it still came up with records with zeros.

I unchecked the do not calculate if all fields are empty, did not change anything and i have tryied it with it clicked does the same thing.

What is going on is there is a field that takes a "Qty" to order and subtracts how many we have in stock and how many have been ordered already and calculates a total.

Then there is another field (call it "real order qty") that has the calculation if (Qty < 0; 0; Qty) both fields are number fields.

I am trying to do a find in the "real order qty" for ones larger than zero. it is only a few records with zeros that it still retrives. And no matter how I change the way I do the search I always get the same records with zeros. The other odd thing i have noticed is that when I sort the list Filemaker is not selecting the new record that was on the top it keeps selecting the record with the lowest ID. This is the first time I have seen this happen.

I have been racking my brain for 3 days now.

[Genx]

Is your calculation field stored or unstored?

[Inky Phil]

Try reindexing the field

(field options > turn off indexing>come out of options > go back in again and turn indexing back on).

There seems to have been a lot of this of late (particularly with 7) and this has cured it every time I suggested it

Phil

[Slugbooks]

the calculation field is unstored. it has to recalculate as information changes.

[comment]

Is your application patched up to 7.0v3?

[Oldfogey]

Keep racking!

You don't by any chance have un-rounded fractions in there do you, with a display showing zero decimal places?

The next step might be a search for gnomes or elves. (I have a friend in Cleveland who can sell you a special torch to make them visible. : )

[comment]

Rounding errors was the first thing I thought of, but this pretty much rules them out:

I also tried just >1 and it still came up with records with zeros.

[Slugbooks]

Yep updated to 7.0v3.

So a few more mind numbing hours and have discovered a few things, not sure how it will help.

So there are about 2000 records.

Of which 32 are the problem.

When I do a search for records w/ qty > 0 I get 205 records, including those 32 which really have a qty of zero.

When I do a search for records w/ qty <=0 those 32 records are NOT included.

Since non of the records had a qty of 2, I changed those 32 records to that. I did a search for qty = 2. File maker found NO records even though if I did the > 0 search they are there with their clear qty of 2.

I think I mentioned it before, but i even created a boolean value based on if the qty was greater than zero. For those 32 the boolean value was 0 and all other records with a qty of zero.

When search in that boolean value for the true values those 32 records were there.

I am not sure how to make it any clearer or simpler to figure out what the problem is.

So far I have been unable to find anything else unuseable about these 32 records.

I am not sure a tourch would help...maybe a sledgehammer.

[LaRetta]

Questions:

1) Are you networked? Are you sure there are no other copies of the file accessible? One client may be finding one file; the other client may be finding a different copy.

2) I assume you have opened the Find Request within the script and verified that it is searching the correct field? Also, how are you listing the Find Request?

3) Are you sure you are not using Perform Find/Replace [] instead of Perform Find[]?

4) Have you experienced a crash while the file was being served?

5) Do you have a mix of versions, ie, some clients on 8.0v1?

[Genx]

It's really hard to help if we don't know all the facts.

Qty... What type of field is it, number, text, calc. Is it manually entered, by script, auto entered, is your default indexing language english etc. etc.

Better yet, if you can, wipe your sensitive data delete all non essential relationships, layouts etc. everything you want to hide from us poor theives :.. and then post the file.

We'll see if we can replicate the problem on xp, or find the problem if it in reality exists.

If no one can work out the problem, it might be that those 32 records were corrupted during a crash -- if you've had any - check the server logs if your solution is networked -- if not, try ridding yourself of those 32 records, and re-entering them - see if you can replicate your problem then.

[Slugbooks]

1) Are you networked? Are you sure there are no other copies of the file accessible? One client may be finding one file; the other client may be finding a different copy.

I am networked there is only one server that has the files, and all clients get their info from that server.

2) I assume you have opened the Find Request within the script and verified that it is searching the correct field? Also, how are you listing the Find Request?

I had written a script, but after having so much confusion I scrapped the script for no and was trying to do this search in Find mode entering the criteria in the specific field.

3) Are you sure you are not using Perform Find/Replace [] instead of Perform Find[]?

In the script I was using a Perform Find[], but like I said I am not use the script. I am just doing the find manually through find mode.

4) Have you experienced a crash while the file was being served?

I have had Filemaker quit responding while it was trying to prefrom the find and I had to force quit the client. To my knowledge the server has not crashed.

5) Do you have a mix of versions, ie, some clients on 8.0v1?

Nope just version 7. on all clients.

Thanks for everyones help.

[Slugbooks]

It's really hard to help if we don't know all the facts.

Qty... What type of field is it, number, text, calc. Is it manually entered, by script, auto entered, is your default indexing language english etc. etc.

Better yet, if you can, wipe your sensitive data delete all non essential relationships, layouts etc. everything you want to hide from us poor theives .. and then post the file.

We'll see if we can replicate the problem on xp, or find the problem if it in reality exists.

If no one can work out the problem, it might be that those 32 records were corrupted during a crash -- if you've had any - check the server logs if your solution is networked -- if not, try ridding yourself of those 32 records, and re-entering them - see if you can replicate your problem then.

I will work at being able to show more information, not really sure what you would need. just the whole file of the quirky db. the problem seems to be more complex. This morning the wacky records has now increase by one.

I need to do more research, but I found no crash logs on the server.

[Slugbooks]

OK, I copied the file off the server and saved it on to a client. The I ran the recovery process on the file. Opened the recovered file and did the same manual search. This time it worked!

So then I copied it back to the server and swaped it out for the old one. Opened the file.

I went back to a client and tried the same find and this time it messed up again. Finding 55 incorrect records. I pulled the old file on to another client, did the search again and it worked.

So some how it is messing up from the server?

[Genx]

Scrap the recovered file NOW!!.. Don't ever use the recover function unless your file is dead, can't be opened and you want to pull the data out. It rips the file up and then rebuilds it - not so happy.

Anyway, make sure that your server is updated to the most recent version - there's clearly an issue there.

[Slugbooks]

well ok. i got rid of the recover. I called fm support and that guy told me to copy my entire database to client A with fm pro and use another client B to access the files from client A. and see if I can replicate the problem. Well, i am not sure how well I did it, but it did not get the desired response. If it did not the recover the file.

I also went and updated the server to fm server 7.4. Still has problems it even found more incorrect records.

The only time I have gotten it to work is if the file is on a client and I am on that same client and I open. It will use some other files from the server, and produce the right results. Aside from that nothing.

I am not sure where to go with this and after 5 days straight I am a little tired. I have been contimplateing building a new file from scratch and moving the information. But the file is a huge section of the overall database and the problem though a bother is not critical.

Thanks for all the help.

[Genx]

I really really think it's your data thats corrupted

[Slugbooks]

Thats what i am thinking as well. What is your suggestion? Rebuild the file from scratch and re-enter the information by hand or just import it. The problem that faces is hours of time. I wonder what part is corrupted. the Files structure or the data put in the structure.

I am open to any possible suggestions on where to go forward with this.

Thanks

[Lee Smith]

If you don't have a clean backup copy of the file, then you should create the File from Scratch.

Do not just import the data though.

Export your data as a Text File from the old file, and then import the text file into your new file.

Lee