Auto fill Multiple Select field from linked table

[Robert_Kamarows]

I have Table A, which has a field Containers. Containers can have multiple entries from linked table Container.

Table B has a field linked to Table A, and a field Used Containers that can have multiple entries from linked table Container.

When I’m in Table B, and I select an entry from Table A, I want to populate Used Containers with the values from Table A Containers field.

Is this possible?
Thank you.

[Lee_Mandell]

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

[Robert_Kamarows]

I didn’t expect this! :slightly_smiling_face:

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

Thank you Lee.

[Lee_Mandell]

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

[Robert_Kamarows]

I would appreciate the details and screenshots.
Thank you.

[Lee_Mandell]

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