Many-to-Many Relations

This type of relationship involves multiple connections between records in the first table and records in the second table. A Many-to-Many relationship uses a match condition to link related records and allows you to create summary columns and display matched detail records on both sides of the relation.

Many-to-Many references and Many-to-Many relations are different and not substitutes for each other. While a Many-to-Many reference creates a multi-reference column and a link table to store selected records, a Many-to-Many relation dynamically matches records without creating any additional physical columns or tables.

Example 1: Calculating Monthly Payments

Let’s assume you manage a Design Service Agency and calculate monthly payments to each designer according to the hours they worked. In this case, a Many-to-Many relation between the "Hours Worked" table and the "Monthly Payments" table can be helpful.

In most cases, a Many-to-Many relation comprises match conditions. For example, there is the "Designer" column in the "Hours Worked" table and the "Monthly Payments" table. This column comprises Designer Id value. As a result, a Designer=Designer match condition can be added to the relation.

After that, the "Total Hours" summary column can be added to the "Monthly Payments" table, where the total number of designers’ worked hours is calculated.

Example 2: Managing Customer Claims

Let’s say your Health Insurance Claims database includes two tables: "Customers" and "Claims," which keep customers’ profiles and registered claims, respectively. Moreover, customers submit claims through a web-to-record form embedded into your website. Therefore, in your database, you need to relate submitted claims to a corresponding customer profile automatically using an email kept in the customer profile and an email specified in the submitted claim.

Firstly, you need to build a Many-to-Many relation between the "Customers" table and the "Claims" table.

In the next step, you add the match condition Email=Email to the relation settings and choose a details view.

As a result, claims are related to a customer by the email value. Also, you can see such claims in the details view displayed below a Customer record form.

Please notice that the match condition Multi-reference Contains Single-reference can be used only in Many-to-One relation. It does NOT apply to Many-to-Many relations.