7 Replies Latest reply on Jun 6, 2012 9:29 AM by Stephen Huston

    Relationship with portal based on multiple fields

    MattLeach

      Here is my scenario.

       

      I have two tables that i would like to relate: Transactions and Companies.

       

      In the Transactions table, there are 6 fields which can contain the company (Buyer1, Buyer2, BuyerBroker, Seller1, Seller2, SellerBroker). Each of these fields is a drop down pointing to a value list of companies from the companies table.

       

      What the client would like is on the companies layout, have a single portal that will list any transaction where the companie's name is in one of the 6 above mentioned fields.

       

      I know i can accomplish this using 6 separate portals with a separate relationship for each but they would like this in one.

       

      How would i accomplish this in a single portal?

       

      Thanks

        • 1. Re: Relationship with portal based on multiple fields
          Stephen Huston

          You could make the fields for the 6 values part of a join-table, removing them from the parent table and making them all choices in one field of the join-table, as links between the other two tables.

           

          They could be used in one portal for establishing the links - creating the child records for these types of "persons", and have a separate portal for listing all transactions (grandchild-records) that are linked via the child records (buyers/sellers).

           

          You end up with 3 tables, but you remove all of the 6 fields from the parent table into a sinlge related field for records of byers/sellers as the Child records, with transactions as the grandchild records.

          • 2. Re: Relationship with portal based on multiple fields
            MattLeach

            I think i have an idea of what you are describing so i put together a test file. Are you saying i need a portal to the join table for each field? I've attached my test file so you can see where i was going with it.

            • 3. Re: Relationship with portal based on multiple fields
              Stephen Huston

              You've got the 3-table idea, but the relationship and fields aren't ready  yet.

               

              You parent table should be the Company.

              Your child table should be the buyers/sellers table. Should have a field for name, buyer-seller-type, Company_ForeignKey and it's own PrimaryKey field.

              Your grandchild table is Transactions. It should have your transaction details and a ForeignKey field for the buyers/sellers table.

               

              company and buyers/sellers link:  Company::_pk_CoID = Buyers_Sellers::_fk_CoID

              buyers/sellers link to transaction:  Buyers_Sellers::_pl_BuyerSeller = Transaction::_fk_BuyerSeller

               

              This will provide a direct link via portals to both buyers/sellers from company, and a link for the transactions from company (through the buyers/sellers table).

              • 4. Re: Relationship with portal based on multiple fields
                MattLeach

                Why would the Company need to be the parent table?

                 

                The reason i ask is in this particular database the Transactions table is always the main table that the client does their data entry into, thus allowing them to select the company as the buyer / seller / broker.

                 

                I've edited my test database and it doesnt seem to be making sense, how do i select the company from the transactions screen?

                • 5. Re: Relationship with portal based on multiple fields
                  MattLeach

                  Disregard, I understand now and have it working. Thanks for your help Stephen

                  • 6. Re: Relationship with portal based on multiple fields
                    MattLeach

                    Actually i have hit another snag trying to implement this. Currenly each Buyer and Seller field is a drop down field tied to a specific value list like so:

                     

                    Buyer 1 and 2 = companies with a type of Investor

                    Buyer Broker = companies with a type of Arranger

                     

                    Seller 1 and 2 = companies with a type of Investor

                    Seller Broker = companies with a type of Arranger

                     

                    By setting up a join table with a company name field, i lose the ability to have these separate value lists as i can only specify one list. Any suggestions on how to work around this? I can provide a sample file for more clarification if needed.

                     

                    Thanks,

                    Matt

                    • 7. Re: Relationship with portal based on multiple fields
                      Stephen Huston

                      You could move all of these values into another table (Buyer_Class), add a field for the Type, and a field for the Type in the Buyer/Seller table, which, once entered would limit the values from the Buyer_Class field to be displayed.