FM Server Shows Corruption

[AudioFreak]

Ok this is something I learned today but have no clue why. I have a file "Inventory" which somehow became corrupt(Still figuring that out).

There is a simple Product Status Field which is a Unstored IF statement of

If(GetAsNumber(Qty In Stock Calc) < GetAsNumber(Lower Limit);"Order";"Good")

Works Fine until just recently when you search for "Order" some of the records will show in the find even though the "Status" is "Good". Ok fine it's corrupted I get that.

What I did notice today is that when the file is not being hosted by Filemaker server and is opened on a client computer the find works as it should.

Why is this? Does Filemaker Server see corruption that a normal clients Filemaker can't see? I have never seen this ever and hoping one of the Gurus here can shed some light on this, as it baffles the hell out of me.

Thanks In advance

Michael

[Ender]

What specific version of Server is it & what's the system it's running on?

What are the field definitions for Qty in Stock and Lower Limit?

[Steven H. Blackwell]

Does Filemaker Server see corruption that a normal clients Filemaker can't see?

Yes.

Save a copy of this file as compressed--under the file menu not the maintenance menu--and use that copy on the server.

Steven

[AudioFreak]

What specific version of Server is it & what's the system it's running on?

What are the field definitions for Qty in Stock and Lower Limit?

I'm at home now Ender but the qty in stock is basically a calc Start qty - Parts Pulled + Parts Received. Lower limit is just a Number field which is the lowest we want to let the inventory get.

Server version 8.0v4 I believe.....Not sure off the top of my head.

The system has worked flawlessly for 4+ years until recently. Must say this threw me for a loop, I pulled a copy off the server looked at everything, Deleted the Product Status field completely from the file. Added a new one, defined it. All seemed fine after that. So I rehosted the file on the server. Checked it and it was a no go. Pulled back off the server and opened it from my desktop and it was fine again........lol.

That's when I was like Ummmmmmm I'm going to ask about this, it makes no sense.

Edit: Tried to double quote it went wacky....lol What a day :P

Edited June 28, 2007 by Guest

[AudioFreak]

Yes.

Save a copy of this file as compressed--under the file menu not the maintenance menu--and use that copy on the server.

Steven

I'll have to give that a try tomorrow, well I could VPN in and do it now but nah it's time to relax to a Ice cold beer

Any chance of getting a better explanation of why the server sees the corruption but not a client version. Things like this intigue me.

Thanks,

Michael

[Genx]

I had this issue a while ago and resolved it with a loop omit instead (small number of records) ..

What exactly does saving as compacted file do Steven?

[AudioFreak]

I had this issue a while ago and resolved it with a loop omit instead (small number of records) ..

What exactly does saving as compacted file do Steven?

I'm actually going to try that tomorrow(not as the fix) but to see if it will see the records the do have "Good" in them. Then if it does omit them, that will bring the next question. Why does it see that they have "good" in the field while in the script but not when performing a find?

Michael

[Genx]

Are you running / have you recently run any versions of FileMaker 8.02 ?

[Ender]

Michael,

I suppose it could be corruption, and your statement that this is a new phenomenon in an unchanged structure supports that, but the symptom doesn't sound too unusual for certain complex searches involving unstored calcs. I've had similar problems with such searches, even running the latest version of everything. I think some complex searches (involving unstored calc) just can't be handled correctly.

So like Genx, I've found myself resorting to partial finds on the stored criteria followed by looping Omits to filter through the unstored calc criteria.

[Ender]

I suppose one method to test whether it's corruption is to test your data in a clone of a backup from when it was known to behave correctly.

[AudioFreak]

GenX,

Im running Advanced 8.5v1. Clents I'm not sure but they should have the latest 8.5v*. Looking at the current updates available I should get the current one Or maybe not...lol

Ender,

That's odd I remember in FM 5.5 and 6 manually doing the find and it performing fine. You could be right though and I may end up doing the same. I'll make a test file with just the key fields I need to test if it is corruption tommorow and try Stevens suggestion.

The number of records it searches is small 3200-3300 the found set even with the mixed records is around 550 FWI.

Michael

[AudioFreak]

That was the first thing I did. It's just odd it only acts this way IF FM Server is hosting the file. Opened Locally it's fine. Even tried intstalling Server on another machine and hosting it there with the same results.

[Genx]

