11 Replies Latest reply on Apr 24, 2016 9:23 AM by jonasmn

    A simple relationship is not working




      I have a one-to-many relationship, standard stuff. Se the relationship below (the blue outlined tables):


      Simple relationship not working.png

      All the other four relationships that are set up EXACTLY in the same way, ARE WORKING! But this fifth one is not working. In the layout for the table "treatment_Z_PREPARATIONSTOTREATMENT" the values from the table "treatment_z_preparationstotreatment_PREPARATION" are not show, even though the records are holding information and the IDf_Preparation has a valid ID that is pointing to a valid ID_Preparation.


      Is there some "hidden" setting somewhere that i do not know about that is missing things up? I am totally stuck, since this is just a repetition of the other relationships.




      Jonas Möller Nielsen


        • 1. Re: A simple relationship is not working

          If the keys are stored correctly it appears it should work. Could it be conflicting field type?

          • 2. Re: A simple relationship is not working

            I'm a complete new to this, but could it be that for the relationship IDf_Places the names must be exactly the same as you did for the others?

            FMP Relationship.jpg

            • 3. Re: A simple relationship is not working

              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?

              • 4. Re: A simple relationship is not working

                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?

                • 5. Re: A simple relationship is not working

                  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.

                  • 6. Re: A simple relationship is not working

                    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.

                    • 7. Re: A simple relationship is not working

                      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.

                      • 8. Re: A simple relationship is not working

                        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?

                        • 9. Re: A simple relationship is not working

                          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 ?

                          • 10. Re: A simple relationship is not working

                            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.

                            • 11. Re: A simple relationship is not working

                              Hi again,


                              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