9 Replies Latest reply on May 29, 2016 7:29 AM by hogfoo

    How do I add a record to a join table through a portal?

    hogfoo

      Hello!  First post here.

       

      I'm new to FMP.  Apologies in advance for what I suspect is a very basic question.

       

      I'm working to create a simple contact management app.  One thing I want to track is one or more types for each contact.  This would seem so simple, but I'm stymied!  I've set things up as follows:

       

      Contacts Table

      ContactID (pk)

      Contact Name

       

      Type Table

      TypeID (pk)

      Type Name (customer, vendor, contractor, etc.)

       

      ContactType Table (join table for Contacts and Type)

      ContactTypeID (pk)

      ContactID (fk)

      TypeID (fk)

       

      Here's a screenshot of the relationships.

       

      Screen Shot 2016-05-28 at 2.09.21 PM.png

       

      I want to have a way, on my Contacts layout, to associate multiple types with a given contact.  I've been trying to have a portal with a dropdown showing the type name that, when selected, would update the join table with a new record linking the contact and the type.  Can anyone offer some insight here?

       

      Many thanks!

        • 1. Re: How do I add a record to a join table through a portal?
          beverly

          Welcome, hogfoo! The Portal, as you may have found, will show all related records. You can "filter" (change the results shown) by calculation. That calculation can call a global field which might have a value list, such as your "type". The calculations updates in the filter and the portal changes.

           

          see if these help:

          and you can find more (including videos) by searching for 'filtered portals filemaker' in your favorite search engine, or just 'filtered portals' on this forum

           

          beverly

          • 2. Re: How do I add a record to a join table through a portal?
            erolst

            hogfoo wrote:

            I've been trying to have a portal with a dropdown showing the type name that, when selected, would update the join table with a new record linking the contact and the type.

             

            To be able to create a new join table record via a portal on the Contacts layout, you must open the ”Edit Relationship” dialog of the relationship between Contact and ContactType and enable “Allow creation of records in this table …" for the ContactType side.

            • 3. Re: How do I add a record to a join table through a portal?
              hogfoo

              Thanks all.  I'm not sure I fully understand the global variable and filtering calculation.  Is there a simpler way to just populate a dropdown from a list in a related table to update the join table without having to write a specific script?  I suspect it's something to do with how I've set up my relationships and value list.  Here is some more information.

               

              In terms of the relationships, I did already set things to allow adding records to the contact type table.

              Screen Shot 2016-05-28 at 2.44.43 PM.png

               

              I also set the relationship to allow additions to contacttype from the types table.

              Screen Shot 2016-05-28 at 2.44.58 PM.png

              Here's how my type table is currently populated.

              Screen Shot 2016-05-28 at 2.46.12 PM.png

               

              I set up a portal on my contacts layout that is linked to the contacts type table.  I added primary key from ContactTypes and a dropdown from Types with TypeNames (my goal in this exercise).  I had hoped that changing the type name in the dropdown would change the pkTypeID in the ContactsType table for this record.  Unfortunately, if I don't have any entries already in the ContactTypes table then the dropdown does not populate.  If I do create an entry for the contact ID with anything as the typeID in ContactTypes, then the TypeName dropdown populates correctly, BUT does not change the pkTypeID when I change the value in the TypeName dropdown.

              Screen Shot 2016-05-28 at 2.47.08 PM.png

              I used a pop-up menu for the TypeName dropdown and used a value list called Types based on the type name from the types table.

              Screen Shot 2016-05-28 at 2.47.38 PM.png

              Here's how I set up the value list.

              Screen Shot 2016-05-28 at 2.48.11 PM.png

              Does this help more in diagnosing my problem?  I'm not trying to duck the good suggestions and links people provided, but looking at them does not seem to immediately help to my befuddled brain and my other searching on the subject also is yielding few results.  Sorry so long, but many thanks for whatever help you can offer!

              • 4. Re: How do I add a record to a join table through a portal?
                BruceRobertson

                Change the value list setup.

                TypeID ValueList.png

                1 of 1 people found this helpful
                • 5. Re: How do I add a record to a join table through a portal?
                  hogfoo

                  Thanks!  I tried changing it as you suggest, but my dropdown on the portal still does not display anything.  Here's the setup for the value list now:

                   

                  Screen Shot 2016-05-28 at 9.49.13 PM.png

                   

                  Basically, unless I already have an entry in the join table for the contact, the dropdown in the portal is non-functional.  If create an entry in the join table manually, then the portal displays that record correctly and the dropdown works, but if I use the dropdown to change a value, nothing is updated in the join table. 

                  • 6. Re: How do I add a record to a join table through a portal?
                    BruceRobertson

                    See attached.

                    1 of 1 people found this helpful
                    • 7. Re: How do I add a record to a join table through a portal?
                      hogfoo

                      Thank you SO much!   This was incredibly helpful.  I was so close.  I needed the dropdown in the portal to be from the join table, but then the value list based on the type table.  I was using the type table in both places.  Super helpful!

                       

                      Follow up question:

                      Any good suggestion on preventing users from entering duplicate types in the portal?  I created a field "Validate" which concatenates the two foreign keys.  Then a summary field, ValidateCount, to count, via self join, the total of each Validate field pair.

                      Screen Shot 2016-05-29 at 12.05.47 AM.png

                      Screen Shot 2016-05-29 at 12.09.47 AM.png

                       

                      Everything is working, counting correctly, etc.  I'm trying to figure out an IF statement that checks validate count before something is committed and then provides some sort of dialogue that indicates the duplicate and prevents the commit.  E.g. IF (ValidateCount; >1) display a dialogue box "Type Already Associated", click OK button, and delete attempted row.

                       

                      Again, any help would be appreciated!

                       

                      Thanks, again!

                      • 8. Re: How do I add a record to a join table through a portal?
                        BruceRobertson

                        Some further refinements...

                         

                        contactTypes.png

                        1 of 1 people found this helpful
                        • 9. Re: How do I add a record to a join table through a portal?
                          hogfoo

                          Wow - thanks!  Now I need to dig in and figure out exactly how all of this works. Very cool.  I really appreciate the help.