Skip to main content

updateRecordAsync in Multiple Select - Failed schema validation and field is readonly

  • December 1, 2021
  • 3 replies
  • 48 views
M

Hey everyone,

I am new to airtable scripting and try to update records in a multiple select field (Users) the same way I do in a single select field (Workstation).

It works in the singleselect but for the multipleselect I get the following error message:

"j: Error: Failed schema validation: must be an array at main on line 19"

As far as I can tell the root as well as my value are both arrays.

Also the multiple select field appears to be in readonly and I cant figure out why.

Here is my code. I really appreciate some help.

Thank in advance.

let table = base.getTable("Bookings - FoxOMS"); let query = await table.selectRecordsAsync({fields:["Location"]});

var myString = new String();
var myStringArray = new Array();

for (let record of query.records) {

myString = record.getCellValueAsString("Location");
myStringArray = myString.split(",");


if(!myStringArray[0].includes("Edit")){ 
    myStringArray.unshift("No Workstation selected");     
}

await table.updateRecordAsync(record.id, {“Workstation”:{name:myStringArray[0]}});
await table.updateRecordAsync(record.id, {“Users”:{name:myStringArray[1]}});
}

3 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • December 1, 2021

Hi @Martin_Sautter - as per the error, the value of the multi-select you want to update must be an array of objects (even if you only want to update to a single value). So your line 19 should be something like this:

await table.updateRecordAsync(record.id, {'Users': [{name: myStringArray[1]}] })

There’s a mix of square and curly brackets so for clarity, you want this:

[{name: myStringArray[1]}]

instead of this:

{name: myStringArray[1]}

There’s also one “gotcha” on a multi-select that you might want to work into your script. If the multi-select field already has a value, then the update above will simply overwrite it with a new value. If you want the final state of the field to be the current value plus the new value, then you need to do something like this:

let table = base.getTable('Table 1')
let record = await input.recordAsync('Pick a record', table);
if (record) {
    output.text(`You picked ${record.getCellValueAsString("Name")}`);
    let currentStatus = record.getCellValue('Status')
    let updated = await table.updateRecordAsync(record, {
        'Status': [...currentStatus, {name: 'Todo'}]
    })
}

(In my example Status is a multi-select field)

Here, I am getting the current value of the Status field:

let currentStatus = record.getCellValue('Status')

Then later on I use the spread operator (...) to merge the current value with the new value:

[...currentStatus, {name: 'Todo'}]

before updating the record

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • 4647 replies
  • December 2, 2021

Hi @Martin_Sautter - as per the error, the value of the multi-select you want to update must be an array of objects (even if you only want to update to a single value). So your line 19 should be something like this:

await table.updateRecordAsync(record.id, {'Users': [{name: myStringArray[1]}] })

There’s a mix of square and curly brackets so for clarity, you want this:

[{name: myStringArray[1]}]

instead of this:

{name: myStringArray[1]}

There’s also one “gotcha” on a multi-select that you might want to work into your script. If the multi-select field already has a value, then the update above will simply overwrite it with a new value. If you want the final state of the field to be the current value plus the new value, then you need to do something like this:

let table = base.getTable('Table 1')
let record = await input.recordAsync('Pick a record', table);
if (record) {
    output.text(`You picked ${record.getCellValueAsString("Name")}`);
    let currentStatus = record.getCellValue('Status')
    let updated = await table.updateRecordAsync(record, {
        'Status': [...currentStatus, {name: 'Todo'}]
    })
}

(In my example Status is a multi-select field)

Here, I am getting the current value of the Status field:

let currentStatus = record.getCellValue('Status')

Then later on I use the spread operator (...) to merge the current value with the new value:

[...currentStatus, {name: 'Todo'}]

before updating the record


This will need a small tweak. If there are no selections chosen in a multi-select field, Airtable will return null, not an empty array, and null will lead to an error when trying to use the spread operator. The modified version below will work around that by forcing currentStatus to be an empty array if null is returned from the field:

let currentStatus = record.getCellValue('Status') || []
JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • December 2, 2021

This will need a small tweak. If there are no selections chosen in a multi-select field, Airtable will return null, not an empty array, and null will lead to an error when trying to use the spread operator. The modified version below will work around that by forcing currentStatus to be an empty array if null is returned from the field:

let currentStatus = record.getCellValue('Status') || []

"If there are no selections chosen in a multi-select field, Airtable will return null"

Excellent point!


Terms & ConditionsAccessibility statement