Avoiding selective duplicates on many-to-many relationships

  • 0
  • 1
  • Question
  • Updated 7 years ago
  • Answered
My goal is to be able to add multiple sites to a contract, but the same site might be on different contracts as well. So, I created a many-to-many using an intermediary table called Contracted Sites.

I have three tables in a many-to-many relationship: Contracts, Contracted Sites, and Sites. Each Contract can have many contracted sites. Each Site can have many Contracted Sites. My problem is in this setup the same Site can be added to a contract more than once (via a Contracted Site). i would like to restrict the same Site from being added to one contract. Meaning, a contract can contain multiple sites (via Contracted site), but can not have the same site more than once. Is that possible?

First thought was to try and use summary / relationship fields but what I really need to know is "how many of the same site are linked to each contract" which doesn't seem possible since the summary / relationship fields are per relationship and need to do a complex count across both relationships.

Second thought was to change the primary key on the Contracted Sites table to include multiple fields such as the "Related Contract" and "Related Site" so that would make it not possible to add a record that contains the same site and contract more than once. But the set key feature does not seem to allow multiple fields as one key.

Photo of David


  • 0 Points

Posted 7 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
In the table Contracted Sites create a formula field named [turkey] that concatenates together the fields [Related Contract] and [Related Site] and set the field to have distinct values:

[turkey] =
ToText([Related Contract]) & ":" & ToText([Related Sites])

This formula will produce values such as "23:145" when there is a join between the Contract with rid=23 and Site with rid=145. Making the field have distinct values will insure only one Contract is related to a specific Site. However, the error message that appears when you attempt to create a second join might be a bit cryptic in the sense that it would refer to the [turkey] field with the value of "23:145" already exists. You can fix this by concatenating other fields. For example, if you have fields in each of the Contacts and Sites tables that when concatenated together would uniquely identify a record in those individual tables you could create lookup fields in Contracted Sites and use those lookup fields in place of [Related Contract] and [Related Sites] in the above formula. The general idea is to create a formula field in Contracted Sites which must be unique when joining specific records from Contacts and Sites.

This conversation is no longer open for comments or replies.