Forum Discussion

ZintJoseph's avatar
ZintJoseph
Qrew Captain
7 years ago

I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

I am struggling to created the following relationship with the tables above

  • Each project will have many assigned subcontractors
  • Each subcontractor may have many contacts
So when I assign a subcontractor to a project, I then want to be able to select from a list of contacts for that contractor and enter more than one.

Thanks for the help.

9 Replies

  • 1. Its best to create a table for each category (Contractors, Sub-Contractors, Contacts).
    2. Make sure each table has a key field that is unique.
    3. Decide which table will be your main table. This table will be on the many side of you "one-many" relationships. 
    For example: if projects is your main table, your first relationship would be a "1-project to many-contractor", "1-project to many-Sub contractor", and a "1-contractor to many-contacts" relationship.

    The parent would be able to write to the child and allow you to add more than one contact. 
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    --> (Has many)

    SubContractors --> Projects
    SubContractors --> Contacts

    Projects --> Sub Contacts
    Contacts --> Sub Contacts

    "Sub Contacts" is the joined table that allows you to add many contacts to one project.
    You can make the selections conditional based on the Sub, so you have a narrow list to choose from.
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      Also you will want a joined for assigned Subs.  Sorry I skipped that one.
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      And you'd probably want the Assigned contacts table to be a child of the joined subs.  
      But depends on how big your contacts list is, and what user experience you'd want more.
  • There are an endless number of solutions.
     
    The basic concept is to make sure that the tables that you are looking to reference from your main table are on the "one" side of your "one-many" relationships.

    There also has to be a field on the main report that matches the key field on the reference table. Also, ensure that the key field on the reference table is unique. 

    If your main table is projects and your looking to reference contractors, ensure that contractors  are referenced on your projects table in a common field.

    Example: 

    Projects Table - record 1 
    Project (field): A
    Contractor (field): 1<-- "this would be the common field"

    Contractors Table- record 1
    Contractor (field): 1(key field)<--
    Name (field): John Smith

    1 Contractor - Many Projects
    Key field(Contractor)

    Now you can bring over "look-up" fields to the Projects table from the Contractors table that reference the contractors.

    Projects Table - record 1
    Project (field): A 
    Contractor (field): 1
    Related Projects (field): 1
    Contractors - Name: John Smith
  • So just to clarify

    Projects will have many Assigned Subcontractors
    Subcontractors have many Sub Contractor contacts

    Tables

    Projects
    Assigned Sub Contractors
    Subcontractors
    Subcontractor Contacts

    My goal is to pick the subcontractors (could be multiple) for a project and then pick from a list of contacts for up to 4 people from each sub as subcontractors may have many contacts
  • The best solutions would be to setup 2 relationships:

    Relationship 1: (between Projects and Sub-Contractor's Tables)
    1 Project - Many Sub-Contractor Assignments, 

    Relationship 2: (between Sub-Contractor's and Contacts Tables)
    1 Sub-Contractor - Many Contacts

    This will allow you to select multiple Sub-Contractor(s) for 1 project and multiple Contacts for 1 Sub-Contractor.