4 Replies Latest reply on Jun 12, 2016 9:47 PM by DanHarris

    Join Table Setup

    DanHarris

      So, I've created a join table to join my Facilities table to my Addresses table, but I'm having trouble making the relationship work.  Essentially I want to be able to input different addresses (physical, mailing, etc.) for each facility from my Facility layout.

       

      I've created the proper parent and foreign key fields in these three tables, linked them as relationships, and created the portal on my Facilities layout. But I can't figure out how to add the various addresses via the portal. In the past, I've always created records in both my parent tables and then used a drop-down list in the portal to select the field (value list) in the second parent table and this would populate the join table foreign fields at the same time.

       

      But now I would actually like to create records (addresses) in the second parent table via the Facilities layout portal. How do I do this while also populating the key fields in these related tables?

        • 1. Re: Join Table Setup
          PeterWindle

          If your child table has a foreign key field (indexed of course) and it is linked to the parent table's unique serial number field (primary key) then the moment you create a record by entering the portal, the link is established, as long as the relationship between the parent and child is set to "allow creation of records via relationship".

          Parent table - unique number field (ID)

          Child Table - indexed number field

          allow creation of related records via the relationship checkbox must be ticked

           

          (it can be number or text, as long as the parent is a unique ID)

          • 2. Re: Join Table Setup
            keywords

            Your description suggests you may be a little confused about join tables. See if the following helps:

            1.     Facilities are stored in their own table

            2.     Addresses are stored in their own table

            3.     A Facilities–Addresses join table serves the purpose of rationalising a potential many-to-many relationship between theses two (Any Facility may have many Addresses; any Address may be used by many Facilities). The idea of the Join table is to break this down into a series of one-to-many relationships (THIS facility is linked to THIS address).

            4.     Each join table record only needs to contain a field to hold the FacilityID and a field to hold the AddressID. Nothing more is needed (although other fields may be added for specific purposes).

            5.     If you want to create a join record to a new address then you will need to create the address record first, otherwise there is nothing to join to.

            • 3. Re: Join Table Setup
              karina

              Hi,

               

              What keywords advised is right.

              I made you a sample file.

               

              It's also possible to create an address directly in the portal but I added a popup window with the existing address because I think you want to prevent having double addresses in your address table.

              You can also use a drop-down list if there aren't that much address records.

              1 of 1 people found this helpful
              • 4. Re: Join Table Setup
                DanHarris

                Thanks everyone for your replies, I got it figured out. All my tables and relationships were ok, the issue was that I had the Auto Enter Serial Number set to "On Commit" and not "On Creation." Also, I failed to have my keys set to Indexing "All".

                 

                Once I got that changed I was able to begin entering addresses into the Addresses table from the portal on my Facilities layout.

                 

                Thanks Karina for the example you created, it's really got me thinking about some formatting options I may want to consider adding to my solutions. You bring up a great point on the duplicating addresses within the Addresses table. It's highly likely that some of my facilities will share an address (like the mailing address). Your popup window set to list view is great.

                 

                Once again I really appreciate all the help everyone.