Help

Linking Records Causes Field to be Deleted in All Records

Topic Labels: Base design
Solved
Jump to Solution
792 2
cancel
Showing results for 
Search instead for 
Did you mean: 
darrrelltaylor3
4 - Data Explorer
4 - Data Explorer

This is my first post to the Airtable community.  I have searched through lots of posts about linking records and tables, but I can't seem to find an answer to this...

I have two imported tables that have a StudentID field in each.  When I edit the StudentID field in one Table to change the field type to "link to another record", Airtable deletes all the StudentIDs in the table I am working in.  I am attempting to create a linkage between these tables because I want to know if a student in Table 1 is also in Table 2 (and the student may be in Table 2 multiple times, however I just want to know if the student is in the table...I don't care how many times).   Can someone help me figure out what I am missing?  Many thanks, in advance, for your assistance with this issue!

My apologies if this problem has already been solved elsewhere, I just couldn't find it.

1 Solution

Accepted Solutions
Alexey_Gusev
13 - Mars
13 - Mars

Hi
Usually, I duplicate and turn the field for such goal, to avoid possible data loss. You can do almost the same, by just creating linked field on the right of StudentID and then copy-paste whole field. ( but I guess both ways will not work in your case )
Anyway, when you paste a column of values (or turn column with values to a linked field), each of them seeking match in a primary field of Table 2 and links to the first it find (order not guaranteed). If nothing found, it creates new empty record with value in primary field and links to it.
So, in usual state, IDs shouldn't dissapear, all must be linked, and in Table 2 you should see a group of new records (means IDs of Table 1 that are absent in Table 2). And some unlinked records in Table 2, union of 'absent in Table 1' and 'has duplicate in Table 2, which already used for linking (more lucky)'

BUT, in your case, Table 2 has formula or synced primary field, that can't be overwritten directly, that's why new records can't be auto-created. And therefore, primary field of Table 2 is not a column of StudentIDs (just guessing). That's why they can't be linked.

Linking two tables by non-primary ID is not so hard. Can be done by script extension, or, if you can't code, by simple automation with 'when chkbox is ✔'  -  'find' - 'update record (linked field)', or, to avoid wasting automation runs, manually via third temporary table.
Almost forget - you can temporary switch primary field to use necessary data to link, and recently Airtable  added it (switch primary field) as function in right-click menu, so it's maybe easier than 3rd table.

In my opinion, the best way is to use IDs according to their purpose, I mean as a primary field. 
I hope you will be able to accomplish your goal and get familiar how linking works. 

See Solution in Thread

2 Replies 2
Alexey_Gusev
13 - Mars
13 - Mars

Hi
Usually, I duplicate and turn the field for such goal, to avoid possible data loss. You can do almost the same, by just creating linked field on the right of StudentID and then copy-paste whole field. ( but I guess both ways will not work in your case )
Anyway, when you paste a column of values (or turn column with values to a linked field), each of them seeking match in a primary field of Table 2 and links to the first it find (order not guaranteed). If nothing found, it creates new empty record with value in primary field and links to it.
So, in usual state, IDs shouldn't dissapear, all must be linked, and in Table 2 you should see a group of new records (means IDs of Table 1 that are absent in Table 2). And some unlinked records in Table 2, union of 'absent in Table 1' and 'has duplicate in Table 2, which already used for linking (more lucky)'

BUT, in your case, Table 2 has formula or synced primary field, that can't be overwritten directly, that's why new records can't be auto-created. And therefore, primary field of Table 2 is not a column of StudentIDs (just guessing). That's why they can't be linked.

Linking two tables by non-primary ID is not so hard. Can be done by script extension, or, if you can't code, by simple automation with 'when chkbox is ✔'  -  'find' - 'update record (linked field)', or, to avoid wasting automation runs, manually via third temporary table.
Almost forget - you can temporary switch primary field to use necessary data to link, and recently Airtable  added it (switch primary field) as function in right-click menu, so it's maybe easier than 3rd table.

In my opinion, the best way is to use IDs according to their purpose, I mean as a primary field. 
I hope you will be able to accomplish your goal and get familiar how linking works. 

darrrelltaylor3
4 - Data Explorer
4 - Data Explorer

@Alexey_Gusev,

Thank you so much for your reply.  When I converted my StudentID field to the PrimaryField, I was able to make it work.  After I tried it out, I knew there had to be documentation on this, and it was much easier to find this time.   https://support.airtable.com/docs/converting-existing-fields-to-linked-records

Again, thanks for the help!!