List items from one table to another table field when in a third table.

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

Example:

I have 3 tables Companies, Contacts and Encounters. Companies is currently related to both contacts and encounters. Each company can have many of each.

The encounters table stores information related to communicating or interacting with any said company. So if a phone call or an in-person visit was made it should be noted here.

One of the fields for encounters is contact person. This is who the rep interacted with during that encounter. I want my field to be a drop down of the names of the contacts that are only specific to that company, as recorded in the contacts table.

Do I need a relationship between encounters and contacts? If so how do I limit it to only contacts on that company? Is there a super easy way to just write a formula for this?


Thank you in advance.

Photo of Jordan

Jordan

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Jordan

Jordan

  • 0 Points
Nothing?
Photo of Xavier Fan

Xavier Fan, Champion

  • 340 Points 250 badge 2x thumb
You want to use a Conditional Dropdown for this.

The table relationships should be as follows:

1)  1 Company has many Contacts

2)  1 Company has many Encounters

3)  1 Contact has many Encounters

Then - in the Encounters table, go to the Reference field for the Contact / Encounter relationship (usually something like [Related Contact].

- go to the field settings for [Related Contact]

- Reference field options > Conditional values

Check the checkbox for "The values in this field depend on a selection in another field"

Set the corresponding "Related Company" values:

- e.g. A selection in "Encounters: Related Company" 

Shows choices where

"Encounters: Related Company" = "Contacts: Related Company"

This way - in an Encounter, you'd first select the Company, and the the Contacts dropdown will only show the Contacts related to that Company.



Here's the online help on Conditional Dropdowns:

http://www.quickbase.com/user-assistance/Default.html#conditional_dropdowns.html