Linking records from database ID's

[jheddings]

I’m attempting to migrate to Airtable from several custom SQL databases… The existing tables have several foreign keys that I am attempting to recreate in Airtable, but I cannot figure out how to update a record properly.

In the example below, I’m trying to re-link the Cities to the references from States. There is an existing DBID column holding the original database foreign key. Finding the reference State is easy enough (though inefficient at this point), but I cannot figure out how to update the record properly at the end of the loop. Any ideas?

let active_table = base.getTable("Cities");
let ref_table = base.getTable("States");

let query = await active_table.selectRecordsAsync();

for (let record of query.records) {
    let ref_id = record.getCellValue("StateRef");
    output.markdown(record.getCellValue("Name"));

    let ref_query = await ref_table.selectRecordsAsync();
    let ref_record = undefined;
    for (let find_ref of ref_query.records) {
        if (find_ref.getCellValue("DBID") == ref_id) {
            ref_record = find_ref;
            output.markdown("-> " + find_ref.getCellValue("Name"));
            break;
        }
    }
    
    active_table.updateRecordAsync(record, {
        State: ref_record
    });
}

[Jeremy_Oglesby]

Hi @jheddings,

I think you have to pass an array of records to a Linked record field in an updateRecordAsync() call, even if you are only adding a single record link.

active_table.updateRecordsAsync(record, {
    "State": [{id: ref_record.id}] 
});

[jheddings]

That did it!! Thank you for the quick reply. It seemed easy enough, but I was missing the array declaration. I’m off and running again…

[Bill_French]

jheddings:

It seemed easy enough, but I was missing the array declaration.

Yep, and you might also find this discussion relevant.