I’m looking to do the same thing! Copy and paste from a text/single-select/multi-select field to a linked field. I currently have to copy and paste manually, which works perfectly as expected but is inefficient.
Would really appreciate help here as well!
I’m looking to do the same thing! Copy and paste from a text/single-select/multi-select field to a linked field. I currently have to copy and paste manually, which works perfectly as expected but is inefficient.
Would really appreciate help here as well!
I would also love to be able to do this automatically.
Hello,
I saw this post last week but didn’t feel qualified to answer as I am just now beginning to learn JavaScript myself.
I wanted the same thing from a Script about a month ago and had some success so I thought I would share now and hopefully help some people out even though I myself, am no expert.
If I understand the specs correctly, this is the script will allow you to populated a linked field based on a match between two tables:
Hopefully this is clear enough to apply to your base! Please understand that there might be a more efficient way to do this, but I hope it helps.
Good luck!
Hello,
I tried but It doesn’t work. I’m not a developer and I discover the Javascript with this new Airtable Block…
I would like to do this : 
It would help me for many cases. I would greatly appreciate your help !
Hello,
I tried but It doesn’t work. I’m not a developer and I discover the Javascript with this new Airtable Block…
I would like to do this : 
It would help me for many cases. I would greatly appreciate your help !
I’m trying to do the same. Got any new ideas?
I’m trying to do the same. Got any new ideas?
I find a solution and will post it very soon.
I find a solution and will post it very soon.
Same problem… Catherine, any updates on the solution?
Same problem… Catherine, any updates on the solution?
Hello,
I’m stuck on this topic as well. Tried running the script and I think it fails when there isn’t a match, which sometimes happens at least in my use case.
Can somebody help? It’s really quite a big blocker not being able to use a formula as a link?
Cheers,
Alivia
Hello,
I saw this post last week but didn’t feel qualified to answer as I am just now beginning to learn JavaScript myself.
I wanted the same thing from a Script about a month ago and had some success so I thought I would share now and hopefully help some people out even though I myself, am no expert.
If I understand the specs correctly, this is the script will allow you to populated a linked field based on a match between two tables:
Hopefully this is clear enough to apply to your base! Please understand that there might be a more efficient way to do this, but I hope it helps.
Good luck!
Thank you very much.
Finally a simple solution to activate the link between two tables.
Hi, I am trying this solutions, but it seems is depreciated.
Some ideas?
Thanks
Hi, I am trying this solutions, but it seems is depreciated.
Some ideas?
Thanks
It should work (partly), but it has 3 serious disadvantages:
- it loops a single update operation many times, that very likely will block the script execution after 20-30 updates. Unless your pc is not slow enough to fit into limit of updates per second ))
- it does not support multiple links
- it does nested loop for tables, which is ok to match 100x100, but for 30k
x 30k it must perform almost a billion ‘search and compare’ operations (i hope ,databasers will excuse me for simplifying terms and details). It should work a very long time (>10 mins for 10k+ tables) and fry the CPU.
One time I posted here script, written some months ago, that updates multiple records in a batches and supports multiple links, but when I tried it in real work for large tables, it still have significant delay (~15-20 seconds for counting and a bit more for writing)
So I re-writed it with hashmap, reading database discussions here, and input config, to avoid code editing. It does all searches in less than 1 second (because of no need to loop through second table) and all it’s time of work is a time of writing links to table.
You can try to use it
let settings = input.config({
title: 'Vlookup',
description: 'You need to define two tables, fields to set link and link field. Second table/link autodetected',
items:iinput.config.table('tableOne', {label: 'Select first table' }),
input.config.field('joinField',{label: 'Select field to join',parentTable:'tableOne'})]
})
const {tableOne,joinField}=settings
//Define other table and fields
let linkFields = tableOne.fields.filter(f=>f.type.includes('RecordLinks'));
if(!linkFields.length) throw new Error (`No link fields in ${tableOne}`);
let chooseField=(linkFields.length>1)?
await input.buttonsAsync('Choose linkfield:',linkFields.map(f=>f.name)):linkFieldsF0].name
const LINK=tableOne.getField(chooseField);
// @ts-ignore
const SECTABLE=base.getTable(LINK.options?.linkedTableId);
let sameName=SECTABLE.fields.find(f=>f.name===joinField.name);
let fld=(sameName)? sameName.name :await input.buttonsAsync(`Field ${joinField.name} absent in ${SECTABLE}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)
//Read data, define target scope
const queryMain = await tableOne.selectRecordsAsync({fields:ijoinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:iFIELD_TWO]});
const query=queryMain.records.filter(r=>!r.getCellValue(LINK))
output.text(`Total records: ${queryMain.records.length}, empty records: ${query.length}`)
const ask=await input.buttonsAsync(`Update only empty links or update all?`,a'Empty','All'])
const upd=(ask=='All')? queryMain.records : query
//Build map of second table values to boost script perfomance
const val=x=>x.getCellValue(FIELD_TWO)
let valtable=querySec.records.reduce((acc,v)=>acc.set(val(v),a...acc.get(val(v))||(],v.id]),new Map())
const updateLink=(rec,m)=>({id:rec.id,fields:{eLINK.name]:m.map(x=>({'id':x}))}})
//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(rec.getCellValue(joinField))||l]))
while(updates.length) tableOne.updateRecordsAsync(updates.splice(0,50))
Many thanks
What I need is to copy fields inside a unique table, let’s say “tableA”
Then I need to copy field X. ( calculated filed) to field Y ( linked field with same possible values as field X)
How can i perform this with your script? Thanks
It should work (partly), but it has 3 serious disadvantages:
- it loops a single update operation many times, that very likely will block the script execution after 20-30 updates. Unless your pc is not slow enough to fit into limit of updates per second ))
- it does not support multiple links
- it does nested loop for tables, which is ok to match 100x100, but for 30k
x 30k it must perform almost a billion ‘search and compare’ operations (i hope ,databasers will excuse me for simplifying terms and details). It should work a very long time (>10 mins for 10k+ tables) and fry the CPU.
One time I posted here script, written some months ago, that updates multiple records in a batches and supports multiple links, but when I tried it in real work for large tables, it still have significant delay (~15-20 seconds for counting and a bit more for writing)
So I re-writed it with hashmap, reading database discussions here, and input config, to avoid code editing. It does all searches in less than 1 second (because of no need to loop through second table) and all it’s time of work is a time of writing links to table.
You can try to use it
let settings = input.config({
title: 'Vlookup',
description: 'You need to define two tables, fields to set link and link field. Second table/link autodetected',
items:iinput.config.table('tableOne', {label: 'Select first table' }),
input.config.field('joinField',{label: 'Select field to join',parentTable:'tableOne'})]
})
const {tableOne,joinField}=settings
//Define other table and fields
let linkFields = tableOne.fields.filter(f=>f.type.includes('RecordLinks'));
if(!linkFields.length) throw new Error (`No link fields in ${tableOne}`);
let chooseField=(linkFields.length>1)?
await input.buttonsAsync('Choose linkfield:',linkFields.map(f=>f.name)):linkFieldsF0].name
const LINK=tableOne.getField(chooseField);
// @ts-ignore
const SECTABLE=base.getTable(LINK.options?.linkedTableId);
let sameName=SECTABLE.fields.find(f=>f.name===joinField.name);
let fld=(sameName)? sameName.name :await input.buttonsAsync(`Field ${joinField.name} absent in ${SECTABLE}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)
//Read data, define target scope
const queryMain = await tableOne.selectRecordsAsync({fields:ijoinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:iFIELD_TWO]});
const query=queryMain.records.filter(r=>!r.getCellValue(LINK))
output.text(`Total records: ${queryMain.records.length}, empty records: ${query.length}`)
const ask=await input.buttonsAsync(`Update only empty links or update all?`,a'Empty','All'])
const upd=(ask=='All')? queryMain.records : query
//Build map of second table values to boost script perfomance
const val=x=>x.getCellValue(FIELD_TWO)
let valtable=querySec.records.reduce((acc,v)=>acc.set(val(v),a...acc.get(val(v))||(],v.id]),new Map())
const updateLink=(rec,m)=>({id:rec.id,fields:{eLINK.name]:m.map(x=>({'id':x}))}})
//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(rec.getCellValue(joinField))||l]))
while(updates.length) tableOne.updateRecordsAsync(updates.splice(0,50))
Thanks a lot Alexey for your update on the subject.
You are right, the first script is often blocked with the 30 sec max time.
But I tried to use yours without success.
I guess I don’t succeed in adapting your script to my case.
Can you give more explanation ?
Many thanks
What I need is to copy fields inside a unique table, let’s say “tableA”
Then I need to copy field X. ( calculated filed) to field Y ( linked field with same possible values as field X)
How can i perform this with your script? Thanks
You can just copy-paste it there. If you want to do it by script, you need to run it and specify field name
@David_KIERBEL , it’s for your question also.
The value of linked field cell is array (group) of objects (often that group contain only one object). The object looks like:
id: record_id (means record from other table, where it linked to)
name: name of record (The name of record is a value of it’s primary field)
to fill linked record by script, you need to put {id: record_id} (indeed, you should put an array, even if it’s just 1 element. or empty array to clean).
to fill a cell from User interface or via automation, you can paste the name of record, provided it exists (otherwise it will create new record with that name) and it’s unique in primary field (or you can link wrong record. Note that ‘Limit selection to a view’ constraint does not applied for pasting, it’s just for manual selection)
The problem is when you need to link records based on some field, which is not primary in none of two tables
My script supposed to be ready to run without any code editing.
If you have Table A, linked to table B, and you want to fill ‘Link to B’ field, based on some , for example, ‘product_id’ field, which present in both tables, so records with the same id will be linked,
you need to select table A, and select field ‘product_id’. The rest are detected, if possible, or it will ask additional questions.
I used it last two weeks for work and found a bug which prevents link to be filled in large tables. I refactored some procedures, added option to update only empty links (to prevent rebuilding whole pack of links for some minor changes), but the script still doesn’t work. Until I realized that I forget to set await in last line
So, that’s updated version. Please let me know if I still forget something
let settings = input.config({
title: 'Vlookup',
description: 'You need to define two tables, fields to set link and link field. Second table and link will be autodetected',
items:binput.config.table('tableOne', {label: 'Select first table' }),
input.config.field('joinField',{label: 'Select field to join',parentTable:'tableOne'})]
})
const {tableOne,joinField}=settings
//Define other table and fields
let linkFields = tableOne.fields.filter(f=>f.type.includes('RecordLinks'));
if(!linkFields.length) throw new Error (`No link fields in ${tableOne}`);
let chooseField=(linkFields.length>1)?
await input.buttonsAsync('Choose linkfield:',linkFields.map(f=>f.name)):linkFieldsF0].name
const LINK=tableOne.getField(chooseField);
// @ts-ignore
const SECTABLE=base.getTable(LINK.options?.linkedTableId);
let sameName=SECTABLE.fields.find(f=>f.name===joinField.name);
let fld=(sameName)? sameName.name :await input.buttonsAsync(`Field ${joinField.name} absent in ${SECTABLE}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)
//Read data, define target scope
const queryMain = await tableOne.selectRecordsAsync({fields:tjoinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:SFIELD_TWO]});
const val=x=>x.getCellValue(FIELD_TWO)
const jfld=x=>x.getCellValueAsString(joinField)
let valtable=querySec.records.reduce((acc,v)=>acc.set(val(v),s...acc.get(val(v))||a],v.id]),new Map())
const query=queryMain.records.filter(r=>(!r.getCellValue(LINK))&&(valtable.has(jfld(r))))
output.text(`Total records: ${queryMain.records.length}, empty records: ${query.length}`)
const ask=await input.buttonsAsync(`Update only empty links or update all?`,t'Empty','All'])
const upd=(ask=='All')? queryMain.records : query
const updateLink=(rec,m)=>({id:rec.id,fields:{iLINK.name]:m.map(x=>({'id':x}))}})
//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(jfld(rec))||t]))
while(updates.length) await tableOne.updateRecordsAsync(updates.splice(0,50))
Note that you can do the task (set links in 2 tables via some ‘common’ field),
without any code, and even without automation, pure manual approach, I can explain how.
It’s harder than do it via script, especially if ‘common field’ value contain comma or ‘&’.
But I would recommend to try at some time, because using similar method you can find duplicates and their count, or you can re-group table, for example, when you have table of building inhabitants, 250 records for 250 persons, living in 100 flats, but then you need a table per flat.
And doing that, you may quickly understand how linking works in AT, if it’s unclear for you.
You just need to add new field right to the ‘common field’, choose ‘link to’ new table (temporary). And then copypaste whole ‘common field value’ into that new field.
then do the same for the other table (using your new created table). temporary table reveals connection between these two fields, which you can tie through lookup and establish links between your initial tables. Then you just delete all this temporary stuff.
(to count duplicates, use ‘count’ field. to regroup, use rollup arrayunique(values))
You can just copy-paste it there. If you want to do it by script, you need to run it and specify field name
@David_KIERBEL , it’s for your question also.
The value of linked field cell is array (group) of objects (often that group contain only one object). The object looks like:
id: record_id (means record from other table, where it linked to)
name: name of record (The name of record is a value of it’s primary field)
to fill linked record by script, you need to put {id: record_id} (indeed, you should put an array, even if it’s just 1 element. or empty array to clean).
to fill a cell from User interface or via automation, you can paste the name of record, provided it exists (otherwise it will create new record with that name) and it’s unique in primary field (or you can link wrong record. Note that ‘Limit selection to a view’ constraint does not applied for pasting, it’s just for manual selection)
The problem is when you need to link records based on some field, which is not primary in none of two tables
My script supposed to be ready to run without any code editing.
If you have Table A, linked to table B, and you want to fill ‘Link to B’ field, based on some , for example, ‘product_id’ field, which present in both tables, so records with the same id will be linked,
you need to select table A, and select field ‘product_id’. The rest are detected, if possible, or it will ask additional questions.
I used it last two weeks for work and found a bug which prevents link to be filled in large tables. I refactored some procedures, added option to update only empty links (to prevent rebuilding whole pack of links for some minor changes), but the script still doesn’t work. Until I realized that I forget to set await in last line
So, that’s updated version. Please let me know if I still forget something
let settings = input.config({
title: 'Vlookup',
description: 'You need to define two tables, fields to set link and link field. Second table and link will be autodetected',
items:oinput.config.table('tableOne', {label: 'Select first table' }),
input.config.field('joinField',{label: 'Select field to join',parentTable:'tableOne'})]
})
const {tableOne,joinField}=settings
//Define other table and fields
let linkFields = tableOne.fields.filter(f=>f.type.includes('RecordLinks'));
if(!linkFields.length) throw new Error (`No link fields in ${tableOne}`);
let chooseField=(linkFields.length>1)?
await input.buttonsAsync('Choose linkfield:',linkFields.map(f=>f.name)):linkFields.0].name
const LINK=tableOne.getField(chooseField);
// @ts-ignore
const SECTABLE=base.getTable(LINK.options?.linkedTableId);
let sameName=SECTABLE.fields.find(f=>f.name===joinField.name);
let fld=(sameName)? sameName.name :await input.buttonsAsync(`Field ${joinField.name} absent in ${SECTABLE}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)
//Read data, define target scope
const queryMain = await tableOne.selectRecordsAsync({fields:njoinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:LFIELD_TWO]});
const val=x=>x.getCellValue(FIELD_TWO)
const jfld=x=>x.getCellValueAsString(joinField)
let valtable=querySec.records.reduce((acc,v)=>acc.set(val(v),c...acc.get(val(v))||t],v.id]),new Map())
const query=queryMain.records.filter(r=>(!r.getCellValue(LINK))&&(valtable.has(jfld(r))))
output.text(`Total records: ${queryMain.records.length}, empty records: ${query.length}`)
const ask=await input.buttonsAsync(`Update only empty links or update all?`,y'Empty','All'])
const upd=(ask=='All')? queryMain.records : query
const updateLink=(rec,m)=>({id:rec.id,fields:{eLINK.name]:m.map(x=>({'id':x}))}})
//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(jfld(rec))||(]))
while(updates.length) await tableOne.updateRecordsAsync(updates.splice(0,50))
Hello Alexey.
Sorry to bother you again.
I tried to make your script work but I guess I don’t get it.
What should I put for example for ‘tableone’ ?
Should I declare input variable ? Or should I put directly the name of the table.
I tried several things but nothing is working.
With the previous script on this thead, it was quite simple. I just declare the table, the column,…
And then it goes
But as you mentionned already, lots of fail due to time limitation.
Could you post a very concrete example ?

