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

    Join table concept

    sudha

      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.

       

      Thanks

        • 1. Re: Join table concept
          Suresh

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

          • 2. Re: Join table concept
            nicolai

            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
              sudha

              Thanks Suresh.

              • 4. Re: Join table concept
                sudha

                Sure, Thaks Nicolai.

                • 5. Re: Join table concept
                  beverly

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

                   

                  EXAMPLE:

                  '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).

                   

                  beverly