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