New Text Function: PROPER ( )

(This would only work on addresses that had a number at the start to give you the initial trailing space without being affected though)

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.

4 Likes

This is such a basic spreadsheet function I can’t see why Airtable wouldn’t have it. Hope they can implement soon.

3 Likes

In addition to title case, we’d love to see sentence case, with initial-cap at the head of a string terminated by a period.

1 Like

Please! Please! Please! :slight_smile:

Is there anyway to do this through Javascript using the new blocks in the meantime?

Happy Friday!

Um, yeah, of course.

2 Likes

Yes… I use this in integromat. They called id startCase

Can we please get support for this function?

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.

#wakeup

1 Like

Hi Bill,

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.)

1 Like

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.

This is why I was also critical of the RegEx implementation; why craft a gating model that constrains the use of RegEx and requires Airtable to add meta-features for RegEx?

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.

How is that any different from writing a script block that runs agains every record and updates not one field, but many?

Here are some differences:

  • 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.

Indeed, and this should not be the case - you should be able to create this in a block editor and introduce it into your Airtable instance.

function capitalizeFirstLetter(string) {
   return string[0].toUpperCase() + string.slice(1).toLowerCase();
}

function kuovonnesTitleCase(string) {
   return string.split(" ").map(x => capitalizeFirstLetter(x)).join(" ");
}

Is this approach optimized for performance? Perhaps. But running this on every record 50,000 times on a server requires no more time than almost every function they currently support. And if they really smartened up the engine, formula fields should support a conditional computation layer much the way conditional GETs eliminate trillions of HTTP requests every hour. :wink:

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.

1 Like

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.

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

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.

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.