Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 01, 2020 08:12 AM
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 -
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
Jun 20, 2022 07:02 AM
Thank you very much.
Finally a simple solution to activate the link between two tables.
Aug 28, 2022 04:16 AM
Hi, I am trying this solutions, but it seems is depreciated.
Some ideas?
Thanks
Aug 28, 2022 07:38 AM
It should work (partly), but it has 3 serious disadvantages:
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))
Aug 28, 2022 11:44 AM
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
Sep 13, 2022 04:25 AM
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 ?
Sep 13, 2022 09:33 AM
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))
Sep 13, 2022 10:03 AM
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))
Oct 03, 2022 10:06 AM
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 ?
Oct 03, 2022 10:26 AM
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.