I just finished writing up a script that does something very similar. You may want to make some edits to this as I'm entering the data from my merged records into a separate (already created) field.
What may be incredibly complicated is that your desired outcome has you creating new columns on the fly depending on the number of matching user ID's you'd find. I've found that comma separating my data, and the inserting into a single column is a much easier and lighter lift.
Notes on this script:
This was designed to pull instances matching my keyword and the same report date. If you want to check only for the keyword, remove the current date comparison
For simplicity, I'm inserting the data from my "duplicates" into a new field (not the original column the data came from). For your purposes, you might actually want to create a new record, rather than update the existing record. If you want all data from your record, including the one you're checking against, you'll exclude: keyword.id!=(id). If that is the case, you will then want to change "if (duplicatefilteredRecords.length!=0)" to "if (duplicatefilteredRecords.length!>1)" otherwise, you'll be including records with only one instance of your keyword.
let table = await base.getTable("YOUR TABLE");
let query = await table.getView("VIEW NAME").selectRecordsAsync({fields:[]})
let assetarray = new Array();
let dupearray = new Array();
let updatedupe = new Array();
//loop through all records to combine
for (let count = 0; count < query.records.length; count++) {
//if DUPE is has not been checked, continue
if (dupearray.includes(query.records[count].id) == false) {
//get the asset ID, report date & record ids
let currentkeyword = query.records[count].getCellValueAsString("Asset ID").toLocaleLowerCase().trim();
let currentDate = query.records[count].getCellValue("Report Date")
let id = query.records[count].id
// FILTER ARRAY TO FIND ALL DUPLICATES
let duplicatefilteredRecords = query.records.filter(keyword => {
return keyword.getCellValue('Asset ID').toLocaleLowerCase().trim()==(currentkeyword)
&& keyword.getCellValue("Report Date")==(currentDate)
&& keyword.id!=(id)
})
// CONTINUE IF DUPLICATES COULD BE FOUND
if (duplicatefilteredRecords.length!=0){
let mergeData1Arr =[]
let mergeData2Arr=[]
//LOOP THROUGH ALL DUPLICATES TO PULL CELL DATA FOR MERGE
for (let countduplicates = 0; countduplicates < duplicatefilteredRecords.length; countduplicates++) {
let mergeData1 = duplicatefilteredRecords[countduplicates].getCellValue("CELL VALUE")
let mergeData2 = duplicatefilteredRecords[countduplicates].getCellValue("CELL VALUE")
let dupeID = duplicatefilteredRecords[countduplicates].id
//Creates function to batch update duplicate records
let dupeFunction = {id: dupeID, fields:{"DUPE":true}}
mergeData1Arr.push(mergeData1)
mergeData2Arr.push(mergeData2)
dupearray.push(dupeID)
updatedupe.push(dupeFunction)
}
//aggregates all cell data from loop into single string
let commaSeparatedMerge1= mergeData1Arr.join(", ")
let commaSeparatedMerge2 = mergeData1Arr(", ")
//creates function to update first ID instance with data from duplicate records
let updaterec = {id, fields:{"FIELD 1":commaSeparatedMerge1,"TOTAL INSTANCE FIELD": (duplicatefilteredRecords.length+1), "FIELD 2":commaSeparatedMerge2}}
assetarray.push(updaterec)
//Batch update to check duplicate box
while (updatedupe.length >0) {
await table.updateRecordsAsync(updatedupe.slice(0, 50));
updatedupe = updatedupe.slice(50);}
}
}
}
//batch upddate to insert data from duplicate records
while (assetarray.length > 0) {
await table.updateRecordsAsync(assetarray.slice(0, 50));
assetarray = assetarray.slice(50);
//Requires confirmation to delete duplicates
}
output.markdown(`${dupearray.length} duplicated found`)
let confirmation = await input.textAsync("Ready to delete duplicates? Type y to confirm")
if (confirmation = "y"){
while (dupearray.length>0){
await table.deleteRecordsAsync(dupearray.slice(0, 50));
dupearray = dupearray.slice(50) }
output.markdown ('Records Deleteed')
}
else {
output.text ("No Duplicates Deleted")
}
