5 Replies Latest reply on Sep 9, 2014 4:21 PM by philmodjunk

    Many-to-many or Table Occurrences



      Many-to-many or Table Occurrences


      Hello, I'm confused about the usage of a join table.

      Initially I've started from a flat file that contained all the information in these two tables. Addresses from contacts were on the same as company addresses. Therefore when exporting my company records had the same number of records as my contacts.
      I’ve got three tables in a many-to-many relationship: <companies>, join table <companies_contacts> and <contacts>. One company could use several contacts and one contact could be employed for several companies.
      __pkCompaniesID (serial)__fkCompaniesID (number)__pkContactsID (serial)
      company addresses__fkContactsID (number)contacts addresses

      In the layout companies I've created a portal to companies_contacts. When I verify the address information it is still correct. Now I wonder how I can add an existing contact in the portal. What do I need to do? I've looked at several examples, but it seems that a lot of them only use a table occurrence (also the starter solution Invoices in FM13). I think I've found a solution to add a new contact to the portal (thx to PhilModJunk), but not to add an existing one. As the last functionality is more common I would like to make it work.

        • 1. Re: Many-to-many or Table Occurrences

          The demo file that I sent you has the ability to select an existing contact or add a new one. A button with a + symbol is used to create new contact records and a field inside the portal row is formatted with a value list for selecting existing contact records. When you click on this drop down list or popup menu in the blank "add row" of the portal, you can select a contact and this same action creates a new record in the join table. You'll create one new join table record in the portal to the same each time that you establish a link between records in your two main tables.

          Caulkins Consulting, Home of Adventures In FileMaking
          • 2. Re: Many-to-many or Table Occurrences

            Indeed, the + "add" button creates a new contact and links it to the portal.
            This file made me realise that I need a TO. Before this it was very unclear.

            But if I have 10000 contacts it's not possible to use a drop down. Therefore I was thinking about something similar as the invoices starter solution with a popover. I only wonder why there isn't a join table and everything seems to be done by creating occurrences.

            Thx for the patience ;-)

            • 3. Re: Many-to-many or Table Occurrences

              You can actually use a drop down with 1000 contacts, but there are better ways. Since you are importing this data, you can populate the join table from your original Excel file.

              Moving forward, stay tuned for Adventures In FileMaking #2. This file is about to enter my version of "post production" where I read and reread all the text on every layout to make final edits and corrections and then it will be available for download. It focuses on "enhanced value selection" with one exploration layout dedicated to using popovers and sliders.

              and everything seems to be done by creating occurrences.

              I don't think you quite understand the meaning of "occurrences" yet. NOTHING can be done in Filemaker without Tutorial: What are Table Occurrences? in Manage | Database | Relationships.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Many-to-many or Table Occurrences

                Yes I agree. I think I've got confused by reading a lot of different methods and at the end of the day I can't seem to get one of them to work :-)

                I've looked deeper into your file and tried to get the (+) new contact button working in my setup. So far it runs without errors, it creates a second line in the portal but I can't fill it in, although "allow creation...." is selected correctly.

                What else could there be wrong?


                • 5. Re: Many-to-many or Table Occurrences

                  you have a problem in Line 4. The expression used to compute a value for $ContactID is enclosed in /* comment brackets */ and thus no value is assigned to $contactID. With no value in contact ID, you'll not get a record in the join table that is linked to the new contact. You'll need to edit that expression to both remove the /* */ brackets and then correct the expression so that it correctly references a table occurrence and field in your file rather than mine.