Skip to main content

I’m currently running an automation script to flatten a source table into multiple rows. My source table has 1 row per company and lots of start/end date columns per phases of a project but I need to transform into a new table (with multiple rows per project phase) in order to use the timeline view correctly. I’ve been amending the “Table Optimiser” script that appears in extensions to make an automated version and have got this far…



let sourceTableStr = "My Source Table"


let destinationTableStr = "My Destination Table"


let fieldsToFlatten = "Date 1 Start,Date 2 Start".split(",") 


let fieldNameField = "Name" 


let dataField = "Date Start"


let dataFieldEnd = "Date End"


let linkedField = "Companies"





let sourceTable = base.getTable(sourceTableStr)


let destTable = base.getTable(destinationTableStr)


let query = await sourceTable.selectRecordsAsync({fieldsToFlatten: o'Company Name','Date 1 Start','Date 1 End','Date 2 Start','Date 2 End']});


let datePairs = {'Date 1 Start':'Date 1 End','Date 2 Start':'Date 2 End'}





let records = query.records;


let arr = l];


for (let field of fieldsToFlatten) {





    arr.push(


        records


            .filter((record) => record.getCellValue(field))


            .map((record) =>


                Array.isArray(record.getCellValue(field))


                    ? record.getCellValue(field).map((element) => ({


                            fieldsToFlatten: {


                                 linkedField]: erecord],


                                 dataField]: telement],


                                 fieldNameField]: field,


                                 dataFieldEnd]: ielement]


                            },


                        }))


                    : {


                            fieldsToFlatten: {


                                 linkedField]: erecord],


                                 dataField]: record.getCellValue(field),


                                 fieldNameField]: "(" + record.getCellValue('Company Name') + ") " + field,


                                 dataFieldEnd]: record.getCellValue(datePairs(field]) 


                                


                            },


                        }


            )


    );


}





let recordsToCreate = arr.flat();


const numRecordsBeingCreated = recordsToCreate.length;


while (recordsToCreate.length > 0) {


    await destTable.createRecordsAsync(recordsToCreate.slice(0, 50));


    recordsToCreate = recordsToCreate.slice(50);


}





console.log(`Complete! Created ${numRecordsBeingCreated} records.`);


It works perfectly the first time (when the destination table is empty and needs to be fully loaded), but I want to run as an automation any time data in the source table changes and so now I need to check each record in the source table to see if it exists in the destination table - if it exists I need to update instead of insert a new record.



Apart from not knowing the most elegant way to do this, if I switch from my current approach of updating 50 records at a time, the script takes longer than 30 seconds to run and so fails. So it seems like I need to keep the batch update (instead of iterating through each record individually) and instead filter my “arr” array to only include records that don’t exist in the destination table.



Has anyone come across a similar problem and solved it using automation scripts? Thanks very much for any thoughts at all!

e.g. can I simply amend the line…



.filter((record) => record.getCellValue(field))



to match on a condition based on the existence of a record in the destination table?

you can check script, that I wrote several months ago with the same purpose. turn H-table to new V-table. transpose values of some field group ‘fld1’, ‘fld2’,… ‘fldX’ into X records with 2 fields ‘fld’ and ‘num’.


“left side” is the static fields, which then added, same as link from new to old table records. nSO,MS] - i think MS is ‘fld’ and ‘SO’ - it’s new name in v_table.


it’s working, but you need to adjust names


feel free to use, but I can’t promise any support))



// Table transpose. Galex 2022


const H_TABLE = base.getTable('horizont table');


const V_TABLE = base.getTable('vert table');


const LS=b'left side','left_','side'];


const iSO,MS]=f'V_table_column Info','Horizont_row '];


const num=n=>Number(n.slice(MS.length-n.length));


const onum=own=>own.name.toString();


const OWNERS=H_TABLE.fields.filter(f=>f.name.includes(MS)).map(onum).sort((x,y)=>num(x)-num(y));


const leftside=rec=LS.map(f=>>f,rec.getCellValue(f)]);


const owners=rec=>OWNERS.map(o=>rec.getCellValue(o)).filter(n=>n);


const create=(rc)=>owners(rc).map((ow,ix)=>({fields:Object.


fromEntries({...leftside(rc),rSO,ow],/'num',ix],i'Link',l{id:rc.id}]]])}))


const query = await H_TABLE.selectRecordsAsync({fields:H_TABLE.fields});


const crt=query.records.flatMap(create);


while(crt.length) await V_TABLE.createRecordsAsync(crt.splice(0,50))

you can check script, that I wrote several months ago with the same purpose. turn H-table to new V-table. transpose values of some field group ‘fld1’, ‘fld2’,… ‘fldX’ into X records with 2 fields ‘fld’ and ‘num’.


“left side” is the static fields, which then added, same as link from new to old table records. nSO,MS] - i think MS is ‘fld’ and ‘SO’ - it’s new name in v_table.


it’s working, but you need to adjust names


feel free to use, but I can’t promise any support))



// Table transpose. Galex 2022


const H_TABLE = base.getTable('horizont table');


const V_TABLE = base.getTable('vert table');


const LS=b'left side','left_','side'];


const iSO,MS]=f'V_table_column Info','Horizont_row '];


const num=n=>Number(n.slice(MS.length-n.length));


const onum=own=>own.name.toString();


const OWNERS=H_TABLE.fields.filter(f=>f.name.includes(MS)).map(onum).sort((x,y)=>num(x)-num(y));


const leftside=rec=LS.map(f=>>f,rec.getCellValue(f)]);


const owners=rec=>OWNERS.map(o=>rec.getCellValue(o)).filter(n=>n);


const create=(rc)=>owners(rc).map((ow,ix)=>({fields:Object.


fromEntries({...leftside(rc),rSO,ow],/'num',ix],i'Link',l{id:rc.id}]]])}))


const query = await H_TABLE.selectRecordsAsync({fields:H_TABLE.fields});


const crt=query.records.flatMap(create);


while(crt.length) await V_TABLE.createRecordsAsync(crt.splice(0,50))



Thanks for providing this but I’m really struggling to follow the code or your explanation as to which values to replace. E.g. where do I specify which columns in the source table should generate the vertical table rows?



Thanks for providing this but I’m really struggling to follow the code or your explanation as to which values to replace. E.g. where do I specify which columns in the source table should generate the vertical table rows?


Sorry, I didn’t catch it first time. You have no problem to transpose, but you need to sync changes then. Then my script is useless for you.



I’m surprised that script engine doesn’t argue against unknown property ‘fieldsToFlatten’



About your goal - you can link these tables, i mean source record to 2 destination records. apart from lookups and other stuff, you can track ‘when record matches condition’ in both tables, if link field is empty.


There are a lot of possible scenarios. Use that result of ‘Find records’ can be put to link field. Also, that when you change something in source, it might be be reflected in both ‘child’ records in destination by links and lookups.

Reply


Terms & Conditions