Linked records (multiple) -> one-per-row conversion

Topic Labels: Scripting extentions
3339 10
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

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?

10 Replies 10

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.

  • Identify the record to process
  • Read the cell values of the relevant fields
  • Loop over the values in the list to identify the records to create
  • actually submit the request to update the records.
6 - Interface Innovator
6 - Interface Innovator

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.

take one record, for example, values are:
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 {}

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'),{}])
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))
4 - Data Explorer
4 - Data Explorer

Hi @Alexey_Gusev

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?

seems like you have empty values in Project field
can’t test, so solution 1 - add question mark
if not helps, use solution 2

const crt=query.records.filter(r=>r.getCellValue('Project')).flatMap(converse).map(create)

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?)

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.

Great! Thanks so much!

4 - Data Explorer
4 - Data Explorer

Hi @Alexey_Gusev

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?