Hacking Airtable with the Alphabet

For those of you that have been following me for a bit, you know I’ve been live-coding Alexa skill development on Twitch for a few months now. One of my favorite tools for development these days is Airtable, which is mostly just a cloud database, but its user interface, api, and documentation is one of the industry’s best. Seriously.

For many of the Alexa things I build on Airtable, whether that’s a trivia game, or just the speech data that I want to say to my users, I always come back to one fundamental problem:

Airtable doesn’t provide a way for me to retrieve a single random record from the database.

There are LOTS of ways for me to retrieve my data. Filter formulas, sort orders, and primary keys give me lots of options. I have access to created and modified dates for each record. If I know what I’m looking for, it’s incredibly easy. But if I want one random trivia question out of the hundreds I have in my table, I typically have to grab ALL of them, and choose one randomly from inside my software. This is a potentially huge, potentially slow process, and one I’m desperately trying to avoid, but when most of my tables have fewer than 100 records, it’s also not destructive. Yet.

Enter sound effects. The skill I’m building has a library of over 1000 sound effects that a user can request. Sometimes, they will say “play the cat meow sound effect,” and I can determine which one that is, retrieve the record, and play it for them. Other times, they will say “play a random sound effect,” and expect to hear something new. How do I solve this randomization problem? 1000 records is too many to grab every time I need to do this.

Here’s how I “solved” this. Each record has a unique identifier, which I call the RecordId. (You can see the entire dataset here.) Below are ten examples of the RecordIds:

recEux9NFw0Cuqrwy
recFqFT3v7gKNryDt
recVHl6WkB3hsfbG0
rectIyMvutLNbdqig
rec5cRiiDDQ2k16kh
reczDDoansCv22MAh
recckUMmBOvOzgzBb
recIOOnbPCZ5QSPBG
recP5xDQkKxV2vEkQ
recB5raUWFx7pEmmB

They all start with “rec”, and then contain 14 additional random characters. Because I can apply filters to my queries, my first thought was to filter the records based on a single character. So, if I filter on “a” in this example, I’d end up with only two of the ten records above (#6 & #10). (It’s case insensitive.)

When I tried this against the entire dataset, it reduced the number of records I was pulling from 1,000 to around 350, pretty consistently. 350ish is better, but it’s really still way too many. In my next experiment, I tried two letter combinations. I assumed there would be cases, even in 1,000 records, where some two letter combination wouldn’t exist, but I was delighted to find that about 15 records matched every two letter combination as well. 15 randomly selected records is something I’m willing to accept. I can pick from one of those and give my user what they asked for. (Three letter combinations resulted in ZERO records most of the time.)

There are a couple of obvious exceptions to this rule: “re” and “ec” each bring back 1,000 records, since every record contains those letters (“rec”). So I just decided to eliminate “e” from my random alphabet pair generator.

Now, when I need a random record from this collection, I pass in filtering rules on the RecordId for a randomly generated pair of letters, and I get a very small collection of data to work with. It’s not perfect (I’d still love to be able to get just one), but this is FAR better than dragging 1000 records across the internet every time I need just one.

Now for the real question: How would YOU solve this problem? Mine is almost certainly not the best solution, it’s “a” solution.

4 thoughts on “Hacking Airtable with the Alphabet

  1. I don’t know sortable, but give each table a column with a sequential integer starting at 1 then just choose random(1, count(table)) and retrieve that row from the dB?

  2. No way for me to help you on this one as a can barely understand anything you are talking about here. I do agree with you on one specific point though. There HAS GOT to be a better way!!! Impressed with the elimination of the “e” in the randomizer. A lesser mind may have been temped to eliminate all three letters 😜.
    Is the problem a time issue?? That’s what I’m gathering. And is not possible to follow the “rec” with a number sequence to plug into your randomizer or would that present the same issue??
    #notqualifiedtogiveadviceonthistopic

    1. Dude, your insights are spot on! Maybe you should have been a software developer? The “rec” and random IDs aren’t generated by me, they’re generated by Airtable automatically. I considered just numbering the records 1 through 1000 also, but I plan to continue adding records to the table, and I’d have no idea how many there are at the time.

      Love this stuff!

Leave a Reply to jeffblankenburg Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s