Many-to-Many Relations

This type of relationship involves multiple connections of records in the first table with records in the second table.

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.

Many-to-Many Relation

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.

Calculating Monthly Payments

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.

Many-to-Many Relation

Customers and Claims Tables

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

Many-to-Many Relation Options

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.

Claims Details View

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.