Dynamic Single Select field?

Unfortunately formulas in one field can’t drive the content of another field (e.g. a Single Select). A formula field only affects its own content, and the output can only be text (including emojis), a number, or a datetime item. That said, you could still make this work by having a formula output “This week” / “…month” / “…quarter” as text. It wouldn’t be wrapped in a colorful bar, but it would get the job done.

Note to self: because you only have the free account, be careful when trying to solve a problem when blocks are involved.

Not a problem — the only reason I caught it is that I keep telling myself I should figure out exactly what it is the Matrix Block does; since I was sitting here, babysitting the PC while it tried to recover data from one of a couple dozen old-to-ancient hard drives I’ve kept telling myself I’d one day get around to consolidating, I decided to find out. That’s when I noticed the Block couldn’t take a single-line text field for clustering.

I often provide a link to a demo base in my replies. I guess most people think that’s in order to be helpful — and, sure, that’s partly why. Mainly, though, it’s because I originally created the base as a sanity-check on my own code, especially when dealing with a feature or function I rarely use.¹

We tend to forget (well, I do, at least) just how complex an application Airtable actually is. In part, that’s thanks to its much-vaunted friendly, spreadsheetish UI. In part, especially for those of us who hang out in the Community forums, it’s because of the steady drumbeat we hear regarding all the things it can’t do.³ Mainly, though, I think it’s because most of us tend to use Airtable to solve problems of a similar nature — that is, similar at the micro level, across all problems I solve, rather than the macro, across everyone’s problems. It would be a very rare individual, for instance, supporting a truly screwed-up work flow, who’d have reason to incorporate even half of the available Blocks.⁴ Even regarding core Airtable functionality, for most of us there are entire areas we rarely, if ever, see.

Accordingly, as best I recall, I have yet to answer any question concerning Collaborators correctly. That doesn’t mean I should stop trying, though, any more than overlooking a Block limitation means you should restrict your responses to non-Pro features — because, when all is said and done, one of Airtable’s greatest strengths is Airtable Community.

I can’t tell you, over the years, how many times I’ve chosen one product over another — even when the rejected product was quite possibly the technically superior of the two — because of the relative levels of support available.⁵ That’s what brought me to Airtable to begin with: I needed a cloud-based RDBMS with a robust and well-documented API, and the first two candidates had the misfortune to have been released by companies far enough removed from the real world to think a blog’s worth of video clips qualifies as ‘documentation.’ My third try was the charm, leading me to Airtable, who had the grace and decency to wrap the obligatory video tutorials with actual words. Later, when the still-in-beta plugin I was using to address the Airtable API hit a snag, I went looking to see if I could somehow bypass the problem by restructuring the data. I found the Community forums — and never escaped.

—because, as important as they are to Airtable neophytes and casual users in need of a quick nudge in the right direction, the forums are even more essential for those of us who consider ourselves proficient at Airtable development.

A while back, I bought a newspaper — not as in, 'from a vending machine,'⁶ but a newspaper publisher — and as part of my attempt to drag it out of 1996, I ended up coding a bit in Perl. I came to like Perl a lot, for while there was always a best way to do something, there were almost always a number of right ways to do it, as well. Once you had mastered significant-enough of a subset of Perl functionality, you could spend your time solving actual business problems, not searching StackOverflow about some niggling syntax issue. Of course, that meant once you figured out a half-assed way to do something, that’s probably the way you’d do it until the day you died:⁷ There was no incentive to go back later and improve the code so it would now do things in a whole-assed? no-assed? whatever-assed-is-better-than-half manner.

Of course, the number of man/hours that had been devoted to programming in Perl up to that point in time was roughly the same as it took to get from Olduvai Gorge to the horse collar, so the distance from ‘right’ to ‘best’ was usually no greater than a couple of Google searches long. On the other hand, Airtable development, in relative terms, may finally be nearing the point where we realize we can shelter this strange ‘fire’ stuff beneath an overhang and it won’t die from the rain: You have to have ‘practice’ before you can have ‘best practices’ — and keeping current on the forums is one of the best ways to make sure ‘expertise’ doesn’t simply mean ‘doing stupid stuff very efficiently.’

Case in point: Recently, someone had a question about how best to extract the last-entered item from an array — a common task that can be surprisingly difficult to do in Airtable, thanks to its lack of a looping mechanism. I responded with the canonical answer: Collapse the array into a comma-separated string and then, using a formula containing an explicit extraction routine for each of as many items as the maximum he ever expects to need to support, retrieve the final value. I wrapped my reply in my usual ‘Here’s an answer, but you’re not going to like it’ boilerplate and added a link to the scary code required to process an array with as few as five items.

It turns out the poster had already created a solution far superior to the one I proposed — a breathtakingly elegant solution I’d never before seen referenced, based on an approach that should prove applicable to a number of commonly used algorithms. However, he had hit a snag in implementation. To me, the problem he faced seemed trivial: It took as long to fix as it did to type the reply. The solution he shared, on the other hand, is priceless: It will save untold hours in development and support — and untold cycles of processing time — over the coming years.

All thanks to Airtable Community.

1. Not that familiarity is any proof against stupidity: I recently spent far too long troubleshooting what should have been a tossed-off² three-line text-parsing formula before finally realizing I’d reversed the order of arguments to FIND() — a function I’ve probably entered 10,000 times in the 20 months I’ve used Airtable.
2. In the US, not UK, sense.
3. Usually in comparison with a 33-year-old application from the world’s largest software company that’s actually an entirely different type of program.
4. Or @Andrew_Enright, in which case the work-flow comment does not apply.
5. For example, there are aspects of Integromat’s UI and implementation I prefer to Zapier — but the former’s recent decision to shutter its support forums and replace them with its Facebook page makes them no longer a viable alternative.
6. Although a vending machine manufacturer came along as part of the purchase. Also, the paper once took its appeal of California newsrack laws all the way to the U.S. Supreme Court… which refused to hear it. (The Court ruled in the paper’s favor in a later First Amendment case, leaving it with a career record of 1:1.)
7. Or the day you hire a recent CS grad who refuses to be an enabler of your Perl habit…

Thanks you both for replying, I appreciate the input. And in fact @W_Vann_Hall your solution is similar to one you posted for a different problem on this forum (about creating a master base calendar for dates across several tables) that I have been using in the very same project, yet didn’t think to apply it as you have suggested. Although not perfectly automated, this will work well enough, so thank you.

Also responding to your longer comment @W_Vann_Hall , as a relatively new Airtable user, the Community has been absolutely invaluable. The level of engagement with issues, and the thoughtful and considered responses that so frequently solve those issues, is quite amazing. So thank you in general for your contributions, they make a huge difference to newbies like myself.

Hi @Jon_Edwards, wanted to share a recent update we rolled out related to your question: https://community.airtable.com/t5/announcements/introducing-single-select-output-for-formula-fields/ba-p/186645

+ @W_Vann_Hall @Justin_Barrett