Skip to main content

I'm fairly new to JS and I'd love feedback if this script is the most effective. I'm trying to link records between tables, and I know it's not a simple copy-paste.

I have two tables: enrollments (students) and courses.

Enrollments has the fields:

  • Course ID: integer
  • Course ID Link: link field to the Courses table

The integers directly correspond to a 'Canvas Course ID' in the Courses table. Manually, I can copy from one column to the next to make the link.

From what I've pieced together, you need to query to find the AT recordID for that matching integer. Here is how I'm approaching this:

let enrollTable = base.getTable('Enrollments');
let enrollList = await enrollTable.selectRecordsAsync({fields: ['Course ID']})

for (let student of enrollList.records) {
//find the matching Course from Courses table
let coursesTable = base.getTable('Courses');
let courseQuery = await coursesTable.selectRecordsAsync({fields: ['Canvas Course ID']});
let filteredTerms = courseQuery.records.filter((record) => {
let name = record.getCellValue('Canvas Course ID');
return name == student.getCellValue('Course ID')
});

//link matching Course record into Student record
for (let course of filteredTerms) {
await enrollTable.updateRecordAsync(student, {
'Course ID Link': [{id: course.id}]
})
}
}

This is a very slow script. It takes about 0.5 seconds per record, which isn't feasible when I have thousands of students. 

Is there a different approach to this? Thank you.

Hi, 
is  'Canvas Course ID ' unique?
is  'Canvas Course ID' the primary field of Courses table?
if yes, do it via simple copy-paste

there are a lot of ways to do it with or without scripting
problem in your script is you are using single update inside the double loop

it's ok for 10-15 updates, but for thousands it doesn't work,
build array of multiple updates at first, with

updateRecordsAsync, not updateRecordAsync

it's done within a few seconds, maybe less than one

and then apply it to the table.
example script

//Set table and field names
const mainTable = base.getTable('Orders');
const secondTable=base.getTable('Product');
const [mainField,secField,linkField]=['Barcode','item.barcode','Product']
//Define functions
const linkObject=rec=>({id:rec.id})
const updateLink=(ord,m)=>({id:ord.id,fields:{[linkField]:m.map(linkObject)}})
const compare=(o,p)=>(p.getCellValue(secField)===o.getCellValue(mainField))
const findMatches=order=>products.records.filter(prod=>compare(order,prod))
//read data
const orders = await mainTable.selectRecordsAsync({fields:[mainField]});
const products = await secondTable.selectRecordsAsync({fields:[secField]});
//Process and write
const updates=orders.records.map(ord=>updateLink(ord,findMatches(ord)))
while(updates.length) await mainTable.updateRecordsAsync(updates.splice(0,50))

 

btw, if the script still needs much time to process, when you have tens thousands of records, I improved it by preparing table [value, id] one time outside the loop, and then using it inside loop, that speeds up the process. 
also, "UI" added so you don't need to put table and field names in code
Note: Select All for first run, or Empty to 'refresh' links, when you already have filled links, but more students & courses were added to the table

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'}),
input.config.select('byviews',{label: 'Do you need to select views?',options:[
{label:'NO (default). Processing full tables',value:'no'},{value:'YES'}]})]
})
const {tableOne,joinField,byviews}=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.name}.Choose:`,
SECTABLE.fields.filter(f=>!f.type.includes('RecordLinks')).map(f=>f.name));
const FIELD_TWO=SECTABLE.getField(fld)

//Read data, define target scope
let queryMain; let querySec;
if (byviews==='no') {
queryMain = await tableOne.selectRecordsAsync({fields:[joinField,LINK]});
querySec = await SECTABLE.selectRecordsAsync({fields:[FIELD_TWO]})
} else {
const viewOne = await input.viewAsync(`Select view for table ${tableOne.name}:`,tableOne)
const viewTwo = await input.viewAsync(`Select view for table ${SECTABLE.name}:`,SECTABLE)
queryMain = await viewOne.selectRecordsAsync({fields:[joinField,LINK]})
querySec = await viewTwo.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
output.inspect(queryMain)
output.inspect(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))





Thank you. I'll play around with this and see if it works out. 

Reply


Terms & Conditions