Skip to main content

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?

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)

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)


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?)

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?)




  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.

Reply


Terms & Conditions