7 Replies Latest reply on May 25, 2016 5:00 AM by ffigueiredo

    Relate three tables (many-to-many)


      Good afternoon,

      I'm developing a database in FM14 with three tables: author, creation and publications.



      The list of questions that arise are:

      - An author may have one or more creations;

      - A creation may have one or more authors;

      - A creation may have one or more publications;

      - A publication can have one or more creations of the same author;

      - A publication can have one or more creations of different authors;



      After creating the 3 tables, I created a Join Table and have used portals in each of the tables layouts. After that, I checked multiple errors, as the fact of creating "ghost" records for every time each of a table record is used and also  records have been deleted each time an instance of the portal is erased, in particular instances "ghost".

      Can anyone give me a clue how to solve this?

      Thanks a lot for the help!





      Boa tarde,

      Estou a desenvolver uma base de dados no FM14 em que junto três tabelas: autores, criações e publicações.


      As questões de relação que se colocam são as seguintes:

      - um autor pode ter uma ou várias peças;

      - uma peça pode ter um ou vários autores;

      - uma peça pode ter uma ou várias edições;

      - uma edição pode ter uma várias peças do mesmo autor;

      - uma edição pode ter uma ou várias peças de autores diferentes;


      Depois de criar as tabelas e ter usado uma Join Table e ter usado portais em cada um dos layouts das tabelas de registos, verifiquei múltiplos erros, como o facto de criar registos "fantasma" por cada vez que cada registo de uma tabela é usado e também serem apagados os registos cada vez que uma ocorrência no portal é apagada, nomeadamente as ocorrências "fantasma".

      Alguém pode dar uma pista de como resolver?

      Muito obrigado pela ajuda!



        • 1. Re: Relate three tables (many-to-many)



          Thank you for your post!


          I am going to move this thread from the Community Feedback which is specifically for input on the Community itself to the Discussions Space where you should receive more views and potentially more advice!



          FileMaker, Inc.

          • 3. Re: Relate three tables (many-to-many)

            Well, one join table isn't enough because you have two many-to-many relations:


            Author --< AuthorOfCreation >-- Creation --< CreationInPublication >-- Publication

            1 of 1 people found this helpful
            • 4. Re: Relate three tables (many-to-many)

              Hi. Thanks for your answer.

              I'm trying to put this working, but i'm affraid I can´t get Publications relating to Authors.

              Currently the graph is as you can see here, where "peças" means creation and "edições" means publication.

              Screen Shot 2016-05-11 at 14.44.15.png

              Do you think that is ok?


              Thank you very much. I'm still testing all the relations, creations and deletes and so on, but it seems to go fine, except that I can't select authors in the author portal in publication table; instead I have to relate first creation and author in order to get connected author-publication via creation portal in publication table. It's a bit confusing, but as far it works and doesn't get any bugs, it's ok.


              • 5. Re: Relate three tables (many-to-many)



                I think the way you suggested me how to do it is ok. Meanwhile, I wanted to complexify a bit these relations and I'm not being well succeeded.


                That is: Taking the scheme you wrote down for me

                Author --< AuthorOfCreation >-- Creation --< CreationInPublication >-- Publication


                I wanted to add this issue: diverse material related to Author or to Creation or related to both at the same time. I tryed to ad these objects to Publication table because they have similar field information. The problem is that publication Author  is being generated automatically through AuthorOfCreation  table and I can't specify an author to these new objects differently from the author of the creation.


                Do you think I should get a new table and set a new relation?


                Let me tell you in advance that I don't need to have bi-direccional info.


                I just should be able to pick up information in Author and Creation tables from these new objects.


                Please help me cause i'm getting desperated!!!


                • 6. Re: Relate three tables (many-to-many)

                  Have you considered treating creations (like articles) and publications (like magazines, books) as similar types of the essentially same thing, where the people involved aren't necessarily only authors, but could also be photographers, editors …?


                  Then you could implement


                  Person --< PersonInWork (role) >-- Work (type)




                  Work --< WorkConnection >-- WorkForConnection


                  so an article is contained in a magazine, and the magazine contains that article;, or an essay has some connection to another essay (influenced by, did influence)


                  which could be very flexible, but would obviously be more complicated to set up.

                  • 7. Re: Relate three tables (many-to-many)



                    Thanks a loto for your answer.

                    Let me see if I got.... Do you suggest that I revert all I done before?


                    "WorkForConnection" would be what?


                    I'm affraid it's too complicated to me...


                    Can't I just add another table from wich I could pull information for my previous Author and Creation tables?

                    Somehow like this......

                    Screen Shot 2016-05-23 at 17.07.20.png