Similar to UPPER( ) and LOWER( ), PROPER( ) would take a text string as an argument, and convert it to “Proper Case”, where the first letter of every work is upper-case, and subsequent letters in the word are lower-case.
PROPER ( “1232 DEER VALLLEY AVE” ) would return: 1232 Deer Valley Ave
USE CASE: I use AirTable in my real-estate business, where I hire virtual-assistants to enter public property data scraped from county websites (which often stores data in upper-case). That address data is used to for mail-merge to marketing letters, but unless I take abother step to convert the data to Capital case, the merged data appears ALL UPPERCASE, which makes the letter seem auto-generated.
To solve this, my workflow includes an export from AirTable out to a .csv file, then into Excel where I run a macro that converts to Proper Case, and then is imported back into AirTable, then out to our document merge partner (WebMerge).
I’d love to eliminate that last, fussy part of the workflow where I export out just to convert the text. If I have a PROPER ( ) function as described, it would greatly simplify my AirTable/WebMerge workflow.
You could hack around this in the meantime by converting to lower case, and then nesting around that substitute functions to turn letters with a preceding space into capital. i.e. " a" into " A", " b" to " B" and so on.
For future searchers, a link to a slightly more full-featured implementation of what @Matt_Sayward described above can be found in this reply to this post. I’ve added proper name formatting functionality to the name parsing and extraction routines I demonstrated earlier and released the combined (but not integrated) system as version 3.0 of my Name Demo base.
When will we all learn? And by “all” I mean Airtable designers and the customers.
We need to stop asking for one function at a time and Airtable needs to stop adding one function at a time. The right approach is to simply create one infrastructure-level feature - the ability for script blocks to export as functions.
If we could simply register a script block as a function, Airtable could allow EVERYONE to extend the formulas in any and every way imaginable. There would be hundreds of “block functions” we could choose from; vast libraries of cool stuff that we could all share and improve. Never again would Airtable be missing something that competitor A or B or C had.
I get it. The problem is Airtable would have to probably spend a crazy amount of time making sure each custom script doesn’t cause denial of service conditions that cause their infrastructure to be taken down.
I agree that it would be a nice feature, but if it’s very hard to set sane bounds on per-script executions, I could see this taking a long time to implement correctly.
It would be nice if Airtable developers could purse both paths in parallel in order to meet their customers needs half way, in the meantime…
How would Airtable determine which scripts are lightweight enough to support all of the automatic recalculating that formula fields do with each key press?
Currently you can have a scripting automation that triggers off of a change to any of the input fields. Scripting automations can provide lots of functionality, but because automation runs are limited, we tend to not want to do use them for small things like change the case of text strings.
I wonder if the potential amount of computing is one of the reasons why array functions are limited (no split, no for each, etc.)
I don’t think this is an issue because they presently support script automations and script blocks and no one at Airtable is spending vast amounts of time checking your code. I believe it’s because these types of script integrations are fully sandboxed in a Caja-like container.
Simple metric gating the use of a script as a function would be required for a script block to “stick” and be callable in a formula. If developers write wonky functions that are not optimized, they wouldn’t be deployed. Airtable can know this with a simple test as “deploy” time much the way we are forced to test script automations.
Yes, this would be a concern if Airtable wasn’t already doing this with script automations.
Not only nice, but important because you need the basics in a form-factor that serves performance and allows them to check all of the we-have-that-too feature list. Core functions are a necessity and Airtable would be free to compete with after-market developers for better title-case or whatever other functions seem to be core to the platform. But the extensibility is needed because not everything must be core to the platform; customers will invariably need unique functions that no primary vendor could ever keep up with or even understand how to implement.
But testing a scripting automation only tests the script against a single record. A different input record could result in very different runtime and resource requirements. Automation scripts are still monitored at every run time for duration, memory usage, and several other items, and if those limits are reached, the script fails.
A formula field is much different. A formula field needs to be much more reliable against a wide variety of inputs. A formula field needs to be able to be run very quickly against 50,000+ records when the formula field is created or modified.
Deciding on reasonable limits for 3rd party formula scripts, and then testing each script submitted would take a lot of company resources, without a clear way to cover the expense of those resources. This doesn’t even take into account how to motivate 3rd party script writers to write quality scripts. The marketplace currently has very few 3rd party scripts–evidence that script writers are reluctant to write and submit quality scripts for general public use for free.
A scripting app script runs on the users computer, not Airtable’s servers. Formulas run on Airtable servers. The computing burden is no longer Airtable’s.
A scripting app runs only on a user button click, not with every key stroke, with many users not even realizing that every key stroke causes formulas to recalculate. Formulas run for every keystroke, in addition to every time the formula is created or updates. And changes to a single input field can have a cascade effect when you have calculated fields based on other calculated fields, sometimes involving multiple roll ups passed back and forth between tables.
Users are far more patient with a script taking a long time to run versus formula fields. Users generally expect a formula field to update instantly, but are rarely surprised when a script takes a second to run.
Now, you could have an automation script that runs on every key press against multiple records, but there are limits for automations.
And so should there be for user-defined functions.
How they decide to implement them is a detail that Airtable certainly needs to work out. But I can safely say that they will realize that function extensibility is critical to taking the platform to the next level, just as extending integration services is gated by a true event model and webhooks. They recently realized that data visualization must be extensible (e.g., Vega) - no vendor can build every chart type or data aggregation in a closed environment. The same is true for data-centric functions.
I think the easiest way for Airtable to deal with this situation is to find a way to let users have more automation and buy more automation runs. People are already clamoring for both. And it wouldn’t require having to vet 3rd party code.
Meanwhile, I’ve been thinking through how to write a formula field to change a text string to title case. It is possible for strings with a limited number of words. It would involve the workaround for split and be crazy long. My head hurts thinking of extending such a function past about three words. Another option would be to have a formula field that detects if the case needs changing, and an automation script that changes the case. That use case would be fairly easy to implement, but a bit of a waste of an automation.