is it corrupt?

[Reed]

I have a table that is set up to track relationships between records that have been duplicated from other records using an auto-enter field that creates a return delimited list of primary keys for related records. The database is acting funny in that it won't find records based on the related fields during a find operation (even though they show up in a portal). The search is just a "*" search not one of the new v8 search patterns

This all worked a few months ago when I added it to the database and put it on the server. The server has never crashed in the meantime, but now the finds are returning incorrect results. The strange thing is that if I take a backup copy made from the server and run the search locally, the results are normal.

Is there some kind of corruption in the file or the index. Can I force a field to be reindexed? I tried turning off indexing and turning it back on, with no change.

I can revert to a backup but since the behavior doesn't show up locally on any old backup copies I don't know how far back to go. Also, I don't want this to happen again and it has me worried about corruption...

Any ideas?

[Reed]

update:

I made a new field and two new table occurrences, and still the behavior of the find is different when the file is hosted than when it is local. Server is 7.0v3 and the behavior is the same with FMP 7v3 and 8v2. I never got around to updating to 7v4 of fmserver, but now my copy of server 8 is here, so I'll see if that changes anything.

it no longer seems to be corruption, but instead related specifically to fmserver.

think this is a bug?

[Reed]

sorry to keep replying to myself, but I found another piece of information. The relationship in question is a multi-criteria:

primaryKey=compoundKey

AND

primaryKey<>primaryKey

The weird part is that the order of the expressions defined in the relationship affects the result of the find (only when the file is hosted) It doesn't affect the records shown in a portal or calc fields based on aggregates of the related records, but only search results.

I think I'll have to try and make up a fresh example file tomorrow, see that it works locally and doesn't work hosted, then post it here and send the file to FMI and see what they can figure.

[Reed]

after a morning of pulling my (remaining) hair out, I solved the problem. My serial number field was defined as a number field instead of a text field. This has never caused me a problem in the past, but under these strange circumstances this error results.

Simply changing the field type has fixed the problem, and now the search of related fields works normally.

[aholtzapfel]

Thank You Reed. I just had a similar problem with a Relationship (simple one to one) that just started to fail when finding by it. The Match field in both places was a number and has worked for about 6 years. After reading your posts I changed the match fields to Text and it works. Thank You, Thank You.

Now I'm Happy it works but, why did it fail to start with? I don't understand the mechinism there. Am I missing something? Shouldn't a Relationship based on a number field work? Shouldn't it even work better? If anyone has any insights into this I'd love to hear them.