Duplicates

[Vonas_P]

Hi,
I am running into issues. I am not a coder at all and I ask GPT to help me. Usually, it does a great job in writing some basic scripts and it has already created nice solutions with scripts. But this time, I am running into issues. I have a huge database and I was asking GPT to write a script to merge duplicates. When I do it one by one, it works. But as soon as I let it search the whole database, it crashes and mentions that it's taking longer than 30 seconds. I was trying to break it down into smaller views, but the problem is that it should check all the data actually, and searching records is also not an option since that's limited to 100 only. Any idea would be awesome.


here is what i got from GPT

Option 1

// Access the table
let table = base.getTable("Online Stores");
let query = await table.selectRecordsAsync();
let records = query.records;

// Define a map to track duplicates
let recordMap = new Map();

// Identify duplicates based on 'merchant_name'
for (let record of records) {
    let merchantName = record.getCellValueAsString("merchant_name");

    if (recordMap.has(merchantName)) {
        recordMap.get(merchantName).push(record);
    } else {
        recordMap.set(merchantName, [record]);
    }
}

// Process and merge duplicates
for (let [merchantName, duplicateRecords] of recordMap) {
    if (duplicateRecords.length > 1) {
        // Sort records by 'Created Time' to keep the most recent
        duplicateRecords.sort((a, b) => b.getCellValue("Created Time") - a.getCellValue("Created Time"));

        let mostRecentRecord = duplicateRecords[0];
        let recordsToUpdate = [];
        let combinedData = {};

        // Combine data from older records
        for (let i = 1; i < duplicateRecords.length; i++) {
            let record = duplicateRecords[i];
            let fields = record.fields;

            for (let field in fields) {
                if (fields[field] && !mostRecentRecord.getCellValueAsString(field)) {
                    combinedData[field] = fields[field];
                }
            }
        }

        // Prepare update for the most recent record
        if (Object.keys(combinedData).length > 0) {
            recordsToUpdate.push({
                id: mostRecentRecord.id,
                fields: combinedData
            });
        }

        // Update the most recent record with combined data
        if (recordsToUpdate.length > 0) {
            await table.updateRecordsAsync(recordsToUpdate);
        }

        // Delete the older duplicate records
        let recordsToDelete = duplicateRecords.slice(1);
        while (recordsToDelete.length > 0) {
            await table.deleteRecordsAsync(recordsToDelete.splice(0, 50));
        }
    }
}

// Notify or log completion
console.log("Duplicate merging process completed.");

Option 2

// The table and view to check
let table = base.getTable("Online Stores");
let view = table.getView("Duplicates");

// Dynamically get all field names from the table
let fieldNames = table.fields.map(field => field.name);

// Query the view including all fields
let query = await view.selectRecordsAsync({ fields: fieldNames });
let records = query.records;

// Function to merge data from old record to new record
function mergeRecords(newRecord, oldRecord, fieldNames) {
    let mergedFields = {};
    for (let fieldName of fieldNames) {
        if (!newRecord.getCellValueAsString(fieldName) && oldRecord.getCellValueAsString(fieldName)) {
            mergedFields[fieldName] = oldRecord.getCellValue(fieldName);
        }
    }
    return mergedFields;
}

// Process duplicates in the view
for (let record of records) {
    let merchantName = record.getCellValueAsString("merchant_name");
    let duplicates = records.filter(r => r !== record && r.getCellValueAsString("merchant_name") === merchantName);

    if (duplicates.length > 0) {
        duplicates.sort((a, b) => b.getCellValue("Created Time") - a.getCellValue("Created Time"));
        let mostRecentRecord = duplicates[0];

        let fieldsToUpdate = {};
        for (let oldRecord of duplicates.slice(1)) {
            let mergedFields = mergeRecords(mostRecentRecord, oldRecord, fieldNames);
            for (let key in mergedFields) {
                fieldsToUpdate[key] = mergedFields[key];
            }
        }

        if (Object.keys(fieldsToUpdate).length > 0) {
            // Check if the record still exists before updating
            if (query.getRecord(mostRecentRecord.id)) {
                await table.updateRecordAsync(mostRecentRecord, fieldsToUpdate);
            }
        }

        // Optionally delete the older duplicate records
        let recordsToDelete = duplicates.slice(1).map(r => r.id).filter(id => query.getRecord(id));
        while (recordsToDelete.length > 0) {
            await table.deleteRecordsAsync(recordsToDelete.splice(0, 50));
        }
    }
}

console.log("Duplicates processed.");