5 Replies Latest reply on Jan 9, 2012 6:22 AM by philmodjunk

    Contact related to many records

    KennethHibberd

      Title

      Contact related to many records

      Post

      I am sure this comes from lack of knowlege but that is why to ask you all.  I have a "projects" database.  In this database I have a few table, project, products, contacts.

      Each project can have many products.

        Each Product can have many contacts

      Each Project can have many contacts

       

      I have a portal in the layouts that display these contacts which are realted.  My dillema is that it is possible for a contact to be responsible for supplying many different products on many different projects.  Currently when adding a contact, it is always adding a NEW contact, but what if that same contact already exists?  How can I simply select that contact and relate him to a new product or project?

      If the contact already exists, I should be able to select that contact and autofill the fields for the related contact.

      Sounds like a script to me, but wnat to make sure.

        • 1. Re: Contact related to many records
          philmodjunk

          You appear to need these relationships

          Products>-------Projects---<Project_Contact>-----Contacts

          Project::ProjectID = Products::ProjectID
          Project::ProjectID = Project_Contact::ProjectID
          Contacts::ContactID = Project_Contact::ContactID

          Project_Contact is called a join table and makes possible a many to many relationship between Projects and Contacts.

          Here's a many to many demo file you may find helpful in understanding how to set this up:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: Contact related to many records
            KennethHibberd

            THis helps a bit.  I understand the concept, but when constructing it, I think I have misconfigured something.  In my Database the main tables are Projects, Contacts and Products.

             

            A Project can have many contacts.

            A Contact can have many Products.

            A product can have many contacts.

             

            I think that I understand that I need two joining tables here but even that I am not sure about.

            • 3. Re: Contact related to many records
              philmodjunk

              It depends on how you need to structure your data.

              Can an Item (Product) be part of more than one project?

              If so, you have another many to many relationship here.

              • 4. Re: Contact related to many records
                KennethHibberd

                Thank you very much for taking a look at this with me. Yes, but not often, it may be possible that a product has many projects. That I had not considered. However once I figure out the contact config, the product will likely be the same. I can add new contacts and products to each already. What I am tryin to achieve is simply that if a contact already exists, just use that one instead of adding a new contact.

                • 5. Re: Contact related to many records
                  philmodjunk

                  i don't think you understand. What we are discussing has little to do with adding records to products, projects and contacts. Instead it has to do with how you establish and manage connections between these three tables so that you do not need to create any records in these tables that are duplicates except for one field that links it to a record in one of the other tables. If it is ever possible that the same product can be part of more than one project, then you must either created duplicate product records for eah project or you use a join table to establish a many to many relationship so that one product record can be linked to more than one project record and one project record can be linked to more than one product record.

                  You may want to play around with the demo file to see how a given "contact" can be linked to more than one "event" and a given "event" can be linked to more than one "contact" and study how the Event_Contact table serves as a "join" table to make that happen. In the demo file, most of the portals are to different table occurrences of this same Event_Contact Join table. To related it to your project, the two tables could be named "Contacts" and "Projects".