Many to Many All Possible Combinations

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.

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:

37%20PM

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.

27%20PM

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:

06%20PM

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:

41%20PM

1 Like

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!