Primary Contact and Secondary Contact Conditional Dropdowns

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have 3 tables: Clients, Contacts, Projects.

I created the following relationship to allow me to do conditional dropdowns based upon which client was selected for a project.

Clients<Projects

Clients<Contacts

Contacts<Projects

However, I need a Primary and a Secondary contact on most projects and they may change or switch on the next project for the same client. I got it to work for the Primary Contact, but when I try to add the same field again for the secondary contact, it ceases to work. I could use a formula on the Secondary Contact field (possibly), the cascading look-ups for Title, Phone, email, and comments won't display properly. Is there a way of using a duplicate conditional dropdown field?

Photo of Audra

Audra

  • 10 Points

Posted 5 years ago

  • 0
  • 1
I suggest that you rename your on the Project table which relates Contacts to Projects to be named Primary Contact. Typically for best practice, you would have the relationship built on a (numeric) field being Related Contact, and the. You would have a lookup field for contact name and in the Related Contact field set that to have the Proxy field of contact name.

Then copy the Related Primary contact field. It will automatically make a new relationship for you and then do that lookup for Secondary contact name and make it the proxy field for related secondary contact.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
I have recently discovered that two proxy references will cause both to not work.

The solution for me was to use lookup references (without any reference proxies) that, as Mark wrote above, populate Primary Contact and Secondary Contact fields, these links come from two separate relationships (Contacts to Projects for Primary Contacts, Contacts to Projects for Secondary Contacts) and on the form I used the reference field for add/edit, and the lookup field for view (so that just the ID # wasn't showing). Hope this helps!!
Photo of Audra

Audra

  • 10 Points
I actually created a relationships between the same two tables twice and worked it that way. I was having trouble getting it to work conditionally and with cascading dropdowns with the other two methods. Though perhaps I was doing it incorrectly...