Help

Replacing all null values in Single Select type field

Topic Labels: Custom Extensions
1613 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Niv_Grinberg
4 - Data Explorer
4 - Data Explorer

Hello,
I have a table called “Prospects” in which there’s a single select field called “Status”.
All the values in this table are filled from a connected form, but I am trying to insert a default value for “Status”.
Regular “default value” functionality doesn’t work on single select, and even when I change to text/number - when the record is inserted via a form - the default values don’t really apply.
I have trying working around it by rewriting the “find and replace” script - find all null values in “Status” field and change to “1”.

let table = base.getTable("Prospects");
let field = table.getField("Status");

// Load all of the records in the table
let result = await table.selectRecordsAsync();

// Find every record we need to update
let replacements = [];
for (let record of result.records) {
    let originalValue = record.getCellValue(field);
    console.log(originalValue)
    let newValue = 1 //originalValue.replace(findText, replaceText);
    if (originalValue !== newValue) {
        replacements.push({
            record,
            before: originalValue,
            after: newValue,
        });
    }
}

output.table(replacements);
// Update the records
let updates = replacements.map(replacement => ({
    id: replacement.record.id,
    fields: {
        [field.id]: replacement.after,
    }
}));

// Only up to 50 updates are allowed at one time, so do it in batches
while (updates.length > 0) {
    console.log(table, updates)
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

And I’m getting this error message:

N: Can't set cell values: invalid cell value for field 'Status'.
Cell value has invalid format: <root> must be an object
Single select field value must be an object with at least one of 'id' or 'name' as a property.
    at main on line 34

I’d appreciate any help - and is it possible to do an automation to run this script whenever a new record is inserted either by hand or by form? so the null values would never actually be there.

Thank you in advance!

1 Reply 1

I’m hoping you figured it out by now but just in case someone stumbles upon this thread in the future: welcome - both to Airtable and JavaScript!

The latter can throw exotic errors, but this ain’t it, and it’s good practice to never really doubt what the JS engine tells you, assuming it managed to log something specific. This is very specific, mind you, it literally says you’re missing an object. This would literally mean " the curly braces around your input", in this instance, and is .

That’s the “object” it complains about your parameters not being. But once you successfully convert that input to a different type (again, welcome to JS, we have a lot of this confusion), the code will 100% throw an exception, informing you that ypir object.slice - is not a function. Then y

Was the editor not throwing errors on your end? Underlining the writing red and all? Or have you been editing it while it was running in full-screen mode,perhaps? There should have been a few “nuh-uh” signals sent in your general direction before you so much as touched the code.

For example, another thing you misunderstood is the way slicing works. I’m also pretty sure you confused slicing with splicing here and that the script might run indefinitely because slice doesn’t actually ever change the value of the updates array (hence my full-screen question), but I’m confusing myself right now lol, can’t imagine what you’re thinking.

As a rule of thumb, on top of never doubting the debugger, always start debugging from the examples part of the scripting app, it adjusts its references to account for your own data in a given base and you could literally force it to spit out the exact code you need to at least initiate every type of action you can perform in every type of field you and table you have access to.

If you’re adamant to continue powering through like this, that is the only way for you not to lose your mind trying to read an elaborate technical specification written by someone with at least a decade of JS coding experience under their belt.

That means going from “I need to update single-select field cells with a script” line of thinking to:

this

image

then this

image

and finally this

image

Leaving you with this

image

Which might be enough for you to notice those pretty curly braces, so prominently disjointed from your content. The thing is, that’s no way to learn JavaScript syntax, which is a topic you could fully master in a matter of days; simply follow the very links from those screenshots above. Mozilla should have you covered for a good long while, knowledge-wise.

While I’m sure this might seem like a lot right now, give it a few days and it will be trivial, assuming you stick with it. No one was born with knowledge of object notation, least of all one as picky as JSON haha.
And you could be "speaking " 50 computer languages by the end of the year; spoiler alert: they’re all English, more or less,share about 25-30 words between themselves and that’s it, piece of cake,. :slightly_smiling_face:

One final thing you misunderstood was what will happen after your “slice” is over.

I’m assuming the log had between 50 and 100 records to update, right? As you don’t need that second line - plenty of other errors for the interpreter to throw before that, anyway, haha. Here, you’ve earned my very best morale boost for newcomers to JavaScript on your first post. Read it, laugh it off, try again tomorrow, see how many mistakes you’re suddengly avoiding, rinse and repeat.