Many to Many All Possible Combinations

[Dan_Coyle]

Hi everyone,

I’ve got a many-to-many relationship. I have a Clients table and a Plans table. Clients have many Plans, and each Plan could be assigned to many Clients.

I have a join table called Clients-Plans, so I can see all the possible combinations (for example, Client 1 + Plan 1, Client 1 + Plan 2, Client 2 + Plan 2, etc.). The problem is, my Clients-Plans table is not populated yet, and I can’t figure out a way to populate it automatically. Is manually the only way to do that?

Thanks in advance.

[Justin_Barrett]

This can be automated to some extent. First, though, take both your [Clients] and [Plans] tables and rename their primary fields to be exactly what you put in your sample. To keep your existing client names intact, insert a new field to the right of the primary field, copy the true client names in there, then change the primary to the new names. Here’s my dummy [Clients] table as an example:

If you have a small client list, making the dummy names should be fairly easy. For a large list, you can shortcut this step by adding an Autonumber field somewhere in the table, then change the primary field to a formula:

"Client " & Autonumber

After the primary field fills with names, change the type to Single Line Text. All the formulated names will be kept, and you can nix the Autonumber field. Repeat this process on your [Plans] table to make dummy plan names: Plan 1, Plan 2, etc.

Now moving to your [Clients-Plans] table, make three fields. The first will be an Autonumber field (with a matching name). The other two will be formula fields, and the formulas you choose will be driven by how many plans you have. In my example, I have five plans, but you’ll change each 5 in the following formulas based on your plan count.

First is a {Clients} formula field:

"Client " & ROUNDUP(Autonumber / 5, 0)

Next is a {Plans} formula field:

"Plan " & (MOD(Autonumber-1, 5) + 1)

What you end up with is this (note: all three of the following screenshots are truncated):

Now convert the two formula fields to links pointing to their respective tables, which gives you this:

Now go back to the [Clients] and [Plans] tables, copy the true names back into the primary fields, and nix the temp name fields, and your [Clients-Plans] table looks like this:

[Dan_Coyle]

Wow, Justin–this is amazing. Thanks for taking the time to respond. I can only work on this project on weekends, so I didn’t see it all week. I need to work through this, but it looks like it solves the problem. Thank you!

[Alex_Whitton]

Agree great explanation as always thanks Justin

My use case is combining Position Name vs Role Name, some 20k unique combinations.

• I setup as Justin suggested but I generated my names first in excel, and pasted into a ‘unique’ 3rd table
• Since this covered all my possible combinations (I allowed extra for new ‘positions’ or ‘roles’ which I did add in later) then them some, my dynamic link was retained allowing a few iterations for a database import

If this can be generated without needing to manually create the unique list, happy to receive

[Justin_Barrett]

Back when I wrote that solution, Airtable didn’t yet have scripting capabilities. Now that it does, this could be done fairly easily with the help of a script.