Populate Child with Parent

Discussion created by cmj on Mar 14, 2018
Latest reply on Mar 18, 2018 by cmj

As a newbie, I'm under the impression that data typically flows throughout a database from Parent to Child.

I've run into cases where I need the flow to be the opposite. In the example shown below there is a need to assign a Contact to a Site. I cannot assign a Site to a Contact because a single contact may be related to many Sites.

Here's what I did:


  1. I created a field in Sites table called SiteContact.
  2. A table occurrence of Contacts is connected to Sites via  fkCompanyID. This limits the contacts  to only those who are with a related company.
  3. A drop-down list on the Sites layout is on the field SiteContact (in Sites table).
  4. A value list based on pk_ContactID (from Contacts table) shows the second field Contact_FirstLastName (from Contacts table).
  5. Selecting a contact from the drop-down list, populates the SiteContact field with a ContactID.


The contact is now related to the site.


Initial Problem:

The Site layout displays the contact as it should. Other fields related to the contact via the table occurrence are also on the Site layout (contact phone, contact email). These fields do not relate to the specific ContactID that was populated via the drop-down (someone else's phone number...).


My Solution

I joined the table occurrence with 2-joins:

Contacts_TO_Sites::fkCompanyID  =  CompanySites::fkCompanyID   And


Contacts_TO_Sites::pkContactID   = CompanySites::SiteContactID  


Now the contact phone and email agree with the selected contact.





Is this the proper way to approach this or am I way off the mark. Perhaps my table relationships are making this more complex? Maybe a join table would be better?


To me it's like I'm pushing the mule with the cart i.e. forcing the data (SiteContactID) to make the relationship work.


Any comments would be welcome. Thank you.