Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

Need to add 1+ client contact names to a project table

I have created a join table "Projects" that lists information from 12 separate tables.  Most issues are working out with patience, but 1.  When a project is created in the "Projects" table that will have at least 1, more likely 2 customer contacts, maybe 3 (not that I like that).  These contacts are stored in a "Contacts" table that also has a relationship to a "Company" table.

So currently I have tried a "Projects" to Customer" relationship and vice-versa.  But all I get is an option to choose a single contact from a drop-down or an ADD button.  It seems like a "Project" to many "Customer" relationship is ideal, as it provides the ADD button. But this way I can only create an new "Customer". 

Currently the customer name is a text field, though I tried a text-multiple choice, and not matter what I can't get it to pick from existing customers.  I do need to provide new customer information, but I don't need to re-enter old data.

Can somebody provide some guidance on this seemingly simple issue?

Thanks!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    This sounds like you need a Many to Many relationship.
    One project has many customers.
    But also 1 Contact is used on Many Projects.

    So you need to make a new table called Project Contacts.  Then make two relationships.

    1 Project has many Project Contacts
    1 Contact has Many Project Contacts.

    When you get that working, you will want to edit the field for related contact on the new table so that so that once a lookup field populated for the Company on that join table, that the drop down list only lists Contacts for that same company.

    Once you get the many ot many set up, I can help you improve that drop down list if you get stuck there.