5 Replies Latest reply on Nov 5, 2015 7:10 AM by sydbarker

    show contacts from one company at a time?

    sydbarker

      I think I am just missing  something so very obvious as to what is going on, but I can't figure it out.

      What I have is a contacts database that I need to be able to see all contacts associated with a certain company, but my one to many relationship is a little confusing.

      I have multiple companies with multiple contacts who are all associated with multiple projects, but at the same time, one contact could be related to multiple companies so I'm not sure how to structure this. below is a screenshot of my relationships so far. They are joined by a serial number field in the Personnel table and a regular number field from the additional tables.

      On my contact entry page, when a contact is entered, the CompanyName field is from the Company table. What I tried to do afterwards was make a layout based on the Company table, and place a portal on the layout based on the Personnel table to be able to look at all contacts associated with one company. Instead, the companies are not grouping with associated contacts.

      I cannot for the life of me figure out what is going on, or how these tables and relationships should be structured in order to view all contacts associated with each company, view all contacts associated with each project, and all companies associated with each project.

      Any advice is greatly appreciated!

        • 1. Re: show contacts from one company at a time?
          bigtom

          Can you post up a screen shot of the relationship details and the portal setup?

           

          How are the ID fields in each table being filled. The company field should be an auto enter of some sort and the related field should not be auto entered.

           

          Looks close. Having the relationship go both ways get a little more detailed but possible.

          • 2. Re: show contacts from one company at a time?
            sydbarker

            portalexample.jpg

            The ID field in Personnel is auto enter serial number, as well as the related field in Company. I tried to change this around, and have the Personnel as a regular number field and keep the auto enter field in Company, but my Company::CompanyName data disappeared. I can change it back for the purpose of trial and error according to this thread.

            • 3. Re: show contacts from one company at a time?
              DavidJondreau

              If you have personnel that can belong to multiple companies and a company that can have multiple personnel, then you need a "join table". An intermediary table that links the two. You wouldn't store the Company ID in the Personnel table or the Contact ID in the Company table but both in the Company Personnel Join table.

              • 4. Re: show contacts from one company at a time?
                bigtom

                The reason you lost the company connection is because you need to select the company in the person record again to connect it to the company.

                 

                As David suggested, you do need a join table if this goes both ways. I did want you to understand the basic relationship first though. The idea is that two unique auto enter serials cannot equal each other so will will never be able to see only the related records. I would also recommend auto enter calculation Get (UUID), but a serial number works as well.

                • 5. Re: show contacts from one company at a time?
                  sydbarker

                  Is there any way to do a join table without needing to input the related ID's? I followed a couple tutorials found on youtube for creating join tables, and it doesn't look like it's exactly what I need. The managers who requested this database want to be able to flip between layouts, and see all associated records together without needing to input an ID to show the values in the portal. What is the best way to go about this?