Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

4315 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Doug_Kaufman
5 - Automation Enthusiast
5 - Automation Enthusiast

Purpose:

In a table where the field is a “LINKED to record” field.

Many times the value for the LINKED field is a value in another text or formula field.

A script that can take the value of a string field, and insert that value to the LINKED To field so that the lookup is somewhat automatic. Currently the only way to do this is to manually do a copy/paste from one field to the LINKED Field.

I believe what would need to be done is -

  • Copy the table field from to a variable
  • Do a lookup in the LINKED Table
  • Insert the valid lookup into the LINKED TO Field
  • If there is an invalid look - then don’t display anything

ie.

Master Table
Phone # Link to Contacts via Phone Name Lookup
111-111-1111 111-111-1111 Abe Lincoln (This is the desired result)

Lookup Table
Phone # Name
111-111-1111 Abe Lincoln

18 Replies 18

Thank you very much.
Finally a simple solution to activate the link between two tables.

Jose_C
6 - Interface Innovator
6 - Interface Innovator

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))
Jose_C
6 - Interface Innovator
6 - Interface Innovator

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

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 ?

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

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

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 :slightly_smiling_face:
But as you mentionned already, lots of fail due to time limitation.

Could you post a very concrete example ?
Airtable example

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.