Help

Re: Delete Records and extend script execution

1182 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Marc_Justin_Rai
6 - Interface Innovator
6 - Interface Innovator

Hello, I’m trying to delete a massive duplicate records in my table about 10,000 to 20,000 records exist in my table. As i don’t know where to insert the proper condition if the records is duplicate by knowing they are older dates or not up-to-date/current date And I can’t delete them in one go due to script execute time.

// Read our table
var table = base.getTable("Weekly Metrics");
var query = await table.selectRecordsAsync ();
// Check whether any record in that table
// match the input 
let duplicate = query.records.filter((record)=>{
    return query.records.find((potentialDuplicate) => {
    return record.getCellValue("Pages") === potentialDuplicate.getCellValue("Pages") && record.id !== potentialDuplicate.id;
    //&& record.id !== potentialDuplicate.Id
    })
});
// console.log(duplicate);
let updates = duplicate.map(update => {
    return {
        "id":update.id,
        fields:{
            "Duplicate?":true
        }
    }
});
while(updates.length > 0){
    await table.updateRecordsAsync(updates.slice(0,50));
    updates.slice(50);
}
12 Replies 12

Um, how about a script that runs every 15 minutes deleting a fixed number of dupes? It constantly chips away at the supply of dupes until there are none.

Just an uninformed thought - if you have a database that’s this over-crowded with dupes, you might want to consider fixing the architecture of your system instead of constantly applying a dupe-elimination bandaid.

Can you please explain more about this? The code sample you provided update records; it does not delete records.

Are you trying to use an automation script (with its time limits) or are you running the script using Scripting app and a button?

I have written scripts in Scripting app that take a very long time to run, and as long as I just let them run, they did not time out.

Plus, if you submit the delete requests in megabatches (15 batches of 50), you should be able to submit all the delete requests in less than 30 seconds. While I have not used the megabatch method for deleting records, I have used it for both creating and updating records, and this technique is very fast compared to using only batches or not using batches at all.

Hi, i don’t know how to delete a record yet, my code only provide update records and How do i submit a request per batches? Thank you for your help. :slightly_smiling_face:

I’m using this datafetcher app in the airtable as i thought of their feature update will delete the existing data and replace it with new. That’s why the table in my airtable is over-crowded with dupes.

Your code is already submitting update requests in batches of 50. You delete records in batches in a similar way. See the documentation for more info.

Don’t worry about using megabatches. (By the way, that is a term I made up. Megabatches is not an official term.) The process is a bit complicated if you do not yet know how to do batch updates.

Does while loop required with the .slice in doing that megabatches?

The slice In the while loop is what process a batch of 50. It is part of JavaScript array handling. There are other ways of using batches, but this is one of the easiest.

However using megabatches is much, much more complex and involves breaking up the requests of 750, each arranged into 15 batches of 50. Then you have to submit the requests asynchronously instead of awaiting each call, while also adding code to make sure that you do not submit more than one megabatches per second. Don’t bother trying to do this at your current stage of your coding journey. Get some more experience with JavaScript before tackling something like megabatches. I only brought it it because it is one method of reducing script execution time.

I got the gist of this one, as i don’t know where to put the deletion that is based on current date. As i get the duplicated data i have to check if the duplicated date is not up-to-date.

Marc_Justin_Rai
6 - Interface Innovator
6 - Interface Innovator

Hi, I have a question. can i use delete or forloop with if-else inside the .map?

That’s not really how .map is designed to be used. It is for mapping one array onto a different array. You shouldn’t be deleting records in the array as part of the mapping process.

Hello miss @kuovonne, is it possible to use the updateRecordAsync from base1 but the data will be put to base2. like base2.updateRecordAsync

No. Scripting is designed to only affect the base in which the script resides. There are workarounds that involve accessing the REST API, webhooks, or third party tools to affect change in a different base. All of these workarounds add a layer of complexity.