4 Replies Latest reply on Jan 8, 2013 6:52 AM by ShelleyM

    Portals and Join Tables

    ShelleyM

      Title

      Portals and Join Tables

      Post

           Hi...I have a database with two primary tables and one join table.  I created layouts for each of my primary tables (Properties and Contacts).  Each of layouts has a portal which shows related records from the join table.  Each layout also has a drop down list that displays a field from the other table to establish the join...hope this makes sense!  Is it necessary to have the portals show related records from the join table or can I have the portals show related records from the primary tables themselves and will this still create records in the join table?

           I would also like to sort by portal records, but the join table does not have the fields I want to use for the sort.  Example, on the Contacts layout I can view the property name (through the portal to the join table), it is a field from the Properties table.  I'd like to sort by this field, but when I go to sort the portal records, there is no property name field (only ID #s).

           I appreciate any help.  Thanks.

        • 1. Re: Portals and Join Tables
          philmodjunk

               Contracts----<Contract_Property>-----Properties

               You can place a portal to Properties on your Contracts layout and vice versa. This works out fine for displaying data from that primary table, but editing data via the portal becomes problematic. Delete portal row in a portal to Properties deletes a properties record--possibly leaving a number of "orphan" join table records that can no longer link to that property unless you enable a cascading delete in the relationship. Delete portal row on a portal to the Join table leaves the related property record intact, but deletes the link to properties for that specifc contact record.

               Interstingly enough, if you enable "allow creation..." for both the join table and Properties, you can add a new properties record in the portal and the linking join table will be automatically created. But trying to link an existing properties record to the current Contracts record cannot be done in a portal to Properties.

          • 2. Re: Portals and Join Tables
            ShelleyM

                 That makes perfect sense.  I don't want to delete a property just because it has a new contact.

                 I have another question regarding join tables.  How do I populate a join table for two existing tables each with hundreds of records and no common field?

                  

            • 3. Re: Portals and Join Tables
              philmodjunk

                   Is it even possible?

                   If these were data written out on 3x5 cards--one stack for Properties and one stack for Contracts, how would you determine which contracts to link to which properties?

              • 4. Re: Portals and Join Tables
                ShelleyM

                     Thanks Phil.

                     Yesterday was just too long!