5 Replies Latest reply on Jan 7, 2016 5:44 AM by beverly

    Join table concept


      Hi all,


      Please confirm my understanding about join table concept:


      Consider three tables Parent A,Join A,parent B


      Parent A is related to Join A using Id1

      Join A is related to parent B using Id2


      In the layout, the main table associated is Parent A

      If I want to fetch related records from parent B, even though the relationship is not established directly, as the join has the unique combination of Id 1 and Id 2 for each record, Using these key values, related values will be fetched from parent B using the portal tool.



        • 1. Re: Join table concept

          Yes, As per I known Its Correct only, sudha!

          • 2. Re: Join table concept

            Just make sure that id2 is a primary key in the parent B table. Join A should have a combination of two foreign keys, one from A and one from B.

            • 3. Re: Join table concept

              Thanks Suresh.

              • 4. Re: Join table concept

                Sure, Thaks Nicolai.

                • 5. Re: Join table concept

                  If displaying related records (in a portal, for example), you *can* use fields more than one relationship away.



                  'Parent A' has:

                  1     Betty

                  2     Ann

                  3     Doris


                  'Parent B' has:

                  4     James

                  5     Bob

                  6     Edward


                  'Join A' can have:


                  1     4

                  1     6

                  3     4

                  2     5

                  2     6


                  This allows you to SEE (from Parent A, for example):

                  Betty shows James and Edward

                  Doris shows James

                  Ann shows Bob and Edward


                  (and the reverse is true is seen from Parent B):

                  James shows Betty and Doris

                  Bob shows Ann

                  Edward shows Betty and Ann


                  On *both* of these layouts the same portal is used with the relationship to 'Join A' (matching appropriate fields on the RGraph, of course).


                  You can add other fields in the Join A table (if any) to the portal, but mostly you want to show the relationship to the respective other parent table. Same portal relationship, different fields based on the direction of the other table.


                  Does that make sense?


                  In the same way, you can show (Customers->Invoices->InvoiceItems) all invoice items a customer has even though it's two relationships away. Only for that you can directly select the InvoiceItems for the portal relationship. It will respect that it's going _through_ the related invoices (for that customer only).