Airscript Learn to use Airtable scripts

Deleting records

Most of the time, we are adding data to our Airtable bases, but, occassionally, we might need to delete data. This post will show a method for deleting data based on some attribute of each record.

Let’s suppose that our table contains records of people with their age:

And, let’s also imagine that we only want to keep the records of working age people, say between the ages of 18 and 65. We can write a script that will iterate through the records and delete those whose age is less than 18 or more than 65.

We’ll start in the common way by defining the table and the query:


let table = base.getTable('People');
let query = await table.selectRecordsAsync();

There are a number of ways we can proceed from here, but our method in this post will be to:

  • Iterate through the records and build a new array of the records we want to delete
  • Loop through the newly created array and perform a delete operation for all of the records within it

This method makes use of a couple of interesting devices:

  • the JavaScript “filter” method
  • the concept of manipulating your data into another, temporary form (typically an array or an object) to allow you to deal with fewer records, less fields or, in some way, more appropriate data for the thing you actually want to do.

The “filter” method operates on an array and returns a new array based on the filtering conditions you specify. For our script the filter operation looks like this:


let recordsToDelete = query.records.filter(
    record => record.getCellValue('Age') < 18 || record.getCellValue('Age') > 65
)

Here, we are creating a new array, recordsToDelete and this is formed by filtering the query.records array returned from our query. The “filter” method loops through every record in the query.records array and we have a variable (here called record) to represent the current element being processed by the filter.

Each record gets passed, using the arrow function notation, to an expression where it is evaluated against the defined conditions. If the evalution returns true then the record is added to our new array; if it returns false then it won’t be added to the new array.

In our example we are evaluating against the conditions:

  • Age is less than 18 (record.getCellValue('Age') < 18)
  • OR (noted by the “double pipe” operator ||
  • Age is greater than 65 (record.getCellValue('Age') > 65)

Let’s run our script as it is to understand what the “filter” operation is doing. Add in two console.log statements to output the records array both before and after the filter. Your script should look like this:


let table = base.getTable('People');
let query = await table.selectRecordsAsync();

console.log(query.records)

let recordsToDelete = query.records.filter(
    record => record.getCellValue('Age') < 18 || record.getCellValue('Age') > 65
)
console.log(recordsToDelete);

When you run the script you should see output similar to this:

We can see that we started with 10 records and the filter operation produces an array of 6 records (remember that these are the records that passed the conditions we applied and are the records we want to delete). If you expand the 2nd array you will be able to confirm that these are indeed the records where the age is less than 18 or over 65.

So far, so good. We now have an array containing the records we want to delete. We can now loop through our new array of records we want to delete:


for (let deleteRecord of recordsToDelete) {
    await table.deleteRecordAsync(deleteRecord.id);
    output.text(`Deleted record for ${deleteRecord.name}`)
}

As we have seen in previous posts, we’re using a for loop through the recordsToDelete array and we define a variable deleteRecord to hold the record of the current iteration.

The deleteRecord.id is passed to the table.deleteRecordAsync method and, once the deletion has happened, we report back to the user that the record has been deleted.

Our final script looks like this:


let table = base.getTable('People');
let query = await table.selectRecordsAsync();

let recordsToDelete = query.records.filter(
    record => record.getCellValue('Age') < 18 || record.getCellValue('Age') > 65
)

for (let deleteRecord of recordsToDelete) {
    await table.deleteRecordAsync(deleteRecord.id);
    output.text(`Deleted record for ${deleteRecord.name}`)
}

As you run the script you should see output similar to this:

Finally, a word of warning: We are deleting data here, so be really careful when you proceed with an operation like this. We’ll see in another post how you can get the user to preview and confirm operations before they actually proceed, which would be really good practice with a delete operation. Also worth noting that records deleted by the scripting block can be recovered from the trash bin if you really make a mistake 😉.

Recap

  • Queried the table
  • Used “filter” to create a new array of the records we wanted to delete
  • Looped through the new array and deleted the records