Help

Help with script to merge duplicate records

Topic Labels: Automations
1162 2
cancel
Showing results for 
Search instead for 
Did you mean: 
CarlosH_18
4 - Data Explorer
4 - Data Explorer

Hi I've been working a script that is supposed to be finding and merging duplicate records but while it can find and delete the duplicates it is not actually updating them and I don't know why. The console does not give me any error messages. If anyone can help figure that out it would be much appreciated. The code is looking for duplicates in a table named "Script Testing" and is looking for duplicates in the "Name" column. The code is as follows

 

let processedRecords = [];

// Get the table object
let table = base.getTable('Script Testing');

// Get all records from the table
let queryResult = await table.selectRecordsAsync({
    view: 'Grid view',
    sort: [{field: 'Name', direction: 'asc'}],
});

let records = queryResult.records;

// Loop through all records
for (let i = 0; i < records.length; i++) {
    let record = records[i];
    let recordName = record.getCellValue("Name");

    console.log('Record Name:', recordName);

    // Check if the record name has been processed
    if (!processedRecords.includes(recordName)) {
        processedRecords.push(recordName);

        // Find any duplicates of the current record
        let duplicates = records.filter(=> r.getCellValue("Name") === recordName);

        console.log('Duplicates:', duplicates);

        // If there are duplicates, merge the data into the first record
        if (duplicates.length > 1) {
            let updateFields = {};
            for (let j = 1; j < duplicates.length; j++) {
                let duplicate = duplicates[j];

                console.log('Duplicate:', duplicate);

                // Loop through all fields in the duplicate
                let duplicateFields = duplicate.fields;
                for (let fieldName in duplicateFields) {
                    let fieldValue = duplicateFields[fieldName];
                    // If the field is not already in the record, add it
                    if (typeof record.getCellValue(fieldName) === 'undefined' ) {
                        console.log(`Adding field "${fieldName}" with value "${fieldValue}"`);
                        updateFields[fieldName] = fieldValue;
                    } else {
                        // If the field already exists in the record, merge the values
                        let existingValue = record.getCellValue(fieldName);
                        if (existingValue !== fieldValue) {
                            console.log(`Merging field "${fieldName}" with value "${fieldValue}"`);
                            updateFields[fieldName] = [existingValue, fieldValue];
                        }
                    }
                }
                
                console.log('Deleting duplicate...');

                // Delete the duplicate record
                await table.deleteRecordAsync(duplicate.id);
                
                console.log('Duplicate deleted!');
            }
            
            console.log('Updating original record...');

            // Get the latest version of the record before updating it
            record = queryResult.getRecord(record.id);

            // Update the original record with the data from the duplicate record
            await table.updateRecordAsync(record.id, updateFields);

            console.log('Original record updated!');
        }
    }
}

 

2 Replies 2
Ron_Daniel
7 - App Architect
7 - App Architect

Have you tried using the DeDupe extension? It's worked wonders for me on numerous occasions.

Heya, I think the issue is partly due to the code trying to get field data from a record.

Field data comes from the table object, not the record object.

     // Loop through all fields in the duplicate
                let duplicateFields = table.fields;
                console.info(duplicateFields)

As a tip for debug, remember that you can use;

console.log("My Message");
console.info("My Info");
console.warn("My Warning");
console.error("My Error");