Simple filter script keeps timing out?

[Avana_Vana]

UPDATE: See my last post for the ultimate solution. I came up with some inadequate solutions earlier in the post, so save yourself some time if you are looking up how to generate random items from a linked field.

EDIT: to make this more clear for future readers—I was trying to do this in an automation script. It worked as a script app in Airtable, though my final script below is much more performant.

I make heavy use of scripting in Airtable, as well as the API, and I am pretty comfortable with using both, but this simple script is kicking my butt for some reason… it either hangs after running for 3000ms CPU time, or quits with an “unexpected error”. At one point it also threw a “syntax error”, even though there is no syntax error, and that error went away on its own without me changing anything.

AFAIK there is no way to ‘select’ records with a filter in Airtable Scripts, before fetching them all, so you have to select the whole friggin’ table (even if it’s 6000 records), and then use Javascript’s native Array.filter() method to get a subset of the whole table. As you can see, what I want to do here in this script is:

1. Cycle through each of 17 or so “topics”.
2. For each of those topics, generate a subset of “videos” whose topic link field id matches the current topic’s id.
3. Pick a random video from the subset (by using the subset of videos and selecting a random index in that array, generated from that’ array’s length).
4. Update the linked record field “Random Video” on the current topic with the random video from the previous step.

    const topics = base.getTable('Topics');
    const videos = base.getTable('Videos');

    const { records: queryTopics } = await topics.selectRecordsAsync();
    const { records: queryVideos } = await videos.selectRecordsAsync();

    queryTopics.forEach(topic => {
         const topicVideos = queryVideos.filter(video => video.getCellValue('Topic Link')[0].id === topic.id);
         const randomVideo = topicVideos[Math.floor(Math.random() * topicVideos.length)];

         await topics.updateRecordAsync(topic.id, {
            'Random Video': [{ id: randomVideo.id }]
         });
    });

Any ideas? This seems exceptionally simple, and from what I understand, unlike the API, Airtable Scripts already have the data loaded in memory—the await call is supposedly just “formal”, so it should just be like running a simple Array.filter() method… I don’t understand why this keeps getting hung up.

[Bill_French]

Avana_Vana:

What is giving me problems is when it is run as an automation.

That would have been good to know 20 hours ago. :winking_face:

Airtable has three [internal[ scripting environments so it’s always helpful to be clear about the runtime climate.

[Avana_Vana]

Ok, sure. But they all use the same language, why should the runtime matter, and shouldn’t any limitations be made clear? Are there limitations I should know about with automations? The expectation as a consumer is that any script I write in a script app could be automated… nevertheless your help has been greatly appreciated.

[Bill_French]

Avana_Vana:

why should the runtime matter, and shouldn’t any limitations be made clear?

I’m probably not the guy to ask because most of my work with Airtable is in NodeJS on other systems. However, imagine an automation action with an input element or output elements - that won’t work.

Avana_Vana:

The expectation as a consumer is that any script I write in a script app could be automated…

Well, it can be automated, but “automation” – by definition – must not contain blocking code, right? It also probably shouldn’t perform any sort of inefficient enumeration that would likely cross some arbitrary time boundaries, otherwise, a “consumer” could take the platform down.

I have a hunch you were seeing Airtable’s defence mechanisms reacting to protect its environment.

[Avana_Vana]

Both of your suggestions were great.

I incorporated both of yours and @Bill.French 's original tip to use updateRecordsAsync() , and now this script is lightning-fast. I may share it with others here, because I suspect a script that chooses a random record from one table, to a linked field of another table performs a fairly useful function for many. (eg. get to know a random employee, word of the day, etc).

Unfortunately it still doesn’t work as an automation. Lucky for me I don’t need it to change that often, and I can manually run it every once in a while, but it would be nice to have it run by itself, or more ideally, once a day (wish this was a trigger option). @Bill.French if it’s so fast now (nearly instantaneous in human terms), why should it trigger Airtable’s defense mechanisms? It’s not querying more data than the view loads.

let topics = base.getTable('Topics');
let videos = base.getTable('Videos');
let allOnlineVideos = videos.getView("All Online Videos");

let { records: videosQuery } = await allOnlineVideos.selectRecordsAsync({ fields: ['Topic Link'] });

let topicVideos = {}, randomVideos = [];

for (let video of videosQuery) {
    let topicId = video.getCellValue('Topic Link')[0].id;
    if (!topicVideos[topicId]) topicVideos[topicId] = [];
    topicVideos[topicId].push(video.id);
}

for (let [topic, videoIds] of Object.entries(topicVideos)) {
    let randomVideoId = videoIds[Math.floor(Math.random() * videoIds.length)];
    
    randomVideos.push({
        id: topic,
        fields: {
            'Random Video': [{ id: randomVideoId }]
        }
    });
}

await topics.updateRecordsAsync(randomVideos);

[kuovonne]

Avana_Vana:

Are there limitations I should know about with automations?

There are several limitations with automations. You can find the script specific ones in this support article. There are other automation limitations described here.

The timeout after 3000ms CPU time is one of the limitations described in that support article. This is different from running in other scripting situations because the script runs on Airtable’s servers versus running on your local computer. Airtable doesn’t care how much of your own CUP time you take, but they need to protect their server CPU time to ensure that the service remains running for everyone.

[Avana_Vana]

Ok, that makes sense. Of course an automation would need to run on their servers. I am a little surprised now that I have got the script running so quickly due to both yours and Bill’s suggestions, that it still doesn’t work, though.

[kuovonne]

Avana_Vana:

Unfortunately it still doesn’t work as an automation. Lucky for me I don’t need it to change that often, and I can manually run it every once in a while, but it would be nice to have it run by itself, or more ideally, once a day (wish this was a trigger option).

Automations need to be activated by a triggering record. In order to run an automation once a day, you need to have a control record that triggers the automation once per day. I don’t have time for a writeup now, but this thread has more info and there are other threads on the same topic.

[kuovonne]

Avana_Vana:

that it still doesn’t work, though.

What error are you getting?

[Avana_Vana]

@kuovonne - you know actually I made a mistake. It seems to be working now with all your combined suggestions. Just have to figure out the daily trigger now.

Thanks so much @kuovonne and @Bill.French.

PS: I could only select one ‘Solution’ to this problem, though I attribute both of your responses to the ultimate resolution of this issue. I suspect, however, that the biggest performance gains were from @kuovonne’s suggestions, and I am not sure which was the one that really helped the most, though I suspect her second suggestion not to loop through 5711 records 17 times was the one that really did it, though filtering the initial selectRecordsAsync() call by a single field could have been of great benefit in this case because each record has many, many fields.

[Bill_French]

Good to hear its working.

Avana_Vana:

if it’s so fast now (nearly instantaneous in human terms), why should it trigger Airtable’s defense mechanisms?

I was referring to the timeouts you said you were experiencing. You weren’t getting good error feedback and it didn’t make sense so I surmised it could have been the platform shrugging off process as untenable.