8 Replies Latest reply on Apr 24, 2017 9:55 AM by philmodjunk

    Join Table Issue

    unc12

      Can anyone help-out on this one?

       

      Have set up a join table to link a transactions table with resolutions table.

      Portal is only showing the first instance of the resolution in a transaction.

      Anyone have an idea why this is occurring?

       

      Thanks in advance.

        • 1. Re: Join Table Issue
          coherentkris

          if you have transactions > join < resolutions and try to show a portal to resolutions from a layout based on the transactions layout that is what you will get. You will have to show a portal based on the join table and any fields from resolutions that you want

          • 2. Re: Join Table Issue
            Malcolm

            That will happen if layout objects are not "in" the portal. You can check this in layout mode by nudging the portal 1px. If your fields don't move, they aren't "in" the portal.

             

            Malcolm

            • 3. Re: Join Table Issue
              unc12

              Thank you. All of the fields are in the portal.

              • 4. Re: Join Table Issue
                philmodjunk

                Then either your data or your relationships have a problem.

                 

                The typical relationships for many to many look like this(but very likely with different names):

                 

                Transactions----<Join>-----Resolutons

                 

                Transactions::__pkTransactionID = Join::_fkTransactionID

                Resolutions::__pkResolutionID = Join::_fkResolutionID

                 

                With these relationships, you can place a portal to Resolutions or a portal to Join on your Transactions layout.

                Using a portal to Join is best for adding/removing links to different resolutions, but a portal to Resolutions can be useful as a way to drop out duplications should the join table be used to link the same transaction and resolution more than once. (This may not be something that should happen in your DB, but there are cases where this is needed due to entering different data in other fields of the join table.)

                 

                So if you are seeing only one Resolution in a portal to either Join or Resolutions, you have either a relationship not right, something wrong with your layout--such as basing it on a different occurrence of Transactions, or the match field values are not correct.

                • 5. Re: Join Table Issue
                  unc12

                  Made this change, however while the portal rows are now sorting and grouping the resolutions correctly, that is per the correct transaction, portal information from the transaction table is not correct. It is showing the first instance in the transaction table in all rows.

                   

                  Getting this:

                  Reso 1........Transaction 1

                  Reso 2........Transaction 1

                  Reso 1........Transaction 1

                  Reso 2........Transaction 1

                   

                  Want this:

                  Reso 1.........Transaction 2

                  Reso 2.........Transaction 2

                  Reso 1.........Transaction 3

                  Reso 2.........Transaction 3

                   

                  Thanks again for any assistance.

                  • 6. Re: Join Table Issue
                    unc12

                    BTW am using a second occurrence of Transactions table and fields are from this occurrence.

                    • 7. Re: Join Table Issue
                      unc12

                      One more piece of info if I may, The layout is per a customer table. So I Have:

                       

                      Customer Table: Cust#= Trans Table:Cust #

                                                               Trans Table: Trans # = Join: Trans #

                                                                                                    Join: Reso # = Reso Table:Reso #

                       

                      Customer Table LAYOUT

                      Join Table - Portal Reference

                      • 8. Re: Join Table Issue
                        philmodjunk

                        I suggest posting a screen shot of your relationships. Including data from transactions in the join table makes this more complicated than you originally posted. How that "second occurrence" is added to the relationship graph and how you have used it to put fields into the join based portal make a difference here.