You can just copy-paste it there. If you want to do it by script, you need to run it and specify field name
@David_KIERBEL , it’s for your question also.
The value of linked field cell is array (group) of objects (often that group contain only one object). The object looks like:
id: record_id (means record from other table, where it linked to)
name: name of record (The name of record is a value of it’s primary field)
to fill linked record by script, you need to put {id: record_id} (indeed, you should put an array, even if it’s just 1 element. or empty array to clean).
to fill a cell from User interface or via automation, you can paste the name of record, provided it exists (otherwise it will create new record with that name) and it’s unique in primary field (or you can link wrong record. Note that ‘Limit selection to a view’ constraint does not applied for pasting, it’s just for manual selection)
The problem is when you need to link records based on some field, which is not primary in none of two tables
My script supposed to be ready to run without any code editing.
If you have Table A, linked to table B, and you want to fill ‘Link to B’ field, based on some , for example, ‘product_id’ field, which present in both tables, so records with the same id will be linked,
you need to select table A, and select field ‘product_id’. The rest are detected, if possible, or it will ask additional questions.
I used it last two weeks for work and found a bug which prevents link to be filled in large tables. I refactored some procedures, added option to update only empty links (to prevent rebuilding whole pack of links for some minor changes), but the script still doesn’t work. Until I realized that I forget to set await in last line
So, that’s updated version. Please let me know if I still forget something
let settings = input.config({
title: 'Vlookup',
description: 'You need to define two tables, fields to set link and link field. Second table and link will be autodetected',
items:oinput.config.table('tableOne', {label: 'Select first table' }),
input.config.field('joinField',{label: 'Select field to join',parentTable:'tableOne'})]
})
const {tableOne,joinField}=settings
//Define other table and fields
let linkFields = tableOne.fields.filter(f=>f.type.includes('RecordLinks'));
if(!linkFields.length) throw new Error (`No link fields in ${tableOne}`);
let chooseField=(linkFields.length>1)?
await input.buttonsAsync('Choose linkfield:',linkFields.map(f=>f.name)):linkFields.0].name
const LINK=tableOne.getField(chooseField);
// @ts-ignore
const SECTABLE=base.getTable(LINK.options?.linkedTableId);
let sameName=SECTABLE.fields.find(f=>f.name===joinField.name);
let fld=(sameName)? sameName.name :await input.buttonsAsync(`Field ${joinField.name} absent in ${SECTABLE}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)
//Read data, define target scope
const queryMain = await tableOne.selectRecordsAsync({fields:njoinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:LFIELD_TWO]});
const val=x=>x.getCellValue(FIELD_TWO)
const jfld=x=>x.getCellValueAsString(joinField)
let valtable=querySec.records.reduce((acc,v)=>acc.set(val(v),c...acc.get(val(v))||t],v.id]),new Map())
const query=queryMain.records.filter(r=>(!r.getCellValue(LINK))&&(valtable.has(jfld(r))))
output.text(`Total records: ${queryMain.records.length}, empty records: ${query.length}`)
const ask=await input.buttonsAsync(`Update only empty links or update all?`,y'Empty','All'])
const upd=(ask=='All')? queryMain.records : query
const updateLink=(rec,m)=>({id:rec.id,fields:{eLINK.name]:m.map(x=>({'id':x}))}})
//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(jfld(rec))||(]))
while(updates.length) await tableOne.updateRecordsAsync(updates.splice(0,50))
Hooo, ok, I got it. You launch the script from a button, directly in the table.
Sorry to say it like that but in this case, as you describe it in your note, you can just copy and paste the colomn value you want to link on you link field column, no need for a script.
But what I want to do is to execute the script automatically with a scheduled automation. To make the link automatically, no human intervention.