Skip to main content

Bulk merge using an "auto" dedupe

  • May 31, 2022
  • 5 replies
  • 160 views
N

Hi Everyone,

Is there an automated way to combine 3 lines into one?

The context is that, we will have 3 different persons filling in 3 different forms to feed into the same table, but want to combine them into a single line.

Dedupe is OK but we will need to do this 1800 times :frowning: And of course, there are a lot more columns than shown above.

Thanks.

    5 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Hey Nick, if each form has a unique identifier, then you could set it up so that each form submission gets linked to a single record in another table, and then create lookup fields to display everything nicely? Nice thing is that it’d work for your historical data too


    Adam, Airtable Consultant - thetimesaving.company
    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9770 replies
    • May 31, 2022

    Note that the DeDupe App won’t work for your needs, unfortunately, because Airtable didn’t do a very good job with the DeDupe App. It doesn’t merge data (except for a few field types)… it simply deletes records that you pinpoint as duplicates.

    @Adam_TheTimeSavingCo’s solution above is probably the best and easiest and simplest.

    However, if you’re really determined to get everything merged onto one line, your best bet is to capture the duplicates on their way into Airtable, which will require some work on your end — it will likely require scripting or the use of an external automation tool, if you’re literally going to be merging data in cells. I don’t know scripting (yet), so personally I use Make.com for this. I just did this exact thing the other day for a client.

    N
    • Nick_Tan
    • Author
    • New Participant
    • 2 replies
    • May 31, 2022

    Hey Nick, if each form has a unique identifier, then you could set it up so that each form submission gets linked to a single record in another table, and then create lookup fields to display everything nicely? Nice thing is that it’d work for your historical data too



    Adam, that’s exactly what I was after. Thank you!

    N
    • Nick_Tan
    • Author
    • New Participant
    • 2 replies
    • May 31, 2022

    Note that the DeDupe App won’t work for your needs, unfortunately, because Airtable didn’t do a very good job with the DeDupe App. It doesn’t merge data (except for a few field types)… it simply deletes records that you pinpoint as duplicates.

    @Adam_TheTimeSavingCo’s solution above is probably the best and easiest and simplest.

    However, if you’re really determined to get everything merged onto one line, your best bet is to capture the duplicates on their way into Airtable, which will require some work on your end — it will likely require scripting or the use of an external automation tool, if you’re literally going to be merging data in cells. I don’t know scripting (yet), so personally I use Make.com for this. I just did this exact thing the other day for a client.


    Thanks Scott, Adam’s method will work well!

    K
    Forum|alt.badge.img+1
    • KevinCM
    • New Participant
    • 1 reply
    • February 9, 2024
    Here we go! Please change the table ('YOURTABLE') and column names ("Name")!
    It finds the duplicate and merges all cells. Note, however, that the code assumes that the values in the cells are the same if the duplicate records have an entry in the same column. 
     
    let table = base.getTable('YOURTABLE');
    let query = await table.selectRecordsAsync();
    let records = query.records;
     
    let uniqueRecords = new Map();
     
    for (let record of records) {
        let name = record.getCellValue("Name");
        if (uniqueRecords.has(name)) {
     
            let existingRecord = uniqueRecords.get(name);
            let updatedFields = {};
     
            for (let field of table.fields) {
                let fieldValue = record.getCellValue(field);
                if (fieldValue !== null) {
     
                    updatedFields[field.name] = fieldValue;
                }
            }
     
            await table.updateRecordAsync(existingRecord.id, updatedFields);
     
            await table.deleteRecordAsync(record.id);
        } else {
     
            uniqueRecords.set(name, record);
        }
    }

     

    console.log("Duplicates merged.");


    Terms & ConditionsAccessibility statement