Merging Duplicates

[Vee]

Hi Airtable Community,

Hoping you can assist.

I'm having trouble merging duplicate records, (Keeping the original record and updating  specified fields from the new record, then deleting the new record once complete).

I have no java script knowledge whatsoever so used ChatGPT to come up with a script. The first script deleted both records, the second updated but kept both records and the third script is completely useless! 

Essentially, I would like the automation to be able to find a duplicate address (Field Name is Property Address and is linked with another table) I have also 

The automation I have set up looks like this

and the script is as follows

let table = base.getTable("Sold Stock");
let query = await table.selectRecordsAsync();

let duplicateMap = new Map();

// Find duplicates and collect their IDs
for (let record of query.records) {
let propertyAddress = record.getCellValue("Property Address");
if (duplicateMap.has(propertyAddress)) {
duplicateMap.get(propertyAddress).push(record);
} else {
duplicateMap.set(propertyAddress, [record]);
}
}

// Merge duplicates and update fields
for (let [address, duplicates] of duplicateMap.entries()) {
if (duplicates.length > 1) {
let masterRecord = duplicates[0];
let deleteRecords = duplicates.slice(1);

for (let deleteRecord of deleteRecords) {
await table.updateRecordAsync(masterRecord, {
"Property Status": deleteRecord.getCellValue("Property Status"),
"Display Online As": deleteRecord.getCellValue("Display Online As"),
"Disclose Price": deleteRecord.getCellValue("Disclose Price"),
"Cancel Auction Day Services": deleteRecord.getCellValue("Cancel Auction Day Services"),
"Is The Reserve Price On The Authority": deleteRecord.getCellValue("Is The Reserve Price On The Authority"),
"Vendors Reserve Price": deleteRecord.getCellValue("Vendors Reserve Price"),
"Holding Deposit Paid": deleteRecord.getCellValue("Holding Deposit Paid"),
"Holding Deposit Amount": deleteRecord.getCellValue("Holding Deposit Amount"),
"Name of Depositor": deleteRecord.getCellValue("Name of Depositor"),
"Important Info To Note": deleteRecord.getCellValue("Important Info To Note")
});
await table.deleteRecordAsync(deleteRecord);
}
}
}

Any help would be greatly appreciated.

Thanks,

[dilipborad]

Hello @Vee 
Please share the error message with a screenshot.

[ScottWorld]

Hi @Vee,

Note that you can do this without scripting by using Airtable’s Dedupe Extension.

— ScottWorld, Expert Airtable Consultant

[Alexey_Gusev]

Hi,
first of all, what's your goal?
1)process existing table with many duplicates to get a result table with unique address

2) set automation to check new record(s)  for duplicate address with 'deduped' table

you don't need scripting at all (almost)
to dedupe existing table, you can use extension mentioned by @ScottWorld 

but sometimes, when the number of duplicates > 50, it's too hard to resolve them all by hand.
you can create the table of addresses by duplicate addresses field and turning it to linked (to a new table)

notice that if your addresses contains comma, they cannot be linked in a simple way, because 
Street #num, State, City  turned to a linked value will create 3 linked values.
You need formula to take it in brackets
like `"`&{Fieldname}&`"`  (note it will work only in you have no " brackets in addresses)
 and then create link.

By way, you already have the linked table, so you can use 'Count' field type in it to see all duplicates.
To merge duplicates, add each column as rollup with 'ARRAYUNIQUE'
If you want to add just newer values instead of all, you can use built-in filter:
create (in main table) creation date column, without time. add in linked table rollup of this column with MAX(values), add in main table lookup of this rollup {Newest date for this address}, add formula IF({Creation time={Newest date for this address},'Yes'), use this formula in built-in filter.

to do the step2, check new records,
use conditional group. At first, Find Records step with Address of new record in the current table.
then use condition according to result of Find Records length
if =0, then it's new Address, do nothing
if >0, it's existing address, make Update record step, with all fields filled as 
[Old Value], [New value] in order to append data, instead of overwrite,
then you need to delete new record, such step is absent, so use script step with record id as input data 'id'

await base.getTable('YourTable').deleteRecordAsync(input.config().id)

[Vee]

Hi @dilipborad, that's the thing, there is no error message 

[Vee]

Thanks Scott, we have the dedupe extension but find, as we have many many fields, it's too time consuming to filter through each one and make the updates. We are aiming for a more automated solution.

[Vee]

Thanks Alexey I'll look into your rollup method 

[Alexey_Gusev]

Hi, 
I read you answer about 'too many fields'. You might don't want to add all of them as rollups, because you need to click each field and set condition.
it's possible to do in bulk, but it depends on how you want to merge.
for example if it's ok to leave only most new data when merging duplicates.
(table 1 - your table, table 2 - linked table with addresses. can be created from 1st, by duplicate address field and turn it to linked)


then, table 1 - create Date column  (created or modified, it's up to you - the way to find 'most new')
table 2, create Rollup of dates MAX(values) , create Count for links
table 1 - add lookups of Count and MaxDate
table 1, add formula, Date=Maxdate result will be 1 for singles and 1 or 0 for duplicates, 1 for newest.
now filter Count>1 AND formula(Date=Maxdate) = 0 , choose link field and press del.
in such way all 'older' duplicates are unlinked.
switch to table 2, click on link field and choose 'Add lookups', Select all
Each field of table 1 will be added as lookup Fieldname1 (from table 1), Fieldname 2 (from table 1 ) etc.

You can quickly turn it to static. Save the table as CSV, open by notepad, select and copy '  (from table 1 )', choose Replace, paste ' (from table 1 )' in Find what,  leave empty Replace with:, choose Replace all.
Now load it back via 'new table' , CSV, but switch off Auto-detect field types.
Thus, you will  have static table with  text fields