Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: clearing fields with scripting

Solved
Jump to Solution
4297 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Mark_Newton
6 - Interface Innovator
6 - Interface Innovator

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!
1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

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.

Ben_Young1_0-1675122349718.png

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.

Ben_Young1_3-1675125468440.png

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

Ben_Young1_4-1675125664881.png

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 + CCMD + 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

Ben_Young1_1-1675124136264.png

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:

Ben_Young1_1-1675124412245.png

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.

Ben_Young1_2-1675124678682.png

 

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.

See Solution in Thread

8 Replies 8
Ben_Young1
11 - Venus
11 - Venus

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.

Ben_Young1_0-1675122349718.png

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.

Ben_Young1_3-1675125468440.png

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

Ben_Young1_4-1675125664881.png

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 + CCMD + 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

Ben_Young1_1-1675124136264.png

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:

Ben_Young1_1-1675124412245.png

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.

Ben_Young1_2-1675124678682.png

 

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
7 - App Architect
7 - App Architect

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

Mark_Newton
6 - Interface Innovator
6 - Interface Innovator

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

Mark_Newton
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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! 🙂 

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

Hi @Ben_Young1 

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

How can I adapt it?

Regards,

Jose

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.