Update record for the Link to another record field

[Dany_Chepenko]

Hi all,

I came up with the following design for the internal BI system.

The first table is used to store the payments. (Payments table)

I also need to calculate the monthly sales statistics for each sales representative along with their quota (a predefined value that differed from month to month and added manually each time)
For that purpose, I have an additional table. (AM table)

The combination of (sales_rep, month, year) creates the unique id for the AM table and the linkage between two tables works very smoothly.

However, I believe there is room for improvement since I have to Link unique-id-am field to AM Quota manually every time.
I just drag the cell to AM Quota as you usually do when copying the column.
I want to automate this process and write a script that will perform this action for me in the add new row event.

I thought of doing something like that, but obviously AM Quota doesn’t allow me to do that due to the conflicts of type.

let table = base.getTable("Payments");
let query = await table.selectRecordsAsync();
for (let record of query.records) {
    let am_id = record.getCellValue("unique-id-am")
    let am_quota = record.getCellValue("AM Quota")
    if (am_quota === null) {

        await table.updateRecordAsync(record, {
             'AM Quota': am_id
        })
    }
}

It seems there should be a search stage before pushing new values, but seeking advice from the community about that.

[Dominik_Bosnjak · Accepted solution]

You want the linking to happen only when a new row is created? An automation would be a better way to go instead of pushing buttons indefinitely.

Especially since it doesn’t sound like you’re doing that much interlinking right now, so using up your monthly quota shouldn’t be a concern. But if that’s not the case, you should reconsider your approach outlined above; updating a single record at a time takes forever, and from what I’ve gathered, you don’t already have a way to glean record ids across different tables?

As for the code you’ve posted, we can do imperative programming more efficiently by declaring a temporary array above that for… of loop of yours, then instead of updating each record as we go along, we’d do something like

//create what you need to do the prepwork;
//a simple array would do in this instance, let's call it 'cache'
    let cache = []
    let am_id = record.getCellValueAsString("unique-id-am")
    let am_quota = record.getCellValue("AM Quota")
    if (!am_quota){ cache.push(record.id, am_id.slice(0,am_id.length-5))} //I'm also assuming all of your unique-ids have exactly five digits appended to them from the right

Afterwards, you’d use that array to search through the other table for a partial match based on that am_id we’ve lifted.

let cache2 = [...cache] // always be making copies of your objects, it'll make debugging easier and JS garbage collection can take it
let q = await base.getTable('AM Quota').selectRecordsAsync()
for (let record of q.records){
//assuming those people's names are in the primary table field, otherwise just use the getCellValue method again

let check = cache.indexOf(record.name)
if(check!==-1){
cache2[check] = {id:record.id,name:record.name}
}

So, build an array of your records to update from here and you’re done, use the following syntax to handle 50 updates at a time instead of doing them one by one:

while(cache3.length) await base.getTable('YourFirstTableName').updateRecordsAsync(cache3.splice(0,50))

sidenote: while(x.length) is the same as writing while( x.length > 0 ){ … }, used to be a risky practice, nowadays even works in Safari for iOS

Cache3 being your final package in this example.

There’s probably like 10 easier ways to accomplish this but not enough info to speculate and tbh it sounds like you pretty much had things under control even before getting into the code itself.

But if you’ve had enough of it by now, then going the Automation route means getting the record.id way easier than this, just click your way through the “when record created/updated” condition and you’ll be golden in no time.

[Dany_Chepenko]

Hey Dominik,

Thanks, I have managed to implement what I need.
I have slightly modified your code, and have tested only per single account manager, but at least for now, I see the direction.

let payments_table = base.getTable("Payments");
let query = await payments_table.selectRecordsAsync();
var cache = []
for (let record of query.records) {
    let am_id = record.getCellValueAsString("unique-id-am")
    let am_quota = record.getCellValue("AM Quota")
    if (am_id == "Alexandra Ermoshina72021") {
        cache.push({id: record.id, name:record.name})
    }
}

let am_table = base.getTable('AM Quota')
let q = await am_table.selectRecordsAsync()
for (let record of q.records) {
    if (record.name == "Alexandra Ermoshina72021") {
        await am_table.updateRecordAsync(record, {
            Payments:cache
        })
    }
}

I get your ideas with cache, and I’ll definitely apply it as a next improvement cycle.

[Dominik_Bosnjak]

Great to hear, I’ve cleaned up my original example a bit in case it also helps clue in someone in the future.

But yeah, that’s the general idea with most Airtable record-updating scripts - you build out an array of modifications you want to perform and then skid down whatever API limit applies to your use case (50 updates/creations per request, in this instance).