Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Help with issues in script to merge nearly duplicate records

Topic Labels: Extensions
670 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi Airtable community! I’m working on a script to automatically merge nearly duplicate records based on given criteria (in this case, I’m using emails), and I’ve run into a couple of problems that I can’t figure out how to solve.

  1. I can’t figure out how to get the script to apply to the actual table. It currently gives me the output as a table within the scripting app instead (see screenshot of current code output).
  2. In the table within the scripting app, it seems looks like the script is having trouble merging multiple select/attachment fields. I’m not sure if that’s just an issue within the scripting app or if it would still be reflected once the script is applied to the actual table.

Script:

let dataTbl = base.getTable('Duplicates test');
let dataQuery = await dataTbl.selectRecordsAsync();

let result = dataQuery.records.reduce((acc, record) => {
    let email = record.getCellValue('Email');
    let color = record.getCellValue('Color');
    let id = record.getCellValue('ID Number')
    let a = record.getCellValue('Attachments')
    let found = acc.find(item => item['Email'] == email);
    if (!found) {
        acc.push({'Email': email, 'Color': color, 'ID Number': id, 'Attachments': a})
    }
    
     else {
        found['Color'] = found['Color'] + ', ' + color;
        found['ID Number'] = found['ID Number'] + ', ' + id
    }

    return acc;
    }, []);

console.log(result)

output.table(result)

Screenshot of base:
Base

Screenshot of current code output:
Code output

In case this is helpful, I’ve tried to solve issue 1 by adding this to the end of my script:

let updates = result.map(update => {
    return {
        "Email":update.email,
        }
    }
);

while (updates.length>0){
    
    await dataTbl.updateRecordsAsync(updates.slice(0,50));
    updates = updates.slice(50);
};

but then I get this error message:
Error

Thanks in advance for any guidance you have to offer!

2 Replies 2

I’m sorry that I don’t know the answer to your questions, but there are lots of JavaScript experts here who will likely help you out!

However, I just wanted to post to let you know about Airtable’s excellent DeDupe app, in case you didn’t already know about it:

Hey! Thanks for this. I agree, Airtable’s dedupe app is great. I’m hoping this script will save me having to manually click through each set of duplicates, at least for records with exactly matching criteria.