6 Replies Latest reply on Feb 10, 2013 9:59 AM by willem67

    self-join does not work



      self-join does not work


           I would like to create a self-join.

           I have a table with records of paintings. Some paintings are related to other paintings. One painting can be related to more than one painting. The other painting can also be related to other paintings. A many-to-many relation.

           I link the records manually with a valuelist.

           I tried to solve this issue with a join table since it is a many-to-many relation. I have been able to link painting A to painting B and show this in the portal of record A. But it does not show painting A in the portal of painting B. This is correct because I have two TO's of the same table and the portal shows only records of one TO.

           How to mirror the records to one portal?




        • 1. Re: self-join does not work

               Join tables form a "one way link" from record A to Record B. To form a two way link, you need to create a second join record linking Record B to Record A.

               So if you have these 2 records in your Paintings table:

               PaintingID: 1
               Title: Mona Lisa

               PaintingID: 2
               Title: Last Supper

               A join table would need two records to create links in both directions:

               JoinID: 1
               _fkIDPaintingA: 1

               JoinID: 2

          • 2. Re: self-join does not work

                 Thank you. That works well.

                 But I will have to link twice if I like to mirror the link in the portal in both records, isn't? Once from the record of painting A to the record of painting B and vice versa? 

                 It will not automatically mirror, because I have two TO's and I can only show one TO in the portal?

                 Can you confirm this?

            • 3. Re: self-join does not work

                   You could use a script to automatically create the mirror (my jargon) record for this to save the extra data entry.

                   It will not automatically mirror, because I have two TO's and I can only show one TO in the portal?

                   I'm not sure what you mean by that. A layout and a portal can only be based on a specific TO. If you have this relationship:

                   Paintings----<PaintingJoin>-------Paintings 2

                   Then your layout is always based on paintings. You can use a Portal to PaintingJoin or a portal to Paintings 2 to list the other paintings linked to the current painting records in Paintings.

                   You may find this demo file on many to many relationships helpful for exploring some options in how to manage the data in such a relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: self-join does not work

                     Apologies for my late reply. Thank you. I think I understand your ideas.

                     My conclusion is that I will need a script to create the mirror record. This is not only to save the extra data entry but also to reduce the risk of forgetting to create the mirror link/record.

                     Does anyone know an example for such a script?


                • 5. Re: self-join does not work

                       The details will depend on how you create the initial join record and the script needs to avoid creating more than one mirror record so it has to check for the existance of such a mirror before creating it.

                       The simplest way to do that that occurs to me is to add an additional occurrence of the join table linked in like this:

                       Paintings----<PaintingJoin---PaintingJoin 2

                       PaintingJoin::_fkIDPaintingA=PaintingJoin 2::_fkIDPaintingB
                       PaintingJoin::_fkIDPaintingB=PaintingJoin 2::_fkIDPaintingA

                       Enable 'allow creation of records...' for PaintingJoin 2 in this relationship.

                       Then this single set field step, from the context of the newly created record in PaintingJoin:

                       Set Field [PaintingJoin 2::_fkIDPaintingB ; PaintingJoin::_fkIDPaintingA]

                       Will create the matcing "mirror" record if it does not already exist.

                       The full script might be:

                       If [Not IsEmpty ( PaintingJoin::_fkIDPaintingA ) and Not IsEmpty ( PaintingJoin::_fkIDPaintingB ) ]
                          Set Field [PaintingJoin 2::_fkIDPaintingB ; PaintingJoin::_fkIDPaintingA]
                       End IF

                       And an OnObjectSave trigger on a portal to PaintingJoin could be set up to run this script.

                       You might also specify the "delete" option for PaintingJoin 2 so that disconnecting two records by deleting the join table automatically deletes the mirror record.

                  • 6. Re: self-join does not work

                         Thank you. I am still trying to get the script up an running. Will let you a.s.a.p. when I have good results.