If the keys are stored correctly it appears it should work. Could it be conflicting field type?
Filemaker can join fields regardless of the name.
Concur with BigTom.
If the keys hold values that produce matching records AND preparation has valid records it should work.
Maybe post the file and provide specific instruction to reproduce what you are seeing?
Since the other relationships using ID_Treatment = IDf_Treatment are all working, I would say the issues lies with the IDf_Preparation = ID_Preparation relationship. Study the following possible causes of the breakdown:
1. as already suggested, field type—trying to match text to number, etc
2. are you sure the relationship is correctly set—incorrect join operator set, e.g. > instead of =?
3. is either of the fields not indexing correctly?
4. the ID values themselves—are you sure they are exactly the same, e.g. could there be a hidden character on one side (space or return) that is negating the match?
ID_Preparation = numeric, indexed, automatic values, not empty, unique value, only numeric, allow violation
IDf_Preparation = numeric, indexed, not empty, only numeric, allow violation
So as far as I can see, the field types should be OK.
The relationship you mention works fine, It is the relationship two steps below that is not working. But thanks for noticing the spelling. I have changed that now.
1. they both (ID_Preparation and IDf_Preparation) have the the same field type, numeric.
2. The join operator is set to =.
3. Both fields are set to INDEXED.
4. I do not think that the ID values have spaces, since they are calculated automatically by FM. I have tried to create new records just to make sure that the values get correct but it does not help.
The tables are located in the file "Journal Data" and they have no relationships in that file. The relationship is created in the file "User Interface". Could this cause the problem? But then the other relationships should not work either.
To really make it simple, I have now created a dummy table (z_Dummy) with the two fields ID_Dummy and IDf_Preparation. The are then related with (=), nothing else. The z_Dummy is also created within the same file as the user interface, so no linking to external files. In the layout, z_Dummy, there are the the two fields ID_Dummy and IDf_Preparation, and then also a field that should show a field from the table Preparation. This is also NOT WORKING. I am beginning to think there is something wrong with the table Preparation, but as far as I know, there are no specific settings for tables, or are there?
Can you post a sample version of this we can look at?
Have you verified that the IDs match between the related records?
How are you setting the foreign key ( idf_Preparation) in treatment_Z_PREPERATIONSTOTREATMENT ?
Agreed with the wise Bruce.
There is something basic you seem to not quite grasp yet about layouts, layout objects, and relationships, and it will be a LOT more productive to troubleshoot this with actual files, whether clones of your real files or simplified test versions.
So embarrassing! I found the problem. There was a link to an external datafile that was wrong. But thanks to you asking me to send the files, I saw it. There was one file that I had forgotten to link into the UI file thus the relationships did not work properly.
Jonas Möller Nielsen