Discussions

Expand all | Collapse all

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

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

    Posted 10-18-2017 18:45
    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.


  • 2.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-18-2017 20:12
    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. 


  • 3.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 05:52
    --> (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.


  • 4.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 13:37
    So no need for the Assigned Contractors table


  • 5.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 15:02
    Also you will want a joined for assigned Subs.  Sorry I skipped that one.


  • 6.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 16:32
    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.


  • 7.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 14:44
    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


  • 8.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-19-2017 19:16
    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


  • 9.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-20-2017 04:46


  • 10.  RE: I am trying to build a project relationship which involves subcontractors, assigned subcontractors and subcontractor contacts

    Posted 10-20-2017 13:48
    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.