Help with a script. Please

[LordPug]

Hello,

I am working on an Airtable script to flag dupliacte records in a table and automatically flag them. But for some reason when I run the script, nothing happens. I have tried the script in another Airtable project and it works just fine but its not working here. Would be glad to know what I am doing wrong and how to fix it.

Here is my script

var tables = base.getTable("📊 Impact Data");

var query = await tables.selectRecordsAsync({

sorts: [{field:"Time",direction:"desc"}],

fields:["location_id"]}).then(result => result.records.map(rec => ({id: rec.id, field: rec.getCellValue("location_id")})))

let uniqueRecords = query.filter((rec, i, arr)=> arr.map(el => el.field).indexOf(rec.field)!==i);

console.log(uniqueRecords);

let updates = uniqueRecords.map(update => {

return {

"id" : update.id,

fields: {

"Test": true

}

}

})

console.log(updates);

while (uniqueRecords.length>0){

await tables.updateRecordsAsync(updates.slice (0, 50));
 uniqueRecords= uniqueRecords.slice(50); }

I have also attached records of the two projects (one where it runs and one where it doesnt work). Thank you in advance 😁

[Karlstens · Accepted solution]

Could you take a screenshot of your table columns, or list out their names?

The script is a little bit of a mess to fix, so it might be easier if I just re-write it from scratch.

EDIT: Here is a script I've shared on a similar topic. Searches all records within a Base, matches a Text field, then updates that Text field. It deals with batch processing too - although it's not perfect - I'm always interested in learning new methods to make it faster or more efficient.

Hopefully this helps!

//Gather all records within a base (50,000). 
const table = base.getTable("Table 1");
const query = await table.selectRecordsAsync({fields: ["Name", "Status", "Animal"], sorts: [ {field: "Name", direction :"asc"}]});

let fishyRecords = query.records.filter( record => record.getCellValueAsString("Animal") === "Fish");

console.log(`${fishyRecords.length} fish records found`)
// @ts-ignore
if (!fishyRecords.length) return

//The update that each record is to receive
let myData = fishyRecords.map( element => ({
    id: element.id,
    name: element.name,
    fields: {
        "Status": { name: "Todo"},
        "Animal" : "Dolphin"
    }
}));

//Divide the updated data into batches of 50 records (due to API hard-limit).
let myBatch = [], size = 50;
while (myData.length > 0) myBatch.push(myData.splice(0, size));

//Stamp the batches with the updated Status into the table (again, 50 records per updateRecordsAsync()).
for (const batch of myBatch) {
  await table.updateRecordsAsync(batch);
}

console.info(`${fishyRecords.length} fish records processed`)

 PS: This will typically timeout if configured to run within an Automation as it will exceed 30 seconds, and will only process a few thousand records within that 30 second period. It will take a few minutes to execute completely within a Scripting App Extension - but will process all the records that match the .filter().

[Alexey_Gusev]

I'm using advanced version that compare not exact match only, but ignore words order, punctuation, alone letters (and 2-letter words). I had to work with names from different sources so I created it to match like 
'JOHN W. DOE' == 'Doe, John'
Also, it requires text field to mark, not checkbox. That's because it not just mark 'it's duplicate', it numbers each set, so you can group by mark field and that's very helpful.
At first, It was painfully slow for >30k records, because I wrote

const others=query.recordIds.filter(id=>(![...valueMap.values()].includes(id)))

, thus creating nested loop inside the loop. After I found the bottleneck, I just changed it to  

const valueSet=[...valueMap.values()]
const others=query.recordIds.filter(id=>(!valueSet.includes(id)))

and it's quite fast - for 40k records ~ 5 secs + time to write marks (depends on how much dupes in table, nearly 100 marks/sec). When I tried to check different scripts, script from airtable examples was a bit faster, but it's not critical in that case.
I just liked other logic approach - to create Map ( value, id ) from all records. Thus, absent IDs will contain set of duplicate values. the rest is to filter all records by this set

const config=input.config({ title:'Advanced Deduper',items:[input.config.table('table',{label:'Select table'}),
input.config.view('myView',{label:'Select view', parentTable:'table'}),
input.config.field('CHECK',{label:'field to CHECK', parentTable:'table'}),
input.config.field('MARK',{label:'field to MARK', parentTable:'table'})]})
const {table,myView,CHECK,MARK}=config
const query=await myView.selectRecordsAsync({fields:[CHECK]})
const norm=r=>r.getCellValueAsString(CHECK).toLowerCase() 
.split(' ').sort().filter(n=>n.length>2).map(m=>m.replace(/W/,'')).join('');
//remove line above for exact match. otherwise it ignores order, punctuation and 1-2 letter words
const valueMap=new Map(query.records.map(rec=>[norm(rec),rec.id]))
const valueSet=[...valueMap.values()]
const others=query.recordIds.filter(id=>(!valueSet.includes(id))) 
const othervals=new Set(others.map(id=>norm(query.getRecord(id))))
const dupes=[...valueMap.keys()].filter(val=>othervals.has(val))
const upd=query.records.filter(r=>dupes.includes(norm(r))).map(u=>({'id':u.id,'fields':{[MARK.name]:dupes.indexOf(norm(u)).toString()}}))
output.inspect(upd.map(u=>query.getRecord(u.id).getCellValue(CHECK))); output.text(`Found: ${upd.length} records`)
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))