10 Replies Latest reply on Mar 12, 2014 5:51 AM by philmodjunk

    portal and many-to-many relationship



      portal and many-to-many relationship



           it is my first post here, so the problem I want to solve is quite easy. In order to present it I've created a very simple data-base, which ilustrates the problem. You can find it here:  http://www.sendspace.com/file/es50sk . 

           In the sample data base there are two main tables: people and cities. The data-base is designed to show cities visited by certain people. It is obviously many-to-many relation so an additional join table was created.

           I would like to enter all data in one-place so inside the person layout I've created the portal with a data from the City table. It works, but it muliplies/duplicates the city records (picture below), so actually it works as one-to-many relationship. Is there any way to avoid the problem of record duplication?

           I would like to add that I cannot create the list of all peoples and all cities before creating a portal. All data should be entered via the portal. Thanks a lot for your help in advance. I have tried to search information virtually everywhere, but I haven't found it although I feel that the solution must be so easy.


        • 1. Re: portal and many-to-many relationship

               Replace your portal to the city table with a portal to the Join table. Use this portal to link the current person record to different existing city records. You can make your cityID field a drop down list or pop up menu of CityID's and city names in order to select a city in each row of the portal. Other fields from the city table may be added to the portal row to show more info about that city if such is desirable.

          • 2. Re: portal and many-to-many relationship

                 PhilModJunk, thenk You for the answer. It really helped me, at least in terms matching people with existing cities. However I still have doubts about creating new city records via this portal. I can do it (when I choose Allow entry of other values from the data Tab in inspector (previously I had chosen Pop-up menu control style), but as CityID in a join table is not a primary key and Prohibit modification of value during data entry or serial number options cannot be chosen it can cause a lot of mess including creation of two city records with the same cityID. Is there a way to avoid it at to force the user to give "the correct" cityID?




            • 3. Re: portal and many-to-many relationship

                   You would not use a portal to the Join table to create a new city record. You need an additional method for creating a new record in that table and then also creating a matching join table record linking that new city record to the current people record.

                   I do this with a button and a script. You can find examples of this if you check out the script performed by the + buttons in this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   If you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open the file in order to get a file converted to the newer .fmp12 file format that you can open and examine.

              • 4. Re: portal and many-to-many relationship


                     Thanks a lot PhilModJunk! I have followed suit and implemented the scripts from samle in my data-base. It works right now. `however, I have to admitt that the solution wasn't as easy as i supposed.

                • 5. Re: portal and many-to-many relationship

                       Hello PhilModJunk,

                       the solution You proposed works perfect, but I have tried to impelemnt in similar case. Now I do not enter the data via a portal, but via related field(s) (being at the "child" side of one-to-many relationship). I have tried to use the script You provided me with (obviosuly I cut the part of the script which dealt with the join table), but it doesnt work. Do You have any idea how to deal with that problem?


                  • 6. Re: portal and many-to-many relationship

                         Not without a much more detailed description of what you have set up. (And the main point of the script is to create the linking Join table record. If you don't have a join table, there may not be any need for the script.)

                    • 7. Re: portal and many-to-many relationship

                           Hello PhilModJunk, 

                           yes, i wasn't very precise. I've created a small data-base to present the problem (http://www.sendspace.com/file/3wu4hd). In the sample data-base there are two tables books and authors. Let's assume that one book may have only one author (we have now - one to many relationship). I want to introduce all the data via book layout (it is reasonable - i want to introduce all data about a book, including author). I can easily create a related field or even a portal (althogh it makes less sense to me, as the book table is at the "child" side of relationship), but it creates a new author record for each book. I have tried to create some drop-down list to choose author from existing records but it didn't help. I have also tried to create a script to create a new record in the author's table, but now i understand it is not a solution.  

                           Best regards and thanks a lot for Your help,


                      • 8. Re: portal and many-to-many relationship

                             Note: I have not at this time downloaded your file.

                             Adding a new Author and selecting an existing author often require two sets of controls.

                             Selecting an existing author can most easily be set up using the _fkAuthorID field defined in Books with a drop down list or pop up menu format that lists all existing authors and their ID numbers. (a common mistake is to try using the __pkAuthorID field defined in Authors.)

                             To add a new author record and link it to your current book record could be done with a button and a script:

                             Go to Layout ["Authors" (Authors) ]
                             New Record/Request
                             Set Variable [$AuthorID ; Authors::__pkAuthorID ]
                             Go to Layout [original layout]
                             Set Field [ Books::_fkauthourID ]

                             A sneaky method that requires no script is to enable "allow creation of records via this relationship" for authors in the author to Books relationship. If you place a field, such as the author's name field on your Books layout. It will appear blank if you have not linked it to an Author record. If you enter an author's name into this field, FileMaker will automatically create the new Author record and copy that ID from that new record into the _fkAuthorID field in your current Books record.

                             It IS possible to use a single text field with an auto-complete enabled value list of author names with scripting that determines that the entered author name does not exist in the Authors table and it then offers to create that needed record and link it to the current book record for you. But it's a more sophisticated method that requires and additional relationship and a fair knowledge of scripting. I suggest getting the simpler method working first and then, if you want to try this approach, let me know and I'll share a demo file and some added explanation of how that method works.

                        • 9. Re: portal and many-to-many relationship

                               Hello PhilModJunk, 

                               yes, i have also made this "common mistake". Now actually it seems completly logical to me, but intuitively I did something completly different. So thanks a lot for that suggestion. This method however is good for small databases. If we would have f.i. 300 authors it would be hard to figure out if the author we enter already exists in our data-base. I would be really grateful for sending me a demo-file with a second method You've metnioned. 

                               Best regards,