10 Replies Latest reply on Aug 31, 2013 5:55 AM by CeciliaBaldwin

    Help Connecting Many to Many Portal

    CeciliaBaldwin

      Title

      Help Connecting Many to Many Portal

      Post

           Hello,

            

           I am  currently trying to make a many to many tables work, and i'm not sure if it's an error with my TOs or if I do not have the proper ids to match that isn't making my portal work. 

            

           This is what I am trying to achieve: I am manage a bakery and we do a lot of wedding cakes and want to be able to link the vendors with related accounts, so that we can keep track of who we've worked with where. As there are multiple categories of vendors - venues, florists, event planners, etc that I have subcategory (by a value list), I would ideally like to have in the Account Table a portal, where I can select the vendor (as each vendor has an id) , and then have it automically complete the relevant fields associated. Then, in the vendor table, have all the vendors information with a portal showing all the related accounts.

           I was able to successfully make the corresponding portals, but it would never allow me to have more than one vendor per account. 

           I have it set up with :

           Account Table                    Vendor Table

           account_id           =            account id

           vendor id             =          vendor id

            

           Any help would be GREATLY appreciated, as I have been fighting with it off and on for months!

        • 1. Re: Help Connecting Many to Many Portal
          philmodjunk

               Many to many relationships require a  third table, called a join table, that serves as the link between your account and vendor tables.

               Vendors-----<Vendor_Account>-----Accounts

               Vendors::__pkVendorID = Vendor_Account::_fkVendorID
               Accounts::__pkAccountID = Vendor_Account::_fkAccountID

               You can place a portal to Vendor_Account on the Vendors layout to list and select a Accounts record for each given Vendors record. Fields from Accounts can be included in the Portal to show additional info about each selected Accounts record and the _fkAccountID field can be set up with a value list for selecting Accounts records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               For a demo file that uses a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               FileMaker 12 users can open this file from the File menu to get a copy converted to the .fmp12 format.

          • 2. Re: Help Connecting Many to Many Portal
            CeciliaBaldwin

                 Ok, I'm beginning to follow you, but could I do the reverse , as it's in each of the accounts record that I want to have a portal to add related vendors. Then, in the vendor table, have a portal showing related accounts. (And with the inability to add accounts via that relationship). 

                  

                 And,  if yes to doing the inverse, would I reverse the link or does it matter? see ex below. 

                 Accounts---<Account_Vendor>----Vendors

                  

                 thank you!

            • 3. Re: Help Connecting Many to Many Portal
              philmodjunk

                   The links are the same and you use a portal to the join table on either a layout based on vendors or on a layout based on accounts. But in each case, you add fields into the portal row from the third table. So if you are putting the portal on the Accounts layout, you would add fields from the vendors table to the portal row.

              • 4. Re: Help Connecting Many to Many Portal
                CeciliaBaldwin

                     Ok, I've done all of that, can see my portal, but I can not add any new lines or actual vendors. i have made sure they're going to the right tables, I have records in each to make the reference, but it won't allow me to add anything. What step did I miss?

                • 5. Re: Help Connecting Many to Many Portal
                  philmodjunk

                       In Manage | Database | Relationships. Double click the relationship line to open the relationship details dialog. Select "Allow creation of records via this relationship" for the join table.

                  • 6. Re: Help Connecting Many to Many Portal
                    CeciliaBaldwin

                         Ok, I did that. Except that every time I add a record (ex - in accounts layout, on the vendors portal, add related vendor) it creates a new one instead of letting me select from a pre-existing vendor. I have the vendors from a value list, select from that, however every time it creates it like new in the vendor layout, instead of the pre-existing one.....

                    • 7. Re: Help Connecting Many to Many Portal
                      philmodjunk

                           The portal should not be a portal to Vendors. It should be a portal to Account_Vendor--the join table. You should be creating new records in this join table, but doing so will not create new records in the vendor table.

                           And you would format the Vendor_Account::_fkVendorID field as a value list of Vendor ID's and Names such that selecting a vendor's name enters the ID into the newly created join table record and links it to the selected vendor record. This then enables any fields from Vendors--such as a Vendor name field that you may have placed in the portal row, to display data from the correct vendor record.

                           You may find it helpful to take a closer look at the demo file so that you can see some of these "nuts and bolts" details in action and compare them to what you have set up for your file.

                            

                      • 8. Re: Help Connecting Many to Many Portal
                        CeciliaBaldwin

                             Ok, I know I'm doing something right and something wrong. I have the portal working 95% properly in the accounts file. I can pull up related vendors - (I have it w/ the vendor id, and vendor name) , but when I insert corresponding fields (such as primary contact - it leaves it blank, for me to fill in. I have been studying the linked sample database - it's very helpful - but i'm really stumped. 

                              

                             I also can't insert a portal on the vendors file to show related accounts... (which i was just using the vendors- accounts table again) ... I know I must be doing something dum, but i just can't figure out this problem. I really appreciate the help so that i can finally get it fully working!

                              

                        • 9. Re: Help Connecting Many to Many Portal
                          philmodjunk
                               

                                    but when I insert corresponding fields (such as primary contact - it leaves it blank

                               Either the fields are from the wrong table occurrence of Vendors or your value list is entering the wrong value or into the wrong field and thus there is no link to the selected vendor record.

                               

                                    I also can't insert a portal on the vendors file to show related accounts...

                               How/where/when does this fail for you? This may also becaused by the same isse that is causing problems with getting data from Vendors to appear in your join table's portal.

                          • 10. Re: Help Connecting Many to Many Portal
                            CeciliaBaldwin

                                 Victory! I figured out what was missing in the table and it now works great! Thank you for all the help!