Hi everyone,
I've two tables with two type of granularity, for instance Table Name and Table Sport, and I would like to create a table with one line equal one name and one sport. I show you this juste below, for the moment I have
In my first table one record = one name
Name 1 - Tennis Rugby Football
Name 2 - Football
In my second one record = one sport
Tennis - Name 1
Rugby - Name 1
Football - Name 1 Name 2
I would like to create a third table NameSport where one record = one name and one sport like this :
Tennis - Name 1
Rugby - Name 1
Football - Name 1
Football - Name 2
I created an automation, when a new record enter a view in the table name, it runs a script, to create in the third table the good granularity. I inspire myself from this community message but it takes the all table and I would like only the triggered record. I created an input variable with the record id, but it's still inserting me in my third table the number of record of the source table with each time the good automation triggered record. Here's my script :
const sourcetable = base.getTable('Name');
const desttable = base.getTable('NameSport');
const config = input.config();
const recordId = config.recordId
const query=await sourcetable.selectRecordsAsync({fields:['name','sport']});
const record = query.getRecord(recordId)
const converse=rec=>record.getCellValue('sport').map(x=>[record.getCellValue('name'),{id:x.id}])
const create=(el)=>({fields:{'name':el[0], 'sport':[el[1]]}})
const crt=query.records.flatMap(converse).map(create)
while (crt.length) await desttable.createRecordsAsync(crt.splice(0,50))