Script Request: Copy the value of a field to a Linked field in a table to perform a lookup in another table

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!

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 find a solution and will post it very soon.

Same problem… Catherine, any updates on the solution?

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!

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:[input.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:[joinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:[FIELD_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?`,['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),[...acc.get(val(v))||[],v.id]),new Map())
const updateLink=(rec,m)=>({id:rec.id,fields:{[LINK.name]:m.map(x=>({'id':x}))}})

//Process and write
let updates=upd.map(rec=>updateLink(rec,valtable.get(rec.getCellValue(joinField))||[]))
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

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:[input.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:[joinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:[FIELD_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),[...acc.get(val(v))||[],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?`,['Empty','All'])
const upd=(ask=='All')? queryMain.records : query
const updateLink=(rec,m)=>({id:rec.id,fields:{[LINK.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))

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:[input.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:[joinField,LINK]});
let querySec = await SECTABLE.selectRecordsAsync({fields:[FIELD_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),[...acc.get(val(v))||[],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?`,['Empty','All'])
const upd=(ask=='All')? queryMain.records : query
const updateLink=(rec,m)=>({id:rec.id,fields:{[LINK.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))