Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 30, 2023 02:55 PM
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); }
Solved! Go to Solution.
Jan 30, 2023 04:41 PM
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:
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.
Jan 30, 2023 04:41 PM
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:
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.
Jan 31, 2023 06:26 AM
Just curious here: What is the purpose of this when the data doesn't get deleted entirely?
Jan 31, 2023 07:19 AM
@Ben_Young1 that's wonderful, and so well documented. Thank you so much!
Jan 31, 2023 07:35 AM
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.
Jan 31, 2023 09:03 PM - edited Feb 02, 2023 04:43 AM
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! 🙂
Feb 02, 2023 08:56 AM - edited Apr 30, 2024 10:35 AM
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));
}
Apr 29, 2024 05:24 PM - edited Apr 29, 2024 05:24 PM
Hi @Ben_Young1
I tried using this script but my field is a "single select" format.
How can I adapt it?
Regards,
Jose
Apr 30, 2024 10:30 AM
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: