Improvements to formulas

One of our biggest feature requests is that we would love to see gigantic improvements to formulas — in particular, way more flexibility with typing in formulas, and better improvements to error capture.

More specifically, these are our top 3 requests for formulas:

  1. Please allow us to have a much larger typing area for formulas, more like a real programming language would allow. Currently, we can’t press the “return” key within our formulas — nor can we space out the formulas to be more readable.

  2. On that very same note, the formulas should be waaaaay more understanding & waaaaaay more forgiving when it comes to spaces & returns. All other programming languages simply ignore extra spaces or extra carriage returns, but any of this causes Airtable to give us an error message. For example, the first formula below works just fine. But the 2nd & 3rd formulas below don’t work at all. The ONLY difference is one extra space in the 2nd & 3rd formulas. All other programming languages would understand that these are the exact same formula, but not Airtable.

Formula #1 — this one works:
IF(AND(City=BLANK(), {Event Name}=BLANK()),Country,IF({Event Name}=BLANK(),City,{Event Name}))

Formula #2 - this one doesn’t work:
IF (AND(City=BLANK(), {Event Name}=BLANK()),Country,IF({Event Name}=BLANK(),City,{Event Name}))

Formula #3 - this one doesn’t work:
IF(AND (City=BLANK(), {Event Name}=BLANK()),Country,IF({Event Name}=BLANK(),City,{Event Name}))

  1. Because Airtable is so rigid & strict & unforgiving when it comes to formulas, we need better formula errors. Airtable should highlight EXACTLY where the error is, so we don’t spend 2 hours (like I did the other night) troubleshooting a formula, when the only problem was simply an extra space.

Thanks,
Scott

2 Likes

I completely identify with this request, and agree they would all be really nice quality-of-life improvements to have – but there are a lot of other things I’d rather see first, mostly because this issue can be worked around by using a code editor to write and store copies of all my formulas.

I just use a code editor (Visual Studio Code) to create .py documents (just a personal choice for the sake of syntax highlighting) to write out my Airtable formulas in, and then copy paste them into the field editor. If it’s a big, important formula that I anticipate I may need to update in the future, I save the document with a name that refers me back to where that formula lives in my base.

Since this can be worked around by using an external editing environment, I wouldn’t expect it to be high on their list of enhancements to make.

2 Likes

Thanks, @Jeremy_Oglesby!

I can see how an external code editor could help us write & visualize our Airtable formulas.

Question: Is your Visual Studio Code program configured to recognize the syntax of Airtable’s formulas? In other words, can Visual Studio Code alert you to errors in your Airtable formulas?

Re: #2, my gut says that spaces in certain places aren’t allowed because a good chunk of Airtable’s formulas revolve around functions. Take IF, for example. In JavaScript, the format for an IF statement looks like this:

if (x > 15) doSomething();

In that line, “if” is a reserved keyword. While spacing around such keywords is encouraged for clarity, it still works to leave the space out.

In Airtable’s formula system, however, IF is a function. Functions often have more strict syntax rules, but it’s up to the language designer to choose whether doSomething() is just as valid as doSomething (). In JavaScript, both versions are valid, though it’s encouraged to leave spaces out when calling functions. Why? My gut says that spaces are discouraged with function calls for the same reason that they’re encouraged around keywords: clarity. You can tell at a glance that “if” is a keyword and “doSomething” is function name by how whitespace is used (or not) around each.

With Airtable, the developers have decided that spaces are not allowed with function calls, so IF(Field, "True", "False") is valid, but IF (Field, "True", "False") is not. In a sense this choice feels similar to one that the developers of Python made when creating rules about defining code blocks. With many languages, indentation is optional when defining a block of code for function definitions, if statements, etc. With Python, though, they force you to indent, and the indentation itself is what defines what code is in a block.

While it may seem like a simple thing from our perspective to allow spaces between a function name and the opening parenthesis, and while I somewhat agree that it would help increase formula clarity in some cases, it might actually be a huge headache from a development standpoint based on how the Airtable devs implemented the tool’s formula system. I also feel that the lack of space is a helpful reminder that IF, AND, OR, etc. are functions in Airtable, not keywords, and should be treated as such.

Thanks, @Justin_Barrett! I get what you’re saying, but I’m coming from the FileMaker Pro world — which is completely designed for maximum developer-friendliness — and FileMaker Pro allows for any number of spaces or carriage returns for optimal readability (and typeability) within all functions & formulas.

It makes Airtable feel extremely restrictive & unforgiving, especially when Airtable doesn’t tell us WHERE the error in our formulas lie.

I spent 2 hours trying to troubleshoot a perfectly valid formula the other night, but because there was an extra space in it, Airtable wouldn’t accept it.

And even worse, Airtable wouldn’t tell me WHERE the problem was in my formula.

Of course, now that I know this information, I will be way more careful in the future, but it just seems like formulas still have a long way to go.

Another unrelated example of how far formulas can still go: instead of nested “IF” functions, we should have a “CASE” function (like FileMaker Pro has). This eliminates the need for nested IF statements. I think they tried to get close to this with the “SWITCH” function, but I don’t believe that the “SWITCH” function allows for multiple expressions to be evaluated throughout the formula. FileMaker’s “CASE” function allows this. I should probably just make this one another feature request. :stuck_out_tongue_winking_eye:

No, I’m not aware of any existing tools in Visual Studio Code that perform syntax highlighting based on Airtable formula standards. It might be possible to build an extension for this, as Visual Studio Code is very extensible… but I don’t have time for that right now.

I use the built in python syntax highlighting just to get the color distinctions on operators and what not – the python highlighter does pretty well at making my Airtable formulas easy to read.

The main advantage, though, is the ease with which I can structure out a formula with new lines and still have it be valid in Airtable’s formula editor when copy-pasted in. In the video below you can see how parentheses are automatically closed for me as I type, and formulas automatically indent their parameters for me as I type. And all of it is valid when copy-pasted as-is into Airtable, so it makes it much easier to prevent errors like an extra space, even if it’s not necessarily easier to spot them (although it may be due to the syntax highlighting).

And, also as you can see, there’s an advantage of being able to collapse formulas, or parts within formulas when they are getting really long, and if you are saving a long list of formulas, you can add comments to them to remind you what the formula was for.

The only thing you miss in this is the auto-completing of Formula names and field names that you get in Airtable’s editor.

1 Like

I can echo the benefits that @Jeremy_Oglesby lists for using an external editor. For simple formulas that aren’t nested too deeply, I’ll build them directly in Airtable (though I do agree that a larger editor space would be better). Anything more than that and I’ll move over to BBEdit, then copy my results back to Airtable. I haven’t tried applying syntax highlighting yet, but might give that a shot eventually.

@Jeremy_Oglesby Not sure why, but the embedded video isn’t playing. Does it play when you view the post?

1 Like

No, it doesn’t play for me either – probably too big a file. I linked it from dropbox, but I’m sure Discourse still has limits on how big a file it will stream… :man_shrugging: