Skip to main content
Question

Multiple same fields containing different information

  • October 27, 2017
  • 0 replies
  • 31 views
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?

Thanks, 
Rosie
    This topic has been closed for replies.

    You will have to create multiple relationships between Opportunity table and Customer table to save separate data from the customer table.
    • ArchiveUser
    • Registered
    • October 27, 2017
    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.
      Terms & ConditionsAccessibility statement