Splitting a table in two


#1

Hello everybody!

I’ve been using Airtable to manage an iPad inventory in an 1:1 environment at my school and I’m quite happy with the result. Awesome product.

The thing is that, up until now, I have been storing iPad and student data in just one of our tables.

Device iD - Device data - Student ID - Student data

But this year some iPads are gong to change hands. Of course, I could just the records by hand, but it’s clear that it would be more elegant having two different tables and linking them.

Table 1
Device ID - Device data - Student ID

Table 2
Student ID - Student data

My question is: Is there a simple way of doing it? I’m afraid I will have to create the two tables and link every record, one by one.

Thanks in advance!


#2

I would copy and paste your entire table into excel, split it into two sheets(keeping the ID column on at least one of the sheets) then importing the two separate sheets. That’s probably the most painless. All students will keep their appropriate device ID link, and it will be split appropriately.

Let me know if you need further explanation!


#3

Actually, Airtable has a convention that could make this very easy.

First, create your new table “Devices”. Then, change the “Device ID” field in your students table to be a “Link to Another Record” type. The ID’s will automatically create new records in the “Devices” table for each unique “Device ID” found in the “Students” table, and convert them to links in the “Students” table.

Then, assuming you have only 1 instance of each “Device ID” in the student’s table (which you seem in indicate), you could simply copy/paste the “Device Data” from the “Students” table into the “Devices” table, since the new “Devices” records will be in the order they were found in the “Students” table. (WARNING: this will not work if there is more than one instance of any given device in the “Students” table).

Here’s a quick example:


#4

Yeah, I totally forgot you could just copy and paste it into the new table. Same concept haha


#5

A junction table would be great for historical data (whats the poing of separating Devices and Students if not?): https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships#junction


#6

Oh, my god.

Thanks a lot to the three of you.

Believe or not, I worked at the Datawarehouse of a big company for 7 years. But I think I was still looking at Airtable as a kind of “online Excel workbook” instead of a relational database, even after deciding I had to split the table in two.

Now I have to plan how to do it… devices! students! classrooms! teachers! year! Keeping track of how everything was at a point and how it is now…

Thanks again. A new world has opened before me :slight_smile:

By the way, while we are at it: besides filters, is there any way to create “views”?


#7

Yes – that’s a main feature of Airtable. A view is a “set” of filters, groupings, sortings, and field orderings/hidings that you can name and access when needed.

You can create those via the “View” menu in the upper left corner, just under the table tabs:


#8

Thanks again :slight_smile:

It seems I can’t find the way to create a view that shows fields from different tables.

If I have

Table 1
Device Id Specs

Table 2
Device Id - Year - Student Id

Table 3
Student Id - Student Name

And I want to show:

Device Id - Specs - Student Name

How would I go about that?

Thank you for your patience and please forgive any mistake I may make, English is not my first language.


#9

In Table 2 (your “join” table, which “joins” a student with a device for a particular year), you can create a field that is a “Lookup” - have it lookup against Table 1, and find the “Specs” field.

Then you will be able to create the View you are wanting in Table 2.

You can always use “Lookups” and “Rollups” to move data around from one table to another as long as the two tables have a link somehow (via linked records).