Forum Discussion

SteveHanson's avatar
SteveHanson
Qrew Trainee
6 years ago

Multiple Contacts to Multiple Organizations and Vice Versa

I have an app with a table for organizations and a table for contacts. I would like to set the app up so each organization can have multiple contacts assigned to it and so that each contact can have multiple organizations assigned to it. I have found similar issues on the forum regarding the table relationships, but can?t pin down a solution that will work for me.

Thanks in advance for the help. 

8 Replies

  • You need what we call a "many-to-many" relationship structure using a "join" table.

    Organizations (table)
    Contacts (table)
    Organization Contacts (table)

    Organizations < Organization Contacts (parent < child)
    Contacts < Organization Contacts (parent < child)

    You probably have yours setup as Organizations < Contacts; but you need to separate the two so they act independently and then create the "join" table between them that allows you to associate one contact to one or more Organizations.  Then you will be able to see in the Contacts table which Organizations that contact has been associated with.
  • Is this also called a bridge table? Also, what data do I put in the new joint/bridge table? 
  • It's probably called many things; but yes a "bridge" would be an appropriate label for it.

    You simply build two relationships to your Organization Contacts table to "connect" your two parent tables. So each record has two related-reference fields that you populate. Depending on what parent-table you start in to create the Organization Contact, that parent-table can auto populate one of those relationships for you; so you are only populating the other relationship manually. 

    Then add to each relationship the data from those parent-tables (that you want) as Lookup fields so each record in your Organization Contacts table displays both the Organization information as well as the Contact information that you have added as Lookup fields from the two parent-tables via the relationships.


  • TateForgey's avatar
    TateForgey
    Qrew Assistant Captain
    The example used most often to demonstrate this is using a "Registrations" table to join a students table to a classes table so that one student record can be associated with many class records and one class record can be associated with many student records.  Relating a single student and class to a record in the registrations table (for as many associations as you need) means you can put a report of all the registration records on the class showing the related students and different report on the student record showing all the related classes.  Whatever data you want to appear in those reports you "push" down to the bridge table through the relationship with the parent tables.

    It is a little hard to explain, but the easiest way for you to explore this may be through an app in the exchange, Many-to-Many Relationship Example App which seems to use this classic example.  In your case, replace classes with organizations, students with contacts, and I might make a bridge table called "Members" instead of registrations, but "Organization Contacts" is fine as well. 
  • Is there any place that has step-by-step instructions? I created the join Table and added the two relationships, but I don't know where to go from here. 

  • Thanks! Your previous link made me login to something I don't have access to, but I could open this most recent one. I'll give it a shot!