Help

Re: Auto fill Multiple Select field from linked table

2006 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Robert_Kamarows
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

5 Replies 5
Lee_Mandell
6 - Interface Innovator
6 - Interface Innovator

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

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.

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
5 - Automation Enthusiast
5 - Automation Enthusiast

I would appreciate the details and screenshots.
Thank you.

Lee_Mandell
6 - Interface Innovator
6 - Interface Innovator

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.

Screen Shot 2021-02-18 at 9.34.08 AM

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