6 Replies Latest reply on Mar 19, 2013 1:06 PM by Terri

    Self Join Relationship Question

    Terri

      Title

      Self Join Relationship Question

      Post

           Hi,

           I have records that have a product code, a style number, a color, a season and a comment field. There can be many style numbers under one product code and they can have the same color assortment. For example:
            
           Product Code 123456
           Style ABCDEF
           Color White
           Season SS 2013
            
           Product Code 123456
           Style XYZ123
           Color White
           Season SS 2013
            
           When someone is reviewing style ABCDEF they may make a comment - for example - liquidate. I need to make sure that comment is also applied ot the Style XYZ123 record. I think this could be done by a self join relationship.
            
           I've created another field called PC_Color. I've created a self join relationship with the following:
            
           Data::PC_Color = Data2::PC_Color
           and
           Data::Season = Data2::Season
            
           I've also checked the Allow Creation of Records on the Data2 table. However, when I enter a comment on the Data table for PC_Color 123456_White, that comment does not transfer to the Data2 table.
            
           What am I missing?

        • 1. Re: Self Join Relationship Question
          philmodjunk

               I think it would make more sense to put the comment in a related table. Then the relationship can make sure that all relevant records link to the same comment.

               I'd give a detailed example but I'm not sure why both records would be liquidated in your example.

               Is it because they have the same Product Code?

               The Same Color?

               The same Color and Code?

               The same color, code and season?

               or ???

               The answer you give determines the match fields to use in linking the comments table to your existing table.

          • 2. Re: Self Join Relationship Question
            Terri

                 The reason they would both be liquidated is because they have the same product code and color. If we are liquidating the women's regular size white then we are liquidating the women's petite size white. And the decision would be seasonally based. So we may be liquidating that product for SS but the fall style we are not. Hard to explain, and maybe liquidate isn't the best example. 

                 I guess I thought by using the self-join I was essentially putting the comment in a related table. Right now I enter a comment on the regular size and then do a search for the other related styles (by product code, color and season) and then copy the comment over. That is basically the same affect I'm trying to accomplish.

                 Thanks for your help.

            • 3. Re: Self Join Relationship Question
              philmodjunk

                   Then I'd use this relationship:

                   CurrentTable::ProductCode = comments::ProductCode AND
                   CurrentTable::color = comments::color AND
                   CurrentTable::Season = comments::Season

                   Then a single comment record made will be linked to all records of the same productcode, color and season. With "allow creation of record via this relationship", enabled, you can simply type a comment into a comment field in the comments table, but placed on your CurrentTable based layout and it will automatically appear with each of the other records of matching code, color and season.

              • 4. Re: Self Join Relationship Question
                Terri

                     Thanks Phil.

                     Can you explain why a self-join relationship does not have the same outcome? Just for my learning purposes?

                • 5. Re: Self Join Relationship Question
                  philmodjunk

                       Actually, the self join might produce the same outcome depending on how you design your layout and the relationships. But with a self join relationship, any single record may match to multiple related records and only one would store the comment that was entered. If you use that approach--which seems needlessly complicated, you have to design things in such a way that you are referencing the comment field of exactly the same related record no matter which record of a given product code, color and season is current on your layout.

                  • 6. Re: Self Join Relationship Question
                    Terri

                         Thanks for the explanation. I implemented your suggestion of the new table, imported the current comments that I had in the other table and it works like a charm. Thanks!