Join tables for standard one-row-per-linked-entry output

[Aardvark]

This may be the wrong category, but I need to join two tables for output of our data. I have two linked tables:

Person and Classes

The person table looks like:

Name  |  Classes
----------------
John  | A,B,C,F
Sally | B,F
Max   | B,C

While the linked classes table looks like:

Class | Date | People
---------------------------
A     | 1975 | John
B     | 2000 | John,Sally,Max
C     | 1823 | John,Max
D     | 1492 |
E     | 2020 |
F     | 2010 | John,Sally

What I need is:

Person|Class|Date
--------------
John  | A   | 1975
John  | B   | 2000
John  | C   | 1823
John  | F   | 2010
Sally | B   | 2000
Sally | F   | 2010
Max   | B   | 2000
Max   | C   | 1823

How can I achieve this in Airtable?

[Kamille_Parks]

Don’t link People to Classes directly, instead add a new table using the structure you show in your “What (you) need” example, with Person linking to your People table, Class linking to your Classes table.

Then in your People table turn your {Classes} field into a Lookup field (and do the same in your Classes table with your {People} field)

[Aardvark]

Thanks for the help, but I’m a little confused:

1. Assuming I already have the structure above with thousands of records (I do), is there a way to easily convert to the structure you suggest?

2. How would this work when inputting data? The convenience of the structure I have is when I’m making a record with 100’s or people in a class, I go to the Classes table, enter in the title once, and then got to the linked Persons column and rapidly select the persons from the list.

In the scenario you suggest, I’ll have to create a new row manually for each Person-Class pairing in the new table? (Basically build the monster “join” table, rather than use the convenience of a database to build it for me?)

[Kamille_Parks]

1. IF you have thousands of existing records the easiest thing to do would be to either use a script, or take the process in stages.
2. While yes, every Person-Class pair would be a row, if you group records by Class in that table you only have to enter a Person for each new row, as Class would be filled in automatically.

To transfer data manually: You could filter the People table by {Classes} contains "a class name", copy all those students listed for one class and paste them in your new Join table. Paste the class name in all those records too. Repeat

To transfer data faster: You could do this with a fairly simple script using the Scripting Block which bulk creates your Join table records.