1 of 1 people found this helpful
Are the two participantID fields both defined as "text" data type in their respective tables? If one is defined as a "number" field, it'll still allow you to enter a value with hyphens (or other non-numeric characters) and even store those characters, but will treat the value as a number for the purposes of calculations, joins, etc. Thus "49-01-016" is treated as "4901016" in one table and doesn't match the text value "49-01-016" in the other table. That's a common mistake and often the source of problems in defining relationships. In ordet to use keys with non-numeric values, make sure they are set to "text" field types in both tables.
Hope this helps,
Thank you for your reponse. I checked both participantID in their corresponsing tables and both are defined as "text". Among several hundreds of records, only this one is not matching correctly. I used SAS to merge the dataset and SAS was able to match them up. Either something in defining relationship or something with respect to this record. If it is wrt this record, the linking variable participantID seems to be the only filed matters in the matching. What else can go wrong?
1 of 1 people found this helpful
There are a couple of possible issues that could be mis-matching these records.
- Non-matching data: if any of the characters such as the hyphens are actually m-dash or n-dash entries make with the alt/option key, they may index differently, or if there is any trailing or leading tab or space characters. Make the Zero is not a letter OH.
- The less obvious issues are with indexing, but since the other records are matching up, the indexing settings themselves are probabaly correct (fully indexed fields on both sides). However, if there is an indexing problem with the file, it may be worth turning indexing off, saving the field defs, and then turning it back on to force reindexing. You can also use the Recover file options to rebuild the indexes, which is a safe option for continued file use, it just rebuilds the field indexes.
If the data itself is correct, rebuilding the field indexes should correct the problem.
The relation relies on the value index of the child record.
It is possible this index is not up-to-date (not to say corrupted).
Please read <http://fmdiff.com/fm/recordindex.html> for details.
As a first measure I would make a clone of the child table and import the data from the existing one.
This regenerates the index.
It may be worth creating a new test database and creating the relationship using the data that doesn't match and see if it produces a match, if it does then it's not the data and as others have said probably the indexing, if it doesn't match it proves that something in the text Ids is wrong.
When I run into this, I first change the indexing to none, then the first find action on that field recreates the index, if that doensn't work I create a new field and fill it with a replace action from the old field then delete the old field.
Last resort is do a recover and in the advanced options check if the recreate index is set.
When I find this sort of thing happening, I usually track it down to a space after the ID. Remove the trailing space(s) and all shows OK.
Thank you all for your replies. I really appreciate your input.
Sorry that I had to work on some other things before I can come back to this one. I tried to make a new copy of the database and tried to restore it and tried to rebuild the index. But those did not solve the problem. As a last resort, the one record which did not link had to be deleted and re-entered. Fortunately, this solved the linking problem.
Thanks again for your help!