6 Replies Latest reply on Aug 9, 2013 3:41 PM by philmodjunk

    Using a portal with a join table



      Using a portal with a join table


           I have a table of Books and a table of Authors, joined by jBooks_Authors, with their respective ID fields. I believe I have the relationships set up correctly for this.

           Now, I need a portal on the Book layout that 1) shows the Authors attached to that book, 2) allows me to attach more Authors to that book, and 3) a way to add an author if not already existing.

           Books (BookID)------------------jBooks_Authors (BookID, AuthorID)----------------------Authors (AuthorID)

           I am using a value list in my portal, but I only get the ID number. The AuthorID is the 1st field and the AuthorName is 2nd field. I have selected to show the second field.

           Can you help me with what I might be doing wrong? I think I've tried about every possibility. Have I missed one or is it not possible to do it that way?


        • 1. Re: Using a portal with a join table

               A portal to jBoooks_Authors is the correct portal to use to link additional authors to the current Book record. And a value list that selects the AuthorID is also correct. If you set up the value list with author names as the second field and specify that only the second field be visible, you can use a pop up menu with this value list and you will not see the ID numbers. You will see the author names. You can also keep it as a drop down list, but include the Authors::AuthorName field in the portal row and it will show the name of the selected author. You can even deny browse mode access to the name field and place it on top of the drop down list if you want.

               To add a new author introduces two new complications to the process.

          1.           Ideally, such an action needs to create both a new author record and a new join table record with values in the id fields that link the new author record to the current book record. This can be done with a button that, when clicked, creates both new records and enters the correct values to link everything correctly.
          3.           Just creating the new author record is not enough, you also need to enter data into fields of that new author record. If all you need is the author name in this record, you can edit the author name directly in the portal row after creating and linking in the new author record. If you need to record additional info about the author, you'll likely need to use a layout based on the author's table to enter that additional info.

               The following demo file contains a button for adding the new record on the far side of the join table and linking it in via a new join table record. Check out the buttons with plus signs on them and the script that they perform: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you are using FileMaker 12, open this file from the File menu to get a copy converted to .fmp12 format.

          • 2. Re: Using a portal with a join table

                 Thanks for two great answers in one day, Phil! I'll study this out, as well.

            • 3. Re: Using a portal with a join table

                   Ok, Phil. I've got the popup menu working. I haven't tackled the adding records to Authors, yet. I have two questions...

                   1) I am not able to delete in the portal. I have two authors showing up correctly, but there are two empty popup menus in the portal that I cannot get rid of. I have Delete... selected in the Portal dialog box, but it doesn't seem to enable it.

                   2) I am not understanding what you meant about the dropdown box, which is what I really want, more than the popup. I am guessing there is a difference in the way the popup menu and the dropdown list handle value lists and portals?


              • 4. Re: Using a portal with a join table

                     1) You have "allow creation of records via this relationship" enabled and you need this feature if you want to add new join table records by entering data directly into the portal. This causes an extra blank "add row" to appear in your portal for the purpose of adding these new records and I beleive that this is what you are describing. It's expected for these settings.

                     2) There is indeed a difference. When you exit the drop down, you see the actual data entered from the value list, the author ID. Thus, you need to add the author name field from the related author table in order to see the name of the author that you have just selected once you have exited the author ID field. One layout trick is to use field behavior settings to deny access to the field when in browse mode, give the field an opaque fill color and place it on top of the drop down field. When you click on the name field, the drop down field pops to the front and deploys the list. When you select a an author, the drop down list disappears back behind the name field and now you see the author name. What you lose with this trick is the abilty to edit the author name field righ there in the portal row has you have blocked access to the field.

                • 5. Re: Using a portal with a join table

                       My bad - as usual. I understand the extra row due to the Add option, but I was pressing delete while the popup menu was selected, though not showing the names. I took that as the same as the whole row being selected (which wasn't possible because my portal was tight all around the popup menu.) Once I enlarged the portal and could select the actual row, it worked - dubm on my part.

                       One more issue and I'll give you a break! Since I haven't got to the function of adding Authors through the portal, I am simply going to the layout and adding them. I have added an Author through the layout, but it simply will not show up in my popup menu in the portal. 1800 other authors are showing, but I cannot get that one to show. What could I be doing wrong?

                       I really appreciate your help today.


                  • 6. Re: Using a portal with a join table

                         I add a button to my portals for deleting portal rows. I've devised a script for this that's a bit more user friendly than just the "delete portal row" script step and yet it can be used with any portal in any file so long as the correct data is passed to it in a script parameter and delete portal rows is enabled for the portal. You can check it out and import it from the Known Bugs List database if you want.

                         As to your new author record, It won't appear in your drop down until you commit records such as by clicking a blank area of the layout. Also, how is the authorID field populated with an ID? Is it an auto-entered serial number? IF the field is empty, it won't appear. Also, if you have two authors with the same name and you are sorting your value list on the name field, the duplicate author will not appear in the value list. (If it did, you'd have no way to tell which author was which anyway.) It's a good idea to set a unique values validation field option on the name field when using this kind of value list.


                              1800 other authors are showing,

                         Hmmm, that's a lot of authors to put into this type of value list. I would find it very tedious to use. I'd either set up some kind of conditional value list to trim the list down to shorter groups of names or I'd use a script supported method to select the author such as the name lookup used in this demo file:

                         FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                         Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7