7 Replies Latest reply on Nov 30, 2011 11:28 AM by philmodjunk

    Many to Many Portal

    DaisyBlanton

      Title

      Many to Many Portal

      Post

      I have FileMaker Pro 11 and am fairly new to Filemaker.

      I have a name file with multiple email addrs related to it. I set up a portal in the name layout and was able to add or delete email records successfuly with it. However the same email addr can be used for multiple names. I set up the name_email table to handle the many to many relationship. The portal displays the existing email info correctly. However it will not let me add an additional one to it. I figure I will need a script to check the email table to see if the particular email already exists and, if not, to add one. Then put the name ID and the email ID from the looked up record or the new one in the name_email table. The portal shows the new row but will not let me enter into it.

      Thanks for any help.

      Daisy

        • 1. Re: Many to Many Portal
          philmodjunk

          To which table does the portal refer, Email or name_email?

          Did you enable any "allow creation of records via this relationship" options for the portal table in Manage | Database | Relationships?

          You may find comparing your file to this demo file helpful: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          If you download the demo file and take a look at it, feel free to post any questions about it you may have here.

          • 2. Re: Many to Many Portal
            DaisyBlanton

            PhilModJunk, Thanks for all your help. I am in the Name Layout and the portal refers to the name_email table with the related email addr. I do have "allow creation of records via this relationship" enabled. I also have your example downloaded from an earlier question. It has been a lot of help. After I posted this question, I was able to tweek everything and get it to create both records when I entered a new email addr. However when I selected one from the existing emails, it replaces the email with the rcdID number. I am going to try a few more things. It is so close.

            A simple question: is there any way to get to the script generated by FileMaker and just edit it? That would be so helpful. Thanks, Daisy

            • 3. Re: Many to Many Portal
              philmodjunk

              A simple question:

              What script is that?

              Filemaker does not generate sripts. You have to use the script editor and create them so they are quite easy to edit. That's how you create it in the first place.

              However when I selected one from the existing emails, it replaces the email with the rcdID number.

              Which field is this?

              You should select the ID number in the Name_Email's matching Rec_ID field and either a different related field from the email table is placed in the portal row to show the email or you use a pop up menu to select your emails, with the email address specified as the second value in the value list and that field 1 values be hidden.

              You may want to compare these options on the layout that compares the two options. (This was added after the initial upload of this file so if You don't find a layout in the demo comparing drop down lists to pop up menus, use the link previously posted here to download the updated copy.)

              • 4. Re: Many to Many Portal
                DaisyBlanton

                I have uploaded my test database to My 4shared however I don't know how to share it with you. Remember I am a beginner Filemaker (and My 4shared) user. The example cbc Database Work Copy.fp7 is the closer one. If I select an existing email for a name it works fine, creating the name-email record. But I can't add a new email. My script is attempting to let the user select the placemaker 'add new email' and then add a new email record with 'Put new email here' for the email address and create the name_email record with the newly created email ID. You could then change the email addr (Put new email here) for the actual address. However it just adds a name_email record for the 'add new email' email_ID and doesn't seem to do anything else. It does create the new email rcd with spaces for the email but that is all.

                I used the trigger OnObject Save but that may not be correct.  What about OnObjectModify? Also I tried setting the value list to allow a new email to be added but it doesn't do anything with it.

                Can you get to the database or how can I allow you to. Any suggestions to add a new email address without going to the email record. Right now it won't let me key into the email field unless I select an existing email.

                Thanks so much. Daisy


                • 5. Re: Many to Many Portal
                  philmodjunk

                  In 4shared. Right click the uploaded file and select Properties. Then click the General tab and copy the text from the Download link text to your clipboard. Paste it into a post here in the forum.

                  If you paste the text, select it, then click the chain tool in the upper left corner of the Post A answer box and paste it again into the first field of the dialog that pops up, you'll get a "live" link that others can click to download a copy of your file.

                  • 6. Re: Many to Many Portal
                    DaisyBlanton

                    Here is the link to my sample database:  http://www.4shared.com/folder/m4KVfMVF/_online.html

                    Thanks, Daisy

                    • 7. Re: Many to Many Portal
                      philmodjunk

                      When I look at the script, I see numerous issues with the script. Set Next Serial value isn't something you want to change via script in most cases and there are better options.

                      Did you download and examine the demo file?

                      Several layouts in this demo file have a button with a plus sign on it just above the top right corner of the portal. Clicking it adds a new record in the table on the other side of the many to many relationship and then it creates a record in the join table with ID numbers from the current record on your layout and the newly created record in the second table inorder to create a link between the current record and the newly created one.

                      I think it would be better for you to examine that script first. The demo file also demonstrates three different methods you can use to keep from selecting the same record more than once. Any of these methods can be used to keep from selecting the same email for the same person more than once and none require any scripting.

                      The demo file illustrates how to set up:

                      A validation rule on the join table so that an error message pops up when a duplicate selection is made.

                      A diminishing value list that filters out previous selections so that they cannot be selected a second time.

                      A selection portal that simulates a checkbox group for making selections.