How to copy records from one table to another, preserving links

[Allan_Cady]

I’m an experienced RDBMS developer, having a little trouble adapting to the Airtable scripting paradigm.

Say I have two tables, Updates and Users. Updates has a field UserID, which links to the ID field in the Users table.

Now let’s say I have a second Updates table, let’s call it UpdatesFiltered. I would like to know, using the scripting app, how can I copy a subset of records from Updates to UpdatesFiltered, preserving the links to the Users table?

I’ve gotten as far as reading the Updates table and inspecting the fields in the records, but I’m not sure how I’m supposed to manage the linked UserID fields. If I understand correctly, it won’t do just to copy the UserID value from one table to the other (or will it?) Do I have to explicitly copy the record ID from one table to the other? I’m not even sure how I would be able to do that.

Thank you.

[Kamille_Parks]

First question: why have two tables for Updates instead of one table with two views?

Allan_Cady:

If I understand correctly, it won’t do just to copy the UserID value from one table to the other (or will it?)

Any table that links to a record in the Users table is going to establish the link based on the same unique record ID. In the script environment, assuming UserID is the primary field of the Users table and not the Airtable-generated unique record ID, you will have to adjust the returned value of what an Updates table record stores to copy it into another table via a script.

Linked fields store values as arrays of objects with two key-value pairs, but when writing to Linked fields they only accept arrays of objects with a single key value pair:

Read format:

[{id: 'airtable record id', name: 'value of the primary field'}, ...]

Write format:

[{id: 'airtable record id'}, ...]

[Allan_Cady]

Thanks for the prompt response. I think I understand so far.

So I pulled this from an example of using createRecordsAsync(), which doesn’t involve links. If I use my field names, the example might look like this if I were creating a new record from scratch:

let newRecordIds = await updatesFilteredTable.createRecordsAsync([
    {
        fields: {
            "User ID": "4435437",
            "Course ID": "3808135",
            "Progress %": 83
        },
    },
]);

Now I understand that this won’t copy the links to Users, and I would probably need an extra lookup step to get the Airtable record IDs. But let’s assume I’ve done that, and the IDs are stored in variables userRecordID and courseRecordID, and I’m going to store those into UpdatesFiltered. How then would I script the objects to pass to createRecordsAsync?

[Allan_Cady]

In response to your first question, why have two tables for Updates instead of one table with two views?

I will eventually have only one table, but I’m doing some one-time cleanup of the data. Eventually the second table will replace the first, after I’ve gotten it cleaned up the way I want it. But in the meantime I want to work on a second table so as not to mess up the original. :slightly_smiling_face:

[Kamille_Parks]

“4435437” isn’t a record ID and again, Linked record fields require an array of objects.

You would have to do "User ID": [{id: userRecordID}], assuming the variable is a single record ID and not an array. If its an array of record IDs then you would do "User ID": userRecordID.map(x => {return ({id: x})})

[Allan_Cady]

Thank you!

“User ID”: [{id: userRecordID}]

is what I was looking for. Tested and working.