3 Replies Latest reply on Dec 31, 2012 6:06 PM by philmodjunk

    Associating Child and Grandchild Data



      Associating Child and Grandchild Data




           I need help looking up data and associating a Record in a Parent - Grandchild relationship


           Referrer::Referral Office

           Referral Office::Referral Office Contact Data

           On a Referrer Layout I want to have a field that has a popup or dropdown menu for Company Names in Referral Office Contact Data that are related through Referral Office.  Referral Office Contact Data is an instance of Contact Data that holds Contact information for all Persons or Companies in the Database so it can't pop up with all company names for Vendors, Customers, etc.

           Once the Company Name has been chosen I want to have it autopopulate additional data from Referral Office Contact Data such as addresses and phone numbers in other fields.  It is not necessary to edit the data from Referrer to Referral Office Contact Data, but it could be nice.

           I have tried many attempts but cant make it work.  The closest I get is to view existing names but when I choose one it tries to create a new record using the same name in Referral Office Contact Data, which defeats the purpose.

           Any assistance would be appreciated.



        • 1. Re: Associating Child and Grandchild Data

               As I understand from your screenshot, these are the relationships that you have:

               Referrer-----Referral Office-----Referral Office Contact Data

               Refferer::RefOfficeIDLink = Refferal Office::RefOfficeID
               RefferralOffice::ContactIDLink = Referral Office Contact Data::ContactID

               And all of these are one to one relationships.

               I would guess that your layout is based on Referrer.

               Selecting a company name in the company drop down "tries to create a new record" in what table?

               When you check the drop down while in layout mode, is this a field from referrer or one of the other table occurrences?

               Since these are one to one relationships, I see no compelling reason for separate Referrer and Referall Office tables. Merging them into a single table might simplify the issues that you are having here.

          • 2. Re: Associating Child and Grandchild Data

                 Referral Office to Referrer is a one to many relationship.  The Contacts can be referrers and/or clients and or vendors, etc so they have seperate occurances of the Contacts Table but I wanted to have basic information in the Contacts Table and then specific information for Clients or Referrers or Vendors in their respective tables

                 A new record is created in the Contacts table (Referral Office Contact Data) when we choose a name out of the drop down.  The trouble seems to be that a Referral Office is not selected when I choose the Company Name from referral Office Contact Data.  Ti seems to bypass associating the records in the Child relationship.





            • 3. Re: Associating Child and Grandchild Data

                   Yep, I see the one to many now. Must have missed that the first time around., but you can establish a link between Referral office and contacts without using an additional Referrer table in between and you can still limit relationships to match only to the desired sub set of contacts.

                   What you describe in terms of that drop down suggests that the drop down field is based on a field from contacts (Referral Office Contact Data) instead of referrer--probably with "allow creation..." enabled in the relationship. Correctly getting a linking record in referrer between Contacts and referral office is an issue, but it's not what is creating the new record in contacts unless you have a script trigger performed script.

                   Go to the layout shown in your screen shot. Enter layout mode. Click on the drop down to select it. What do you see named to the left of the :: in the display data from box on the Inspector's data tab? I suspect you will see "referral office contact data" and this will be the wrong field for this.