Help

Re: Problem with Handling Duplicate Record Merging in Airtable Script

1011 0
cancel
Showing results for 
Search instead for 
Did you mean: 
fede2244
4 - Data Explorer
4 - Data Explorer

Hello Airtable Community,

I'm reaching out for some assistance with a scripting block in Airtable. My objective is to identify duplicate records in a "PNS - Content samples" view of the "PNS" table, based on a unique "Link" field, and merge them. The script should combine the records such that for each link, we keep a single record with data aggregated from the duplicates. The requirement is to preserve non-empty field values from the first record and fill in any empty fields with data from the second record, ensuring that no data is lost from the combined records.

The approach I've taken involves:

  1. Grouping records by the "Link" field.
  2. Iterating through the groups to aggregate field values.
  3. Updating the first record with the combined data.
  4. Deleting the remaining duplicate records.

Here's the code:

 

async function mergeDuplicates() {
    console.log("Starting the duplicate merging process...");
    const table = await base.getTable("PNS");
    const view = await table.getView("PNS - Content samples");
    
    const fields = [
        "Link", "Post topic", "Date", "Stato", "Social", "intro", "Branded?", "Reach",
        "Impressions", "Likes", "Comments", "Shares", "Saves",
        "Followers", "Plays", "Caption",
        "Media url", "Timestamp"
    ];
    console.log("Fields loading complete.");

    const query = await view.selectRecordsAsync({ fields });
    console.log(`Query completed. Number of records loaded: ${query.records.length}`);

    const linkMap = new Map();

    query.records.forEach(record => {
        const link = record.getCellValueAsString("Link").toLowerCase().trim();
        if (!linkMap.has(link)) {
            linkMap.set(link, []);
            console.log(`New link discovered and added to the map: ${link}`);
        }
        linkMap.get(link).push(record);
    });

    console.log("Grouping of records by link completed.");

    // Process each group of duplicates
    for (const [link, records] of linkMap.entries()) {
        if (records.length > 1) {
            console.log(`Found ${records.length} duplicate records for the link: ${link}`);
            let combinedRecord = {};

            // Combine data from the first and second records
            records.slice(0, 2).forEach(record => {
                fields.forEach(fieldName => {
                    const value = record.getCellValue(fieldName);
                    if ((value !== null && value !== undefined) || !combinedRecord.hasOwnProperty(fieldName)) {
                        combinedRecord[fieldName] = value;
                    }
                });
            });

            console.log(`Data combined for the first record of the link: ${link}`);

            try {
                // Update the first record with the combined data
                await table.updateRecordAsync(records[0].id, combinedRecord);
                console.log(`Main record updated for the link: ${link}`);
            } catch (error) {
                console.error(`Error while updating the main record:`, error);
            }

            try {
                // Delete the other duplicate records
                for (let i = 1; i < records.length; i++) {
                    await table.deleteRecordAsync(records[i].id);
                    console.log(`Duplicate record deleted: ID ${records[i].id}`);
                }
            } catch (error) {
                console.error(`Error while deleting duplicate records:`, error);
            }
        }
    }
    console.log("Duplicate merging process completed.");
}

mergeDuplicates().catch(console.error);

 

However, I'm running into an error that isn't providing much detail. The console simply outputs Error {name: "a"} and doesn't specify what the error is. Additionally, I'm encountering specific errors during certain operations with the following output:

When attempting to update the primary record:

CONSOLE.ERROR "Error while updating the main record:"->Error {name: "a"}

And when attempting to delete the duplicate records:

CONSOLE.ERROR "Error while deleting duplicate records:"->Error {name: "a"}
 
If anyone has faced and overcome similar challenges or has suggestions for better approaches for automating the process of merging duplicate records, I'd greatly appreciate your insights.

Thank you in advance for your time and assistance!

4 Replies 4
jsep
7 - App Architect
7 - App Architect

The code looks good at first glance. I can help you debug this issue. Please schedule a call with me, and we'll get it fixed for free.

If you can provide an example base and example data in that base it'll make helping you a lot easier!

This is the example base with data similar to my data:

https://airtable.com/appPxyfv4u8cmpxr3/shrWz1c4Wgd4dz97p

Thanks in advance,

Federico

Thanks!  I tried running your script with the data you provided and it ran fine without errors:

Screenshot 2024-04-14 at 5.03.48 PM.png
If you can provide data that causes the error that'd be great!