6 Replies Latest reply on Jun 10, 2014 12:14 PM by Stephen Huston

    Show related record of a related record in a portal

    camberol

      I have 3 tables:

       

      Companies

      Contacts

      Phones

       

      Companies::ID = Contacts::parentID

      Contacts::ID = Phones::parentID

      Companies::ID = Phones::gparentID

       

      Contacts and Phones have a primaryField set to auto-enter 1 if it is the first record created that is related to another record by the parentID.

       

      Only 1 Contact can be selected as a primary for each Company.

       

      Only 1 Phone can be selected as a primary for each Contact.

       

      How can I show the primary Phone for the primary Contact in Companies?

      How can I show the primary Phone for each Contact in a portal in Companies?

       

      Is it possible that the only way to show the primary Phone for the primary Contact in Companies is to add a child field to Companies for the ID of the primary Contact and add Companies::primaryChild = Contacts::primary as an additional condition of the relationship?

       

      Is there a better or simpler way?

       

      Any advice would be appreciated.

       

      (I have uploaded two picture to help)

       

      Greg

       

      Message was edited by: camberol

        • 1. Re: Show related record of a related record in a portal
          Stephen Huston

          I assume that the Phone value "Primary" is from a value list, and is a value in one phone.

           

          If you sort the actual relationship between Contacts — Phones based on that value list, and make sure that the "Primary" value is listed first in the value list, the Primary number should be the one which appears because it is then the "first" matching record across the relationships.

          1 of 1 people found this helpful
          • 2. Re: Show related record of a related record in a portal
            camberol

            Stephen,

             

            Sorry for a failure to more fully explain.

             

            It is just the number 1, set by a script. I will try your suggestion.

             

            Thank you

            • 3. Re: Show related record of a related record in a portal
              camberol

              This technique allows me to show the primary record one relationship away with a simpler relationship.

               

              Thank you.

               

               

              Any idea how I can get the portal in Companies that shows Contacts to show the primary Phone for each Contact?

               

              Greg

              • 4. Re: Show related record of a related record in a portal
                keywords

                Try the following:

                 

                1.     Create a constant field (call it, say, @one) in both Contacts and Companies tables with the value 1 (easiest way is a stored number calc field set to 1).

                2.     Set your Primary field to 1 for the selected phone number

                3.     Create a relationship with the dual values:  Contact/Company::IDfield = Phone::company/contactIDfield AND Contact/Company::@one = Phone::primaryRecord

                 

                That would isolate a single phone record in each case, as long as only one phone number was tagged as the primary one.

                 

                Another method would be to use the standard ID = ID relationship, sort it so that the primary record displays at the top of the portal, and maybe set the portal to display just one record.

                • 5. Re: Show related record of a related record in a portal
                  erolst

                  camberol wrote:

                  Is it possible that the only way to show the primary Phone for the primary Contact in Companies is to add a child field to Companies for the ID of the primary Contact and add Companies::primaryChild = Contacts::primary as an additional condition of the relationship?

                   

                  That's not the only way, but AFAICT, it is the recommend approach.

                   

                  From an efficiency point of view: every company is bound to have a primary contact, but not every contact can be the primary one. Same with contacts and their communication.

                   

                  That's why it makes more sense to encode this as an attribute of the parent, instead of the child. It also allows you to forego the “set new flag, unset old flag” song and dance; simply set/toggle the foreign key.

                   

                  Find attached a practical demonstration of this approach.

                  • 6. Re: Show related record of a related record in a portal
                    Stephen Huston

                    If they are the same TOs where you sorted the relationship, just adding the related field should be enough to accomplish this. If, not, then you can also sort that relationship.