4 Replies Latest reply on Nov 12, 2015 11:38 AM by mmmily

    Many Relationships


      I am trying to create a database for projects.  For each project, there can be multiple estimates, multiple invoices, and multiple related companies and contacts.


      Right now I am working out the companies / contacts part.  Currently, there are tables for Jobs, Contacts, and Companies.  Companies is related to contacts as a one to many relationship.  Jobs need to be related to both contacts and companies as many to many relationships.  I have made a table to reconcile the many to many relationship and make it one to many. This table called Link has a type field so that the role of the contact can be defined (client, assistant, etc.), and it has foreign keys for the company ID and contact ID. So then there are 4 total tables (Jobs, Companies, Contacts, Link).  Table names are in parenthesis below:


      (Jobs) <---->> (Link)<<---->(Companies)<----(Contacts)

      (Jobs) PK_JobID <---->>FK_JobID (Link)

      (Companies) PK_CompanyID <---->>FK_CompanyID (Link)

      (Companies) PK_CompanyID <---->>FK_CompanyID (Contacts)




      On the Jobs layout, I made a portal to the Link table.  In this portal I can select the company and a related contact from pop-up menus.


      However, the issue is that I am not able to display the related contact data, such as their mobile phone number or personal email address.  It currently defaults to one of the contacts mobile phone numbers and does not update as I select different contacts.  I think this is because I am not sure how to link the contact ID between Contacts and Link.  I've tried creating occurrences to fix this problem, but haven't been able to get the right result.


      The other problem is that there can be several contacts from a company on the same job and they can be different types and I need to display unique data from each of them.


      Also, in some cases, we do not have a specific contact, but only the company name so this is why I am not connecting the link table to the contact table and then pulling in the company data from there.

        • 1. Re: Many Relationships

          Re: "the issue is that I am not able to display the related contact data, such as their mobile phone number or personal email address"


          If you have the connecting relationship correct, there is no reason the data from the related file will not be visible, provided you display the fields from that relationship. The most likely reason for what you describe is that the fields on your layout which are showing incorrect data are from the wrong TO.

          • 2. Re: Many Relationships

            Hi keywords,  Thanks for your reply.  I am sure I do not have the relationship correct.


            I think the problem is something to do with the contact ID.  In the portal I have the fields, type, companyID and contactID. There are pop up menus that fill in the companyID and contactID with data from the companyID field from the companies table and the contactID field from the contacts table. The portal also shows data from the company table such as address.  I can not get it to show data from the contacts table related to the specific contact ID.  It just pulls the mobile and email of the first contact related to that companyID.


            Do you have a suggestion on how to revise the relationships to display that data?


            What is TO?

            • 3. Re: Many Relationships

              Re: "Jobs need to be related to both contacts and companies as many to many relationships"


              Based on the above, you may need a link table between Jobs and Contacts, as you have between Jobs and Companies already. It seems, from what you describe, that a Contact may not necessarily be associated with a Company, so a separate pathway may be needed.


              TO just simply stands for Table Occurrence (those boxes on the relationships graph). You can have many Occurrences of an underlying Table, and to view the related data you have to display field instances from the related TO.

              • 4. Re: Many Relationships

                I seem to have found a solution.  This is what I did:


                (Jobs) <---->> (Link)<<---->(Companies)<---->>(ContactsCopy)

                (Jobs) PK_JobID <---->>FK_JobID (Link)

                (Companies) PK_CompanyID <---->>FK_CompanyID (Link)

                (Companies) PK_CompanyID <---->>FK_CompanyID (ContactsCopy)

                (Jobs) <---->> (LinkCopy)<<---->(Contacts)

                (Jobs) PK_JobID <---->>FK_JobID (LinkCopy)

                (Contacts) PK_ContactID <---->>FK_ContactID (LinkCopy)

                And then on the layout I made two portals.  One of the link table where you select contact type, company, and then a contact from a pop up menu with only contacts from that company.  In another portal to LinkCopy, It shows the mobile phone number and the email of the contact.  By putting them side by side, and sorting them both by type, they line up correctly.