Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 26, 2019 01:58 PM
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.
May 27, 2019 03:39 PM
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:
Jun 01, 2019 02:57 PM
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!
Sep 27, 2022 02:31 AM
Agree great explanation as always thanks Justin
My use case is combining Position Name vs Role Name, some 20k unique combinations.
If this can be generated without needing to manually create the unique list, happy to receive
Sep 27, 2022 07:15 AM
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.