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:
- Cycle through each of 17 or so “topics”.
- For each of those topics, generate a subset of “videos” whose topic link field id matches the current topic’s id.
- 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).
- 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.
