6 Replies Latest reply on Feb 17, 2013 3:35 PM by philmodjunk

    Self Join Portals



      Self Join Portals


           Hi  guys.

           I  setup a database for a small museum.

           I have Document , People and Location tables

           When i create a new record under the Document table i pick a location from the location portal ( which lists all cities and states)

            When i then go to the Locations table i created a Portal For the Document table which will show all Documents that are realted to that specific location.

           Now, when i create a self join table to the People table - Which allows me to link relatives to 'John Smith', while using the names  that are already in the database, I would like to  set 'Jane' as a wife and then be able to go to 'Jane's' profile and see 'John' listed there , the same way the location table shows all Documents related thru the Doc portal.

           But it doesnt work.  I can choose 'Jane' thru the self join portal and then i need to go the 'John' and set 'Jane' as a spouse

           Any help would be appreciated!



        • 1. Re: Self Join Portals

               This question seems to be asked with increasing frequency.

               Assuming that you have this relationship:

               People----<SelfJoin>-----People 2

               People::__pkPeopleID = SelfJoin::_fkPeopleID1
               People 2::__pkPeopleID = SelfJoin::_fkPeopleID2

               You can make the link between two records in People work in both directions if you create two records in SelfJoin, but with the values in the two _fk fields switched. This is a process that can be automated so that adding a new record in the SelfJoin table performs a script that checks for the existance of a corresponding "mirror" record and creates one if it does not already exist.

               The following relationship can make creating such a record easy to do:

               People----<SelfJoin----SelfJoin 2

               SelfJoin::_fkPeopleID1 = SelfJoin 2::_fkPeopleID2 AND
               SelfJoin::_fkPeopleID2 = SelfJoine 2:_fkPeopleID1

               If you "allow creation..." for SelfJoin 2, this single script step will create the mirror record if one does not already exist:

               Set Field [SelfJoin 2::_fkPeopleID2 ; SelfJoin::_fkPeopleID1]

               The allow creation option not only enable creating the second join record but populates the _fkPeople1 field in the mirror record with the value of the _fkPeopleID2 field of the current Join table record.

          • 2. Re: Self Join Portals

                 Thanks for the quick reply!

                 First, i did setup the self join you the way you mentioned. However,  i wasn't able to implement your technique.

                 1) Should i use 2 portals in the People table, one to choose relatives and one to list them?

                 2) self join 1 and self join 2 -  i should create a duplicate of the self join table? (self join 2)

                 3) if that is the case, then where does _fkPeopleID2 come into play?  Am i supposed to keep the 2nd People table , 

            Thanks again,


            • 3. Re: Self Join Portals

                   1) you should not need a second portal

                   2) You need a duplicate Tutorial: What are Table Occurrences?--not a duplicate table.

                   3) your existing join table shoule have two foreign key fields in order to facilitate your original self join relationship.  _fkPeopleID2 is the field name I used in my example for the second foreign key field, the one that identifies the related person to which you have linked as a relative of your current people record.

              • 4. Re: Self Join Portals

                     Thank you!

                     I finally got it!

                • 5. Re: Self Join Portals

                       once we are on this topic,

                       I created a field in the SelfJoin table called 'relative' so that when i pick 'Jane' i can go to the 'relative' field in the portal and choose 'Spouse/wife' from the dropdown.

                       Is there a way to 'port' the Spouse option and paste it on 'John's' portal row to display husband under Jane's profile. 

                       This would also help for listing children under a Parent's profile.

                       Thanks again for all your help!

                  • 6. Re: Self Join Portals

                         Given that the value of that field will depend on the value you select in the same field of a different record in the same table, I think you'd need a script to put the correct value in "relative" in the mirror record.