Multiple same fields containing different information

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have an opportunities table, where a person can create a potential opportunity in the business. I also have a customer table (which is connected to a contact table, a customer can have many contacts). 

The opportunities and customer table has a many to many relationship, an opportunity can have many customers, and a customer can have many opportunities. 

In my opportunities table, I have been able to input a lookup field which allows the user to select a customer (and then a contact under that customer). 

However, because an opportunity can have many customers, I will need several lookup customer (and contact) fields. I have replicated the fields, and they seem to work when I am inputting information in to the form, however when I save the opportunity it is displaying all of the customer and contact fields as the same customer and contact. How can I get these lookup fields to save separate data from the customer table?

Photo of Rosie Jameson

Rosie Jameson

  • 100 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Blake Harrison - QSP & Certified Developer
Official Response
The preferred method to setting this up would be to have a 3rd table that would represent the Customer / Opportunity combination. This table would be the child to both the Customer and Opportunities tables. The Customer would then have no direct relationship to the Opportunity, and vice versa. Setting it up this way allows you to have an unlimited number of Opportunities related to a Customer and an unlimited number of Customers associated with each Opportunity. Setting up individual relationships for each is not a scalable solution and will end up causing other issues in the long run.