Just for the sake of it, you're running server 8.04 yeh?

[AudioFreak]

I'll have to check. I'll fill you in on this though. I'm going to reinstall Fm server 5.5 and check it too if I have too. I know this worked fine at one point. Hell I may have converted a FM6 file to 8 that was corrupted already but it didn't show til later.

Who knows at this point but I will find out. :P

[Steven H. Blackwell]

What exactly does saving as compacted file do Steven?

Rebuilds some indices and otherwise removes some corruption or other bad behavior of files. It also rebuilds the tree structure. See especially Tech Info 5594.

Steven

[Genx]

Good Info,

Thanks Steven.

[AudioFreak]

Just for the sake of it, you're running server 8.04 yeh?

Yes it is Server Version 8.0v4

Tried the compacted version. No luck there. Went back to the originally converted FM6 File and it works fine hosted with Server 5.5. Converted that file to 8 works fine opened by client, as soon as Server 8 Hosts it, it displays this odd behavior.

All I can assume is the original 6.0 file has some corruption in it. Just doesn't show until the file is converted to 8 and hosted by Server 8.

Gonna have to make a test file with the relevant fields and see if it behaves the same. If it doesn't I'm going to have to rewrite the file

Thanks for all the ideas guys, appreciate the help.

Michael

[Ender]

Went back to the originally converted FM6 File and it works fine hosted with Server 5.5. Converted that file to 8 works fine opened by client, as soon as Server 8 Hosts it, it displays this odd behavior.

That's a pretty good indicator to me that it's not corruption. FM8 Server does searches differently than FM5.5. With stored fields, it can usually do the Find on the server side and return the results to the client (that's why it's so much faster). But unstored calcs involving globals or aggregate calcs have to be evaluated on the client side. So it seems to me that there's something about the search that it just can't put together.

Sometimes reworking the search will work, omitting instead of Finding, or using a relational hop. For example, perhaps there's a way using a self-join range relationship between Qty in Stock Calc and the Lower Limit (along with the record ID), jumping to the related record set (using the 'match all records' option).

Otherwise using a loop or a stored copy of the Qty field may be the only option.

[AudioFreak]

Ok did some further testing before I rewrite this thing. If I perform the find I get a list and some do say "Good". So I duplicate the record and delete the original. That record will never show again in the found set but the find ALWAYS comes up with a Found Set of 348. Just Wierd.

[AudioFreak]

For example, perhaps there's a way using a self-join range relationship between Qty in Stock Calc and the Lower Limit (along with the record ID), jumping to the related record set (using the 'match all records' option).

Otherwise using a loop or a stored copy of the Qty field may be the only option.

Not a bad idea.

[AudioFreak]

Ok set up a test file with the relevent fields to test. It reacts the same way, so in the end It's not corruption. Odd that performing a find happens to fast on the server for the client side to be able to get the correct results.

Ended up going with performing the Original find as it does find all the records with "Order" in them, just adds some that say "Good". Then loop through that set setting a seperate Text field with the Product status field. And performing the "Order" search on that field which does return the correct results.

Adds about 20 seconds to the whole process which is standable.

Michael

[mr_vodka]

Would the results be any different if you used a boolean instead?

Calc: Qty In Stock Calc < Lower Limit

You can display the boolean as Order and Good since its less than 7 characters.

[AudioFreak]

Thanks for the alternative. For a sec I thought that might work. But it does do the exact same thing.

Michael

[Ender]

Ended up going with performing the Original find as it does find all the records with "Order" in them, just adds some that say "Good". Then loop through that set setting a seperate Text field with the Product status field. And performing the "Order" search on that field which does return the correct results.

You might try omitting the values you don't need right off the bat (instead of setting a field and then finding those you want). The loop would look something like:

Loop

If [ Qty In Stock Calc < Lower Limit ]

# Order

Go to Record/Request/Page [ Exit after last ; Next ]

Else

# Good

Omit Record

Exit Loop If [ get(recordnumber) = get(foundcount) ]

End If

End Loop

[AudioFreak]

Thanks for that ender. I'm going to have to sort out whats happening. I end up with a much smaller found set when using the omits in the script. I added some pauses so I could see what was happening and all seemed fine. Then I put a show ommited at the end of thte script and there are some that say order that are in the omiited set of records.

I'll let you know what I find out. Time to eat :P