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

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
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.
Photo of Joey Zint

Joey Zint

  • 1,508 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Claude

Claude

  • 70 Points
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. 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
--> (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.
Photo of Joey Zint

Joey Zint

  • 1,508 Points 1k badge 2x thumb
So no need for the Assigned Contractors table
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Also you will want a joined for assigned Subs.  Sorry I skipped that one.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Claude

Claude

  • 70 Points
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
(Edited)
Photo of Joey Zint

Joey Zint

  • 1,508 Points 1k badge 2x thumb
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
Photo of Claude

Claude

  • 70 Points
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.