clearing fields with scripting

[Mark_Newton]

Hi all,

This seems pretty basic but I can't get it to work after a day of researching and patching together example code I've found. I have no Javascript experience.

I simply want to run a script that entirely clears the contents of a column. Here's what I have:

let subsTable = base.getTable('Sub Assemblies');

let clearQuery = await subsTable.selectRecordsAsync({
    fields: []
});

function myObj(rec){return {'id':rec.id,'fields':{'BOM CREATE':""}}};   
let updates=clearQuery.records.map(myObj);
console.log('updates: ',updates[0].fields)
  while (updates.length > 0) {
    await subsTable.updateRecordsAsync(updates.slice(0, 50));
            updates = updates.slice(50); }

The error I'm getting is:

j: Error: Failed schema validation: <root> must be an array

line: 2nd from last

Any help appreciated!

[Ben_Young1 · Accepted solution]

Hey @Mark_Newton! 

To demonstrate this, I've created a table with a single text field and pasted in the record ids of each record to provide.

Next, I just created an automation that would theoretically run on a schedule to serve as just a sample trigger.
You can use whatever trigger fits your business needs.

Next, I just have a single script action that will do our heavy lifting.

const tableId = "tbleq4AFp1rZ7Pu1u";
const table = base.getTable(tableId);

//Load all records from the table
//Returns an array of record objects.
const allRecords = await table.selectRecordsAsync({
  fields: [
    //Field To Clear
    "fld7bln5wXXpy0yYv"
  ]
}).then(records => {return records.records});

let updatedRecords = allRecords.map(record => {
  return {
    id: record.id,
    fields: {
      "fld7bln5wXXpy0yYv": ""
    }
  }
});

if (updatedRecords) {
  if (updatedRecords.length <= 50) {
    await table.updateRecordsAsync(updatedRecords)
    .then(updatedRecords => {
      console.log(`Updated ${updatedRecords.length} Records.`)
    })
  } else if (updatedRecords.length > 50) {
    while (updatedRecords.length > 50 || updatedRecords.length > 0) {
      await table.updateRecordsAsync(updatedRecords.slice(0, 50));
      updatedRecords = updatedRecords.slice(50);
    }
    console.log(`Updated ${allRecords.length} Records.`);
  }
}

The script grabs every record in the table and sets a blank value to the field we specify.
We're not able to conduct bulk operations on more than 50 record at a single time, so there is logic written in to make sure that in situations where there are more than 50 records, we work through separate batches until every record has been updated.

When I test the script, it behaves as expected. The field on each record is now empty.

We can confirm this against the record revision history on each record.

There are some key components that you'll want to change out for this script if you want to use it.
You'll need to change out the table id at the top of the script to reflect the id of your table.

If you are working in Airtable desktop application, navigate to your desired table, and use CTRL + Shift + C / CMD + Shift + C to copy the link to the table you're currently working in.

When you examine the link, it will look something like this:

https://airtable.com/appxxxxxxxxxxxxxx/tblxxxxxxxxxxxxxx/viwxxxxxxxxxxxxxx

You can just copy out the table id from the URL and directly into the script. Be sure to confirm that you're working with the correct table ID.

The next thing you'll want to switch out from the script I posted is the field ID string as shown below:

You'll need to replace this id with the field id of the field that you're looking to clear.
You can quickly grab the field ID of any field in a table by navigating to the Manage Fields page within your base's Tools.

There's one last thing that is important to note.
This script will only work in it's current form if you are clearing one of the following field types:

• Single Line Text
• Long Text
• Email
• URL
• Number
• Percent
• Currency
• Date/Time
• Phone Number
• Checkbox

If you want to clear the values of another field type not listed, let me know and I can post an updated script for you to look at.

If you have any additional questions, curiosities, or have specific requirements, let us know and we'd all be happy to jump in and help!
I'm also happy to explain any part of the script that you're curious to know more about.

[plyske]

Just curious here: What is the purpose of this when the data doesn't get deleted entirely? 

[Mark_Newton]

@Ben_Young1 that's wonderful, and so well documented. Thank you so much!

[Mark_Newton]

For general interest, I got my version of the code to work, posted in my original comment. I simply changed the "" (blank string) on line 7 (where the function is declared) to null.

[Carlos-Werq]

Hi, @Ben_Young1  - 

Much like Mark, I've been trying to figure out how to get this done with no luck. I read through your excellent step-by-step, but Alas, the fields I am trying to clear are Linked Record fields. Could you possibly post a version of your code that would remove the linked records? Feel free to send me a DM if that's easier.

Thank you in advance for your help! 🙂 

[Ben_Young1]

Hey @Carlos-Werq!

Here's a version of the snippet that will clear linked record fields:

const tableId = "your_table_id";
const table = base.getTable(tableId);

//Load all records from the table
//Returns an array of record objects.
const allRecords = await table.selectRecordsAsync({
  fields: [
    //Your Relationship Field ID
    "your_field_id"
  ]
}).then(records => {return records.records});

let updatedRecords = allRecords.map(record => {
  return {
    id: record.id,
    fields: {
      //Your Relationship Field
      "your_field_id": []
    }
  }
});

if (updatedRecords) {
  if (updatedRecords.length <= 50) {
    await table.updateRecordsAsync(updatedRecords)
    .then(updatedRecords => {
      console.log(`Updated ${updatedRecords.length} Records.`)
    })
  } else if (updatedRecords.length > 50) {
    while (updatedRecords.length > 50 || updatedRecords.length > 0) {
      await table.updateRecordsAsync(updatedRecords.slice(0, 50));
      updatedRecords = updatedRecords.slice(50);
    }
    console.log(`Updated ${allRecords.length} Records.`);
  }
}

If you're curious about how to figure out how each field type accepts data (and subsequently, how to clear values from them), you can reference the Cell values & field options section of the Scripting API documentation.

For linked record fields, we can clear existing field values by passing an empty array to the field. This can be seen in the script:

fields: {
      //Your Relationship Field
      "your_field_id": []
}

Happy to answer any additional questions you might have about different scenarios and use cases!

Edit (2024-04-30): Here's a refined version on the original script that provides examples for clearing values from both single-select and linked record field types:

const tableId = "your_table_id";
const table = base.getTable(tableId);

const fields = {
  yourSingleSelectField: "your_field_id",
  yourLinkedRecordField: "your_field_id"
};

const records = await table.selectRecordsAsync({ fields: Object.values(fields) })
  .then(q => q.records);

let updates = records.map(r => ({
  id: r.id,
  fields: {
    [fields.yourFieldName]: null,
    [fields.yourLinkedRecordField]: []
  }
}));

while (updates.length) {
  await table.updateRecordsAsync(updates.splice(0, 50));
}

[JRMunoz]

Hi @Ben_Young1 

I tried using this script but my field is a "single select" format.

How can I adapt it?

Regards,

Jose

[Ben_Young1]

Hey @JRMunoz! 

Here you go:

const tableId = "your_table_id";
const table = base.getTable(tableId);

const fields = {
  yourFieldName: "your_field_id"
}

//Load all records from the table
//Returns an array of record objects.
const records = await table.selectRecordsAsync({ fields: Object.values(fields) })
  .then(q => q.records);

let updates = records.map(r => ({ id: r.id, fields: { [fields.yourFieldName]: null }}))

while (updates.length) {
  await table.updateRecordsAsync(updates.splice(0, 50));
}

You'll need to replace the following in order for this to work:

1.  tableId - Variable value.
2. fields.yourFieldName - Property value.