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.

[Avana_Vana]

So the way I solved the daily trigger at midnight is as follows:

1. Add an autonumber column called ‘ID’ to the table ‘Topics’.
2. Add a number column called ‘First ID’ to the table ‘Topics’.
3. Create an automation on that table that keeps the column ‘First ID’ updated to always show the ‘ID’ column value of the first record in ‘Topics’.

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

let { records: topicsQuery } = await topics.selectRecordsAsync({ fields: ['ID']});

let ids = [], firstIds = [];

for (let topic of topicsQuery) {
    ids.push(topic.getCellValue('ID'));
}

for (let topic of topicsQuery) {
    firstIds.push({
        id: topic.id,
        fields: {
            'First ID': ids[0]
        }
    });
}

await topics.updateRecordsAsync(firstIds);

4. Add a formula column to the table ‘Topics’ called ‘Trigger’ (formula = IF({ID}={First ID}, HOUR(NOW()), 0))
5. Set up the trigger for the random video script to watch when a record in Topics has a ‘Trigger’ column value that equals 5 (midnight EST, based on the output of step 4).

This all ensures that the script will only trigger once at midnight, by only allowing the first item to ever trip the trigger, because if the formula HOUR(NOW()) is applied without a condition, all records would match at midnight, causing the script to run once for each record. Steps 1-3 set up a dynamic way to calculate if any row equals the first row, even if you add and remove rows, re-order, etc.

Airtable will recalculate the value of NOW() periodically, but frequently enough that this works for triggering a script on an hourly basis. Less than that and one would have to experiment (for example, Airtable doesn’t globally update the value of NOW() frequently enough to set up a trigger like this for MINUTE(NOW()).)

Notes to future readers:
1.) You don’t need to do steps 1-3 to set up this trigger, for example you can find the record ID of the first record and use the formula IF(RECORD_ID()="recXXXXXXXXXXXXX", HOUR(NOW()),0), or you can add the Autonumber column and then do IF({Autonumber}=1, HOUR(NOW()), 0) or somesuch, but if you do not use any condition, your trigger will run once a day, for as many times as there are records in the table you’re watching. Also, if you don’t do steps 1-3, then when you remove rows or the manually entered values no longer match your condition, your trigger will fail. Thus I recommend to do steps 1-3 to ensure it always runs.
2.) Don’t use the “else” value of 0 in IF({ID}={First ID}, HOUR(NOW()), 0), if you are in the GMT timezone and want it to run at midnight, because 0 will be your value for midnight. You can either set your trigger to another value other than 0 (ie at an hour other than midnight), or use 24 in the formula as in IF({ID}={First ID}, HOUR(NOW()), 24), because the hour will never match 24 (there are only 0-23 hours in the day), yet all values must be an integer for the trigger to work (this is why I don’t use ""—ie empty string here).

[Avana_Vana · Accepted solution]

Update #2: I improved this once again by:

1. Checking to make sure a Tag has a Video before trying to choose a random one
2. Checking to see if each Tag has more than one Video (not very random if it’s only one!) and if so, then…
3. Making sure the random video is different than the current video repeatedly choosing a random video if it is initially the same, or…
4. For Tags with only one Video, use just that one video as the random video.

BONUS: I also updated the second script below that always generates the first record to use for an hourly trigger, simplifying the logic, and reducing the number of getCellValue() calls by n-1 times.

Update: For future readers of this thread, here is a much simpler and more flexible version of the above script that will also handle situations when more than one record can be linked, (The above is for one-to-one relationships, not one-to-many), as well as situations where the total updates are greater than 50 per batch (Airtable’s limit). My above version was a rather silly way to do this, querying two tables and pivoting on one, when all the information needed is actually just in one.

This script will cycle through all the items in a linked record field (videos, here) for each row of a table (tags, here). A linked item (video, here) is chosen at random and then added as the only item in a ‘Random Video’ linked record field, in a queue of updates to the tags table. This queue of updates is then dispatched to Airtable in batches of 50, for maximum efficiency.

let tags = base.getTable('Tags');

let { records: tagsQuery } = await tags.selectRecordsAsync({ fields: ['Videos', 'Random Video'] });

let randomVideos = [];

for (let tag of tagsQuery) {
    let videos = tag.getCellValue('Videos');
    
    if (videos) { 
        let currentVideo = tag.getCellValue('Random Video')[0].id,
        randomVideo = videos[0].id;

        while (videos.length > 1 && currentVideo === randomVideo) {
            randomVideo = videos[Math.floor(Math.random() * videos.length)].id;
        }

        randomVideos.push({
            id: tag.id,
            fields: {
                'Random Video': [{ id: randomVideo }]
            }
        });
    }
}

while (randomVideos.length > 0) {
    await tags.updateRecordsAsync(randomVideos.slice(0, 50));
    randomVideos = randomVideos.slice(50);
}

If you want this script to run daily on a specific hour, follow my instructions above, but use the following script to keep your items (tags, here) updated, so that the script runs only once for all items. As I mentioned above, you can do this manually, but the following second script run as an automation on create and update will ensure that if you add/edit/re-order items, your trigger still runs.

const tags = base.getTable('Tags');

let { records: tagsQuery } = await tags.selectRecordsAsync({ fields: ['ID']});

let firstIds = [];

for (let tag of tagsQuery) {
    const first = tagsQuery[0].getCellValue('ID');

    firstIds.push({
        id: tag.id,
        fields: {
            'First ID': first
        }
    });
}

while (firstIds.length > 0) {
    await tags.updateRecordsAsync(firstIds.slice(0, 50));
    firstIds = firstIds.slice(50);
}

[kuovonne]

Thank you for sharing this update. Your last solution is indeed the most elegant of the solutions posted.