Help

Re: Update Record Script is not working anymore?

2735 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Austin_Drozin
5 - Automation Enthusiast
5 - Automation Enthusiast

I have this script I made a month ago. It worked perfectly fine. Just tried it today and it doesn’t work. The fields that I’m updating with updateRecordAsync do not update. They are just blank.

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
                })

Like I said, this script did exactly as I wanted to in the past. Did something change with updateRecordAsync?

23 Replies 23

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.

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")}]

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));

Austin_Drozin
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
image
image

Like I said, exactly the same

Austin_Drozin
5 - Automation Enthusiast
5 - Automation Enthusiast

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
                })
            }
        }
    }
}

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.

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’