I'd check the actual values in the fields and value list. FMP relationships have become more strict with each revision. For example, a space at the end of the data will now stop it from working.
A couple of suggestions.
Clean up the data in the fields used in the relationship and possibly reenter the values for the value list or have it build from the field itself. Make sure the values really do exactly match.
Put both fields together on the layout. Check the indexes in both fields/files using command-i.
Check for strange entries at the beginning and end of the index that might indicate a possibly corrupt index or bad data in the field(s).
Turn off the field index and set it to reindex as needed. Possibly use the Advanced recovery to turn off all indexes. Then do a find in the field and let FMP determine which indexes to build. Recheck the indexes.
Use Go To Related Records (GTRR) and see if there are any all "?" records. This is a pretty clear sign of a corrupted index.
Thanks JImmy. Will give these a try.
Look thru the Recover Log, for any items marked as "changed"
It can also help to export and re-import all the data into a clone, for both files
> Recovery told me that modifications were made to the files during recovery.
look at the field type of the field being used for the search. It might have been changed from a text field into a number field.
In my on-going hunt to figure out what's going on with this file....
There are 3 fields that are the main "search fields" - first and last name and a field used for classification of the record (a year indicator). When searching for the current year, records are found. When seraching for previous years - search fails.
Tried rebuilding the indexes, replacing the fields on the layout, resetting the relationship and a few others - no luck.
As a workaround the client was performing searches based on the name, which would only return the current record, no historical records. I taught them to then do a Show All Records and then sort by name. This would group everything together and let them page through the records.
The database only had a form view for this table. So I quickly threw together a List view based on the same table occurence. Tried a search and hello - it worked - everything that should have appeared did appear. Same search on the Form view layout fails.
Now I'm thinking there is something corrupted on the layout, not necessarily with the structure of the file or the data.
It'll take a while to rebuild the layout as its particulary nasty but this is something to explore.
As the previous post suggested, be sure the field "types" are correct. Are they all text fields ? what is your "year indicator" ? a number, a date, or text ?
This is a VERY common mistake, and the cause of much hair pulling
> There are 3 fields that are the main "search fields" - first and last name and a field used for classification of the record (a year indicator).
Checked that. All file types are correct.
so then, what is your year "indicator" ? Please give an example
and, which year seems to work, and which years do not ?
> All file types are correct
The indicator is a 3-character prefix with the 2-digit year "ABC12" for example. This field is defined as text. It is always entered with a fixed value list.
The "current" year is for ABC13 for next year's sessions. Out of 2500+ records, searching for ABC13 works fine. Searching for anything older - which goes back to ABC99 fails.
On the new list layout I created today I can search for any year and it works fine. Searching with the same criteria on the form layout fails.
That's really strange DZ. Can you save a clone and import some of the data?
Tried the import. No love.
This actually could make sense
If the new list layout is based on table B, then finds will work fine
However, If the form layout starts on table A, and the find is done in a Related field from B, then the finds will fail, if the records you seek happen to not be related to the record you are on
Make sure you can see a Related "ABC99" record from the form, then try a find
> On the new list layout I created today I can search for any year and it works fine. Searching with the same criteria on the form layout fails.
Well, I know DZ is not new to FM and is a seasoned developer. While it's possible that the new List layout is based on table B....I doubt it. DZ would have double checked that. It sure is sounding a lot like something on the layout is corrupted. I've had layout level corruption before that didn't show up in the recovery log.