Auto fill Multiple Select field from linked table

Hi,

I’m working on the same thing but it is crazy slow. Here is the testing code I’m using.

console.log(`Hello, ${base.name}!`);

// the table containing record links, and the linked-to table

let rootTable = base.getTable("Table 1");

let linkedTable = base.getTable("Table 2");

// the record with the links we want to summarize

// we need to create a 'recordId' input variable connected to a record trigger

let config = input.config();

let recordId = config.recordId;

// query the table and find our record according to its id:

let rootQuery = await rootTable.selectRecordsAsync();

console.log("rootQuery: " + JSON.stringify(rootQuery))

let record = rootQuery.getRecord(recordId);

console.log("record: " + JSON.stringify(record))



let table2Id = record.getCellValueAsString("Table 2");

console.log("Table 2 link: " + table2Id)

let linkedQuery = await linkedTable.selectRecordsAsync();

console.log("linkedQuery: " + JSON.stringify(linkedQuery))



let linkedRecords = linkedQuery.records.filter(linkedRecord => {return linkedRecord.getCellValueAsString("Id") === table2Id})

console.log("Table 2 link: " + JSON.stringify(linkedRecords))

let linkedStatus = linkedRecords[0].getCellValue("Status")["name"]

console.log("Table 2 status: " + JSON.stringify(linkedStatus))

let linkedStatusOption = linkedStatus["name"]

console.log("Table 2 status option: " + JSON.stringify(linkedStatus))



    await rootTable.updateRecordAsync(record, {

        // Change these names to fields in your base

        Status: {"name": linkedStatus},

     });

When I put this in my production database which only has ~7k records it takes over 15 seconds.

I hope this helps,

Lee

console.log(`Hello, ${base.name}!`);

// the table containing record links, and the linked-to table

let rootTable = base.getTable("Table 1");

let linkedTable = base.getTable("Table 2");

// the record with the links we want to summarize

// we need to create a 'recordId' input variable connected to a record trigger

let config = input.config();

let recordId = config.recordId;

// query the table and find our record according to its id:

let rootQuery = await rootTable.selectRecordsAsync();

console.log("rootQuery: " + JSON.stringify(rootQuery))

let record = rootQuery.getRecord(recordId);

console.log("record: " + JSON.stringify(record))



let table2Id = record.getCellValueAsString("Table 2");

console.log("Table 2 link: " + table2Id)

let linkedQuery = await linkedTable.selectRecordsAsync();

console.log("linkedQuery: " + JSON.stringify(linkedQuery))



let linkedRecords = linkedQuery.records.filter(linkedRecord => {return linkedRecord.getCellValueAsString("Id") === table2Id})

console.log("Table 2 link: " + JSON.stringify(linkedRecords))

let linkedStatus = linkedRecords[0].getCellValue("Status")["name"]

console.log("Table 2 status: " + JSON.stringify(linkedStatus))

let linkedStatusOption = linkedStatus["name"]

console.log("Table 2 status option: " + JSON.stringify(linkedStatus))



    await rootTable.updateRecordAsync(record, {

        // Change these names to fields in your base

        Status: {"name": linkedStatus},

     });

I didn’t expect this!

I’m new to AirTable. Where did you put this code? And there is no way to do this in Automation?

Thank you Lee.

Hi Robert,

You attach it to the action part of an automation. I have it triggered when the linking field changes. Let me know if you need more detail and I’ll put together some screenshots.

Lee

I would appreciate the details and screenshots.

Thank you.

Hi Robert,

Here is a screen shot of the set up in automations. The top part is triggering it when a field in a table is updated.

Here is my latest code that is working but not commented at all.

console.log(`Hello, ${base.name}!`);



const getSingleSelectValue = (record, field) => {

    return ""

}



// the table containing record links, and the linked-to table

let rootTable = base.getTable("Planting Orders");

let view = rootTable.getView("Today's Planting")

let cropRecipes = base.getTable("Crop Recipes");

// the record with the links we want to summarize

// we need to create a 'recordId' input variable connected to a record trigger

let config = input.config();

let recordId = config.recordId;

// query the table and find our record according to its id:

let rootQuery = await view.selectRecordsAsync();

// console.log("rootQuery: " + JSON.stringify(rootQuery))

let record = rootQuery.getRecord(recordId);

// console.log("record: " + JSON.stringify(record))



let table2Id = record.getCellValue("Crop")[0].name;

console.log("Crop Recipes link: " + table2Id)

let linkedQuery = await cropRecipes.selectRecordsAsync();

// console.log("linkedQuery: " + JSON.stringify(linkedQuery))



let linkedRecords = linkedQuery.records.filter(linkedRecord => {

    let linkedCode = linkedRecord.getCellValue("#Code")["text"]

    console.log("Linked Code: " + JSON.stringify(linkedCode))

    return linkedRecord.getCellValue("#Code")["text"] === table2Id}

    )

console.log("Table 2 link: " + JSON.stringify(linkedRecords))

let flatType = linkedRecords[0].getCellValue("Flat Type")["name"]

console.log("Flat Type: " + flatType)

let substrate = linkedRecords[0].getCellValue("Media")["name"]

// let germiantion = linkedRecords[0].getCellValue("Germination")["name"]

let topSoil = getSingleSelectValue(linkedRecords[0], "Top Soil")

console.log("Topsoil: " + topSoil)

// let topSoil = linkedRecords[0].getCellValue("Top Soil")

// let h2o2Spray = linkedRecords[0].getCellValue("H2O2 Spray")

// let h2o2Spray = true

// console.log("Table 2 status: " + JSON.stringify(flatType))

let linkedStatusOption = flatType["name"]

// console.log("Table 2 status option: " + JSON.stringify(flatType))



    await rootTable.updateRecordAsync(record, {

        // Change these names to fields in your base

        // "Flat Type": {"name": "NFT"},

        // "Substrate": {"name": substrate},

        // "Germination Location": {name: germiantion},

        //  "Top Soil": {"name": topSoil},

        "Substrate": linkedRecords[0].getCellValue("Media")["name"],

        "Place of Germination": linkedRecords[0].getCellValue("Place of Germination")["name"],

        "Flat Type":  linkedRecords[0].getCellValue("Flat Type")["name"],

     });

I hope this is clear enough.

Lee