removing records that have duplicate values in 1 field using automation script

Hmm, your script seems to be running fine:

What error message are you getting when you try to run it?  Is the ‘Deviceid’ field completely filled out when the automation triggers?

Hi, I’m not receiving any error message, it says it ran successfully but the duplicates are still not deleted. FYI, the records I am creating is through a post API request, im not sure if this would effect anything but I thought I’d just put this out there. Also my real table name is “Intro Data” with spaces in between, so my code looks like.

let table = base.getTable("Intro Data");

// Get the triggering record's Deviceid

let inputConfig = input.config();

let newDeviceId = inputConfig.Deviceid;

if (!newDeviceId) {

    throw new Error("No Deviceid found in input config.");

}

// Fetch all records with same Deviceid

let query = await table.selectRecordsAsync({ fields: ["Deviceid", "createdtime"] });

let matchingRecords = query.records.filter(record => {

    return record.getCellValue("Deviceid") === newDeviceId;

});

// Only proceed if duplicates exist

if (matchingRecords.length > 1) {

    // Sort by createdtime DESC (latest first)

    matchingRecords.sort((a, b) => new Date(b.getCellValue("createdtime")) - new Date(a.getCellValue("createdtime")));

    let [latest, ...toDelete] = matchingRecords;

    for (let record of toDelete) {

        await table.deleteRecordAsync(record.id);

    }

}

when a record is created via post request, the automation runs, it says it ran successfully but it doesnt delete the record with duplicate values

Did not give it a shot on my end yet, but should still run if records are created via POST api request. Creating records via API creates the record with full info for it at once.
So that is probably not the reason.

Hope to get back to this later today!

Mike, Consultant @ Automatic Nation

Hi,
i would suggest to delegate most of script work to UI part of automation and only leave a small part because there is no ‘Delete record’ step.
I suppose the number of duplicates to delete less than 50, otherwise you should run delete operation by batches (make a last line a bit more complex)
 

By way, if you want to fix your script, your value of DeviceId looks strange. It should be selected with ‘+’ button and look like Token on my screenshot (ids and newID)

Hi I’m very sorry, I’m still a little confused on what you did on the find records area.

It’s the part
// Fetch all records with same Deviceid
(but i was too lazy to rename my ‘ID’ column of ExampleTable, in your case it is ‘DeviceID’)
So you should set condition. Press on wheel near input field and select Dynamic.  
 

then press ‘+’ and select DeviceID from trigger record
 

I added ‘not empty’ just to ensure that next created record with empty ID will not erase all previous records with empty ID.  If your table has no such records, you can omit this condition.
Procedure will find all records including new created, that’s why script run only when more than 1 record found and filter the list of IDs to exclude ID of trigger record (new created)


 

Thank you! It works. 

Please help with this last question, would this only work for the first 1000 records? cuz the last find records has a ‘max record limit’ of 1000

No, for thousand of duplicates you should better use script. But If you have thousands of duplicates, you can better perform bulk-dedupe
duplicate and convert
 

in new table, add Rollup for latest created date
 

pass it back via lookup and add a formula to compare
 

Delete all records, whose ‘created’ is not latest for that DeviceID
 

Then do a cleanup - remove this additional table and 3 additional fields created during this procedure.