Update #2: I improved this once again by:
- Checking to make sure a Tag has a Video before trying to choose a random one
- Checking to see if each Tag has more than one Video (not very random if it’s only one!) and if so, then…
- Making sure the random video is different than the current video repeatedly choosing a random video if it is initially the same, or…
- 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);
}
