I need help with script to merge Multiple rows with same "User ID".

[SHUG]

I need help with script (or other way?) to merge Multiple rows with same "User ID". These aren't duplicate rows. They have 2 fields in them that are different in each of the rows with the same User ID but need to be merged to one row. Essentially every 4 rows will have the same User ID that needs to be merged like this. Examples below.
Example of what I have:

User ID	Question	Answer
Dave	Do you like Cats	No
Dave	Do you like Dogs	No
Dave	Do you like Hamsters	Yes
Dave	Do you like Monkeys	Yes

Example of what I Need:

User ID	Question 1	Answer 1	Question 2	Answer 2	Question 3	Answer 3	Question 4	Answer 4
Dave	Do... Cats?	No	Do…Dogs?	No	Do…Hamsters?	Yes	Do…Monkeys?	Yes

[r0anne0h · Accepted solution]

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")
}

[SHUG]

Thanks for this! We were able to tweak to fit what we needed.

[r0anne0h]

Awesome! Glad to hear it!

[Alexey_Gusev]

Hi,
Usually it can be done by duplicating ID field and turning it to the linked to new table. But then you need to add many new fields with lookups/rollups, and with script it much easier. I will use it as well. 
And for those who might be interested here is my script doing the vice versa

//Read settings
let settings = input.config({title: 'Transpose table',
items:  [input.config.table('tTable', {label: 'Select table' }),
  input.config.view('tView',{label: 'Select view',parentTable:'tTable'}),  
  input.config.text('tName',{label:'Name of transposed table'}),
  input.config.text('tWild',{label:'Wildcard for fields to transpose',description:
  `Example: if you want to transpose fields "Agent 1", "Agent 3", "Agent 4", input "agent". Case insensitive.
   You can put several wildcards, separated by comma.`
  })]
})
const {tTable,tView,tName,tWild}=settings

//Find fields to transpose
const primary=tTable.fields[0].name
const tWilds=tWild.split(',').map(w=>w.toUpperCase())
const wildCheck=fname=>tWilds.some(w=>fname.name.toUpperCase().includes(w))
let tFields=tTable.fields.filter(wildCheck).filter(f=>f.name!=primary)
const fNames=arr=>arr.map(a=>a.name).filter(n=>n!=primary)
let chosen=fNames(tFields)
console.log(`These fields will be transposed: ${chosen.join(', ')}`)

//Ask for left side
let ask=''; let leftSide=[primary]
let restFields=fNames(tTable.fields).filter(t=>!chosen.includes(t))
restFields.unshift('All OK');
while (ask!='All OK') {
output.text('Current left side: '+leftSide.join(', '));
ask=restFields.length==1? 'All OK':await input.buttonsAsync('Choose left side fields: ', restFields)
leftSide.push(restFields.splice(restFields.indexOf(ask),1)[0]);
output.clear; output.text('==================')}
leftSide.pop() // put out All OK from fieldlist

//Read and transform data
const query=await tView.selectRecordsAsync({fields:[...leftSide,...chosen]})
output.text(`Total records: ${query.records.length} and ${chosen.length} fields to transpose`)
const leftpart=r=>leftSide.map(f=>[f,r.getCellValueAsString(f)])
const trans=r=>chosen.map(f=>({fields:Object.fromEntries([[newCol,f],
[newVal,r.getCellValueAsString(f)],...leftpart(r),[newLink,[{id:r.id}]]])}))

//Create new table
const newLink='Link_'+tTable.name; 
const newCol=tWilds[0]; const newVal=newCol+'_value'
const crtField=n=>({'name':n,'type':'singleLineText'})
const newflds=[...leftSide,newCol,newVal].map(crtField)
newflds.push({name:newLink,type:'multipleRecordLinks',options:{linkedTableId:tTable.id}})
// @TS-ignore
const newID=await base.createTableAsync(tName,newflds)
const newTab=base.getTable(newID)

//fill new table with transposed data
const crt=query.records.flatMap(trans)
const total=query.records.length*chosen.length
output.text(`Table ${newTab.name} will be fiilled with ${total} records`)
console.log(crt)
const go=await input.buttonsAsync('Press GO to start',['GO','Quit'])
if(go=='GO') while (crt.length) await newTab.createRecordsAsync(crt.splice(0,50))
output.text(go=='Quit'? 'upload skipped...':'Done') 

[mrrambles]

This is awesome. I have a slightly different use case, and I am hoping to leverage your script to adapt to my use case.

Essentially I have a form that collects one or more responses, and if more than one response for a specific project on a given date, I want to aggregate those responses into a new record. 

An over simplified example is this:

Tabe A structure - 

Field1: Project Name, Field2: Created Date, Field 3, Response Data, Field 4, 2nd Response Data

If I have only one response for any project on a given date, then no need to aggregate, though I could still run automation with no ill effect of course.

I can provide more specific details if that's helpful, but if you have some suggestions I would greatly appreciate any insight you'd care to share!

But if I have 2 or more people submit for a project on a given date, I want to combine their response data into a new record. It can be in the same table, or in another table.