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:
- Grouping records by the "Link" field.
- Iterating through the groups to aggregate field values.
- Updating the first record with the combined data.
- 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!
