Update Record Script is not working anymore?

Is there a particular reason you are creating a new record and then immediately updating the record, instead of passing the field values when the record is first created?

What is the obj variable and why doesn’t it already include the values for your ID, Discipline, ... fields?

Here is the full script! It basically takes one record from one table, and just copies some of the fields over. Then the other fields are filled in with ‘new’ info.

let activity = base.getTable('Create Activity');
let main = base.getTable('Main');

let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
    // Get total num of objects in the three fields
    let content = record.getCellValue('Content')
    let numContent = content.length
    let response = record.getCellValue('Response')
    let numResponse = response.length

    //let totalNewRecords = numContent * numResponse * numReflection

    //console.log(totalNewRecords)

    // New template record made
    let fields = activity.fields
    // Doesn't include these fields since in Main they are either different field types or tags which have different ids
    let exclude = ["ID", "Discipline", "Experience", "Duration", "Activity Type", "Content", "Response"] 
    let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

    let obj = {}
    filteredFields.map(x => {
        // @ts-ignore
        Object.assign(obj, {[x.name]: record.getCellValue(x.name)})
    })

    let IDCount = 0
    //loop through the content, response, and reflection and make new records for each
    for (let cn = 0; cn < numContent; cn++) 
    {
        let contentText = content[cn].name

        for (let rs = 0; rs < numResponse; rs++) 
        {
            let responseText = response[rs].name
            let newCombinedText = contentText + "\n\n" + responseText

            IDCount++
            let newID = record.getCellValue("ID") + "." + IDCount

            //Queries
            let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });

            //Filter to find product
            let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)[0];

            //If a match is found
            if(IDMatch !== undefined)
            {
                throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
            } else
            {
                let newRecord = await main.createRecordAsync(obj)
                main.updateRecordAsync(newRecord, {
                    "ID" : newID,
                    "Discipline" : {name: record.getCellValueAsString("Discipline")},
                    "Experience" : {name: record.getCellValueAsString("Experience")},
                    "Duration" : {name: record.getCellValueAsString("Duration")},
                    "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
                    "Content": newCombinedText
                })
            }
        }
    }
}

Does the script fail to run, or does it run but not produce the expected results?

If the script fails to run, it should give you a line number and some other indication of the error.

I also suggest you liberally sprinkle console.log() throughout your script to see what the values are for each variable as the script is running.

It runs but the update sync part does not work. All those are blank. Two weeks ago they filled in properly.

You’re not await'ing the update call.

Again, I would recommend passing all the fields in one go. Its inefficient to pass some values, then pass more immediately afterward.

let newRecord = await main.createRecordAsync({
   ...obj,
   "ID" : newID,
   "Discipline" : {name: record.getCellValueAsString("Discipline")},
   "Experience" : {name: record.getCellValueAsString("Experience")},
   "Duration" : {name: record.getCellValueAsString("Duration")},
   "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
   "Content": newCombinedText
})

Also, your code could be more efficient still by not excluding "Discipline", "Experience", "Duration", "Activity Type" from the fields to copy over since those values don’t appear to change.

let newRecord = await main.createRecordAsync({
   ...obj,
   "ID" : newID,
   "Discipline" : {name: record.getCellValueAsString("Discipline")},
   "Experience" : {name: record.getCellValueAsString("Experience")},
   "Duration" : {name: record.getCellValueAsString("Duration")},
   "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
   "Content": newCombinedText
})

I had to do those fields that don’t change since they were multiple selection. Found that out in another thread. It was throwing an error that they didn’t match.

Alright so I tried your code and it threw this error:

j: Can’t create records: invalid cell value for field ‘Discipline’.
Could not find a choice with that ID or name at main on line 67*

Does that mean the original field was a multiselect, the new field is a multiselect, or both?

If the field you’re copying into is a multiselect as opposed to a single select, then you should be feeding it an array not a single object.

What value are you trying to pass to “Discipline” and is there in fact a matching select option for the destination field?

i can’t seem to get the formatting right for an array. Can you show how to do that?

Yeah the fields have the exact same options, but maybe it’s like not the same ID for each option?

No option from Field A can have the same ID as an option from Field B. You’re passing the name so that doesn’t matter.

The format for the array is what you have already, wrapped in brackets:

[{name: record.getCellValueAsString("Discipline")}]

[{name: record.getCellValueAsString("Discipline")}]

Yay it worked making it an array! Not sure why it working fine two weeks ago haha.

Though what do I do when the field it’s copying from is blank? Some of them are blank, but I want that to be a ok. When the original field is blank, I get this error:

Can’t create records: invalid cell value for field ‘Specialization’.
Could not find a choice with that ID or name

Hi,

feel like the problem is that you are trying to populate Select or Multiselect field with value doesn’t existing in “possible values”. Maybe empty value not allowed in your case.

in that case you should filter cellvalue (or update field options)
sorry, can’t remember proper single update format and doesn’t fully understand ‘create/then update’ logic
(after create)

let arr=[“Discipline”,“Experience”,“Durion”,“Activity Type”].map(x=>
[x,record.getCellValue(x)]).filter(a=>a[1]);
arr.push([“ID”,newID],[“Content”,newCombinedText]);
await main.updateRecordAsync(newRecord,Object.fromEntries(arr));

I’m still very confused. The script doesn’t work every time… I have no idea what I’m doing wrong and it’s really halting progress :frowning:

I have two identical tables. The Specialization field has the EXACT same inputs as the other table. However, every once and a while one of the record creations just won’t work when I run the app

Can’t create records: invalid cell value for field ‘Specialization’.
Could not find a choice with that ID or name.

Like there is NOTHING different. The record right above it has the same selection in the field, but this particular one is invalid? Even though it’s exactly the same haha.

These are the two fields:

Like I said, exactly the same

Current script:

let activity = base.getTable('Create Activity');
let main = base.getTable('Main');

let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
    // Get total num of objects in the three fields
    let content = record.getCellValue('Content')
    let numContent = content.length
    let response = record.getCellValue('Response')
    let numResponse = response.length

    //let totalNewRecords = numContent * numResponse * numReflection

    //console.log(totalNewRecords)

    // New template record made
    let fields = activity.fields
    // Doesn't include these fields since in Main they are either different field types or tags which have different ids
    let exclude = ["ID", "Discipline", "Specialization", "Experience", "Duration", "Activity Type", "Content", "Response"] 
    let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

    let obj = {}
    filteredFields.map(x => {
        // @ts-ignore
        Object.assign(obj, {[x.name]: record.getCellValue(x.name)})
    })

    let IDCount = 0
    //loop through the content, response, and reflection and make new records for each
    for (let cn = 0; cn < numContent; cn++) 
    {
        let contentText = content[cn].name

        for (let rs = 0; rs < numResponse; rs++) 
        {
            let responseText = response[rs].name
            let newCombinedText = contentText + "\n\n" + responseText

            IDCount++
            let newID = record.getCellValue("ID") + "." + IDCount

            //Queries
            let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });

            //Filter to find product
            let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)[0];

            //If a match is found
            if(IDMatch !== undefined)
            {
                throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
            } else
            {
                let newRecord = await main.createRecordAsync({
                ...obj,
                "ID" : newID,
                "Discipline" : {name: record.getCellValueAsString("Discipline")},
                "Specialization" : [{name: record.getCellValueAsString("Specialization")}], 
                "Experience" : {name: record.getCellValueAsString("Experience")},
                "Duration" : {name: record.getCellValueAsString("Duration")},
                "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
                "Content": newCombinedText
                })
            }
        }
    }
}

let newRecord = await main.createRecordAsync({
   ...obj,
   "ID" : newID,
   "Discipline" : {name: record.getCellValueAsString("Discipline")},
   "Experience" : {name: record.getCellValueAsString("Experience")},
   "Duration" : {name: record.getCellValueAsString("Duration")},
   "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
   "Content": newCombinedText
})

I had to do it this way because copying it over directly gave me a similar error to what I’m getting now. Where the ID’s don’t match.

Just for future reference - while your tables might be identical, record IDs never are. Assuming this is the built-in record.id property being referenced.

let activity = base.getTable('Create Activity');
let main = base.getTable('Main');

let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
    // Get total num of objects in the three fields
    let content = record.getCellValue('Content')
    let numContent = content.length
    let response = record.getCellValue('Response')
    let numResponse = response.length

    //let totalNewRecords = numContent * numResponse * numReflection

    //console.log(totalNewRecords)

    // New template record made
    let fields = activity.fields
    // Doesn't include these fields since in Main they are either different field types or tags which have different ids
    let exclude = ["ID", "Discipline", "Specialization", "Experience", "Duration", "Activity Type", "Content", "Response"] 
    let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

    let obj = {}
    filteredFields.map(x => {
        // @ts-ignore
        Object.assign(obj, {[x.name]: record.getCellValue(x.name)})
    })

    let IDCount = 0
    //loop through the content, response, and reflection and make new records for each
    for (let cn = 0; cn < numContent; cn++) 
    {
        let contentText = content[cn].name

        for (let rs = 0; rs < numResponse; rs++) 
        {
            let responseText = response[rs].name
            let newCombinedText = contentText + "\n\n" + responseText

            IDCount++
            let newID = record.getCellValue("ID") + "." + IDCount

            //Queries
            let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });

            //Filter to find product
            let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)[0];

            //If a match is found
            if(IDMatch !== undefined)
            {
                throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
            } else
            {
                let newRecord = await main.createRecordAsync({
                ...obj,
                "ID" : newID,
                "Discipline" : {name: record.getCellValueAsString("Discipline")},
                "Specialization" : [{name: record.getCellValueAsString("Specialization")}], 
                "Experience" : {name: record.getCellValueAsString("Experience")},
                "Duration" : {name: record.getCellValueAsString("Duration")},
                "Activity Type" : {name: record.getCellValueAsString("Activity Type")},
                "Content": newCombinedText
                })
            }
        }
    }
}

you can just put output.inspect(record.getCellValueAsString(“Experience”)) before ‘let newrecord…’ to avoid guessing.
but here is another problem. I just discovered that you are trying to use ‘createRecordAsync’ (and ‘updateRecordAsync’ before) in a loop, that’s wrong if you have many loop cycles

• if you consider updating muptiple records, you should use updateRecordsAsync , not updateRecordAsync , otherwise script stops after 15-20 update actions in a loop. updateRecordsAsync limited by size (max 50 records).

Ok so did that and it outputted the correct strings. So there definitely is something there!

That’s interesting. I had it work with one where it made 75 records in one go. No problem with it.

Is there a good way to copy over filed inputs then? If they never match, then there is no full proof way to copy over data from one table to another?

Which again, is so odd, since this script worked 2 weeks ago no problem. I used it like 40+ times. No edits made to it. I’m either really dumb or something fishy is goin lol

I suppose it depends on some (undocumented?) limit calls/sec
in June, when i tried to create ‘bulk deduper’, for tables with hundreds of duplicates, and just started with JS and airtables, I put ‘createRecordAsync’ in a >500 loop and it stopped after 20-30 creations, without any error. So one day may be lucky, another - no.

JS is a “friendly” language, it lets you run even if your code is not 100% correct and tries to convert if you mess with data types and else, But such 'sometimes OK, sometimes not" behaviour is harder to debug. It’s good from one side, but bad from another.

btw, it’s also not OK to put async functions like “let mainRecordsCheck = await main.selectRecordsAsync” in a loop, when it should run once.

usual airtable script is: [get input data] => [process data] => {write data]
selectRecordsAsync usually performed in 1st step, and createRecordsAsync - in last step
[processing data] usually prepare some ‘array of new records’ or ‘array of updates’

Ok so I can definitely move that createRecords out of the loop, but I’m creating multiple records per one record in this script. So idk how else to do it than put it in a loop?

The most efficient way to create/update Airtable records is in chunks of 50 or however many you need below that figure.

Build the records to create/update as per usual but instead of doing the createRecordAsync, push them to a separate array declared inside the same scope.

After you’re done composing the batch, the optimal syntax is as follows:

//assuming your queue is called Cache, and you just finished building it out:
Cache.push(lastRecordToHandle);

while(Cache.length)
    await base.getTable(YourTableName)
    // use whichever one you need here
   //.createRecordsAsync(
    .updateRecordsAsync(
        Cache.splice(0,50));

You should use loop to created some array, or object and then pass it to updaterecordS function in a proper way (use output.inspect(array) to debug. it’s hard at beginning, but then much easier to code at all.
i prefer using ‘array helpers’ instead of for… loops (but sometimes for… is no choise option), and own functions
you may see example in ‘Scripting a Rollup Substitute’ topic

. it’s hard at beginning, but then much easier to code at all.

Ain’t that the truth. :slightly_smiling_face: