New Text Function: PROPER ( )

[Adam_Parker]

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.

EXAMPLE:

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.

Thanks!

[kuovonne]

Bill.French:

But running this on every record 50,000 times

But the issue isn’t really the code snippet you included in your post. The issue is building a system for vetting unknown code that 3rd parties will create and submit, code that could be far more complex, slightly more complex, and everything in between. (Vetting custom apps for the Airtable marketplace already costs Airtable resources, with no current way of generating revenue for Airtable to offset those costs.)

I think the fact that formulas run on Airtable servers really complicates the issue.

If I wanted to, I could write a custom app that watches all records and changes the case of text strings to title case automatically, as long as the app was running in the foreground. The result would be very similar to a formula field recalculating, but the computational load would be on my computer. Plus, the app would be cluttering up the user interface.

Technically, a custom app could include a code editor so that the app could run custom javascript against any records/fields that the app chooses to watch. But such an app would never be admitted into Airtable’s marketplace.

[Bill_French]

kuovonne:

The issue is building a system for vetting unknown code that 3rd parties will create and submit

They already support this - they allow us to write code without submission and running these on the client should work just fine since the benchmarks on script blocks are blistering fast.

kuovonne:

I think the fact that formulas run on Airtable servers really complicates the issue.

I’m pretty sure I never said the code must run on their servers. I referenced only a server-side performance test.

kuovonne:

I could write a custom app that watches all records and changes the case of text strings to title case automatically, as long as the app was running in the foreground.

Yes, we all could, but this is not about this one function. It’s about seamless extensibility using the most reasonable approach that developers and users will benefit from. They have blocks; they have the blocks editor; they have the ability to bind client-side code with the account; they have the ability to sandbox the activities; they have the UI for supporting the testing. This is not a huge leap and it will lessen the impact of roughly 18% of all support questions. In my view, the fruit is not only low-hanging, it is vastly juicy.

kuovonne:

Technically, a custom app could include a code editor so that the app could run custom javascript against any records/fields that the app chooses to watch. But such an app would never be admitted into Airtable’s marketplace.

Wow - this is way out in the weeds now. I’m simply suggesting that Airtable do what many other platforms have done to allow and share user-defined functions. Google sheets and Excel have supported them for decades. It took Microsoft 13 years to learn this requirement; Google just 6 years. Hopefully Airtable will learn that extensibility matters far sooner than later.

Google…
Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren’t enough for your needs, you can use Google Apps Script to write custom functions — say, to convert meters to miles or fetch live content from the Internet — then use them in Google Sheets just like a built-in function.

Microsoft …
Although Excel includes a multitude of built-in worksheet functions, chances are it doesn’t have a function for every type of calculation you perform. The designers of Excel couldn’t possibly anticipate every user’s calculation needs. Instead, Excel provides you with the ability to create custom functions.

[kuovonne]

Bill.French:

I’m pretty sure I never said the code must run on their servers.

But formula fields have to run on Airtable’s servers. Formula fields need to run even when no user is logged in. When data comes in from a form or from an api call, formula fields need to recalculate.

Google App Scripts has (generous) quotas. Excel custom functions require the workbook to be open.

It is possible to make Airtable custom functions have quotas or other requirements, but that would complicate the user experience.

I’m not saying that Airtable shouldn’t do what you suggest. It would be really nice if they did. I just think that increasing the number of automations and the number of automation runs is more likely to happen first.

[Bill_French]

All valid points, but they should and probably can find a way. :winking_face:

So far, I’ve heard only reasons it can’t be done. Where’s the innovative spirit?

[Bill_French]

kuovonne:

When data comes in from a form or from an api call, formula fields need to recalculate.

Didn’t they redesign the engine to support javascript server-side? I thought I heard that on a call somewhere. This is why Google Apps Script has been so successful - it’s entirely server-side javascript and anyone can use it and it’s safe, secure, and mistakes happen often and systems don’t come crashing down. Sheets are always running your custom functions when data changes.

[kuovonne]

Bill.French:

So far, I’ve heard only reasons it can’t be done. Where’s the innovative spirit?

I’m not giving reasons why it can’t be done. It can be done. But it would take resources and it isn’t clear that the benefit is currently worth those resources. I’m suggesting reasons why they might not have done it yet, and why they might prioritize working on other things first.

As for innovative spirit … I doubt our conversations will motivate Airtable to do (or not do) anything one way or the other. I have my own projects that I am building, that make me happy, and that will work with the currently available features.

[Bill_French]

kuovonne:

it isn’t clear that the benefit is currently worth those resources.

Well, that’s always a concern - we don’t like to invest more than the benefits might accrue over time. Pretty sure Google and Microsoft performed those exact computations and realized the cost of chasing customer needs one-by-one and addressing the support conversations were far in excess of an architecture that made it possible to help people help themselves.

Since you bring it up, let’s talk about resources then…

The Airtable community has dozens of requests for math functions that still remain unmet. These are fairly straightforward functions and lacking an extensible model, engineers (at Airtable) have to be assigned and customers have to wait. As such, the current model to address fringe needs isn’t working well. I’ve seen many who have left Airtable because they are unable to compel the team to address their needs and there’s no alternative.

Zero function extensibility just seems like a bad idea from the get-go.

[kuovonne]

Bill.French:

Google and Microsoft performed those exact computations

But Google and Microsoft have much larger user bases, especially users who have a basic understanding of what a spreadsheet is and what a function is. Both also already have reputations as places where people can use code. Airtable’s users as a whole are in a different place.

Bill.French:

the current model to address fringe needs isn’t working well.

Working well? No. But scripting and automations go a long way in dealing with these fringe cases.

Bill.French:

I’ve seen many who have left Airtable because they are unable to compel the team to address their needs and there’s no alternative

If Airtable knew about these cases, then Airtable decided that it wasn’t worth the resources at that time. There are soooo many things about Airtable that could use improvements.

[gxjansen]

@Adam_Parker Still a workaround, but instead of creating a lot of columns to achieve this, you can also use the formula mentioned here: We still need a simple TITLE CASE (PROPER) function alongside UPPER and LOWER - #6 by gxjansen

[Core_Team]

Has this function been added? This would be super helpful!