Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 05, 2022 02:40 PM
Hi - I am trying to have a script that will take a single row with a linked record column (one which allows multiple entries), grab the data from the field, strip the commas, pivot the data (now 1 value per row), and then insert those rows into another table. For example, the source data:
Source table
Person Project (linked record, multiple)
Bob Proj1, Proj2, Proj3
Jane Proj1, Proj3
Destination table
Person Project (linked record, single)
Bob Proj1
Bob Proj2
Bob Proj3
Jane Proj1
Jane Proj3
I can generally understand existing scripts but am not a dev full time and so am not sure where to start with creating new. Can anyone help?
May 05, 2022 11:23 PM
It sounds like you want a script that creates junction records.
You say that you generally understand existing scripts. Do you know how to identify the record you want to process? Do you feel confident in your ability to get and interpret the cell value of a linked record field? Are you comfortable creating new records with values for a linked record field? If you don’t know how to do any of these things, I recommend working on those sub-skills first.
If you already know how to read and write linked record field data, the algorithm is fairly straightforward.
May 06, 2022 06:58 AM
The larger context here is our reason for having the source table with multiple linked records in a single row is because the source data comes in from a form, and I only want the users to have to do the form just once. However for later use of the information, joining, reporting - it makes more sense for the records to be one-per-row, so I’m trying to automate that.
I understand the logical flow of what to do. I can also look at a script and roughly make out the general direction. But I don’t know the specifics of airtable scripting, properties, commands and so on. I can go work on those subskills but was hoping for examples or someone who had done the same thing because that is obviously quicker.
May 06, 2022 11:47 AM
take one record, for example, values are:
rec.getCellValue(‘Person’)
rec.getCellValue(‘Project’) - array of links
you need to map array of links (Project) and get smth like this
const converse=rec=>rec.getCellValue(‘Project’).map(x=>
[ rec.getCellValue(‘Person’) , x ] )
^^Bob^^ ____________^^ProjX^
// link obect read format has id and name. to write, we need only object with id. so, x should be changed to {id:x.id}
then transfer it to create record format
const create=(el)=>({fields:{‘Title’:el.Title,‘ID’:el.ID }}) //just example from other script
const create=(el)=>({fields:{‘Person’:el[0], ‘Project’:el[1]}})
//link should be an array of object, even if it’s single link, so ‘Project’:[el[1]]
final version:
const sourcetable = base.getTable('source');
const desttable = base.getTable('dest');
const query=await sourcetable.selectRecordsAsync({fields:['Person','Project']});
const converse=rec=>rec.getCellValue('Project').map(x=>[rec.getCellValue('Person'),{id:x.id}])
const create=(el)=>({fields:{'Person':el[0], 'Project':[el[1]]}})
const crt=query.records.flatMap(converse).map(create)
while (crt.length) await desttable.createRecordsAsync(crt.splice(0,50))
Sep 12, 2022 01:00 PM
Thank you for this solution. When I try to use it, I get an error:
“TypeError: Cannot read properties of null (reading ‘map’)
at converse on line 4
at main on line 6”
Any ideas?
Sep 13, 2022 02:16 AM
Hi,
seems like you have empty values in Project field
can’t test, so solution 1 - add question mark
rec.getCellValue('Project')?.map(x=>
if not helps, use solution 2
const crt=query.records.filter(r=>r.getCellValue('Project')).flatMap(converse).map(create)
Sep 15, 2022 10:43 AM
Thanks for the code! Works perfectly for my needs. One question, how do you write a code that will populate the list in the second column? (i.e. not in the primary column?)
Sep 15, 2022 11:31 PM
Hi,
I hope I understand you well and you mean new created records.
Target fields defined in function ‘create’
It takes value pairs [person, link] from previous line
and puts them to fields (element count in array starting from 0):
(el)=>({fields:{'Person':el[0], 'Project':[el[1]]}})
so, if you want to change destination fields, just put here desired names. Number of column doesn’t matter here. I suppose you have computed primary field, otherwise it will be empty.
Sep 16, 2022 12:04 PM
Great! Thanks so much!
Nov 21, 2022 12:09 AM
I know this thread is a couple of months old, but I am having trouble with trying to use this where the Destination Table - ‘Person’ field is a linked field linking back to the Source Table record.
On trying to create the record in the Destination Table, where I would like the ‘Person’ to be linked back to Source Table I get the following error:
j: Can’t create records: invalid cell value for field ‘Person’.
Cell value has invalid format: must be an array.
Linked records field value must be an array of objects with property ‘id’ corresponding to linked record id.
at main on line 7
Could I ask is there a way to link back to the source table is this scenario? Or have I missed the point?