Product update: Formula editor improvements

Hi everyone -

As you may have noticed yesterday, we’re introducing new visual cues and functionality to help users successfully read, create, and troubleshoot formulas directly in Airtable.

Some of these new formula editor capabilities include:

  • Colorful syntax highlighting
  • Parenthesis matching
  • Multiline editing with the ability to fold multiline formulas
  • Surface error when there are unbalanced parenthesis
  • Surface error when there are blank space or miscellaneous characters between a function name and the opening parenthesis

Formulas are complex and hard to compose, especially when they contain more than one function. We hope that these updates make it easier for you to troubleshoot your formulas and lead to fewer errors. You should see these changes reflected in Airtable today, but we can’t wait to hear what you think!

Additional resources:

19 Likes

Pretty nice version 1.0 of this! :slight_smile:

There are a few bugs with this:

  1. If you have 2 parentheses next to each other (open & close parentheses), and your cursor is blinking in the middle of the parentheses, and then you choose a field name from the field list, it deletes the closing parenthesis.

  2. The new formula editor doesn’t work properly in a web browser on the iPad. The formula editor won’t let you drag the cursor around (at least not consistently), and it likes to delete parts of the formula as you try to select fields or type text.

4 Likes

Thanks @ScottWorld - I’ll share those with the team now!

1 Like

Deploying on a Friday?
Jordan, please Slack the product manager and let them know that they have nerves of steel, but also thank them. :grin:


@ScottWorld - I’m happy I’m not the only one that caught the parenthesis behavior.
Regardless, the change is very much appreciated.

2 Likes

Such a great change! I can’t tell you how many times I’ve had to pull my formula into Notepad++ to see which parentheses wasn’t closed correctly :sweat_smile:. Linebreaks will be really nice as well. Thanks!

1 Like

Thanks for the official announcement.

The linter is a bit overly zealous on my formula. It says that I have missing parenthesis when I do not. It is also stating that these errors are in weird spots in my formula, such as the middle of a word.
image

Pressing tab, shift+tab, and arrow keys on the keyboard also have unexpected results.
When I press the up/down arrow keys when the cursor is in the edit box, I expect the cursor to move up/down, but sometimes it instead scrolls through the list of suggested fields. When I press tab, sometimes I get an indent, but other times I get a new field name entered.

Also, is there some way to make the formula editor wider? While 45 characters is nice, 80 characters would be much, much better.

3 Likes

@kuovonne thank you for sharing this - super helpful. This is just the first wave of more improvements we’re making to formula editor so I’ll make sure to pass this along to the team.

3 Likes

I’m really looking forward to these bugs in the formula editor being worked out. Until then, I’ll share my current method for writing formulas.

I currently use an installation of Scripting App to edit my formulas. Scripting App is free to use in all bases with no limits on the number of instances, so this should work for everyone.

I shrink Scripting app so that the editor sits on the right sidebar and I can still view and interact with all of the fields in the tables on the left. I put // @ts-nocheck on the top line of the scripting app editor, and most (but not all) of the squiggly red underlines go away.

All the keys (tabs, shift+tab, enter, arrows, etc.) work as expected. I can easily see matching parenthesis. Most things are color coded. I have lots of width and length to view my entire formula. I get folding/unfolding. I still get some squiggly red underlines, but // @ts-nocheck gets rid of most of them.

4 Likes

This is such a great improvement and has been very much needed! Although I need to spend more time with testing (currently on the road), my first feedback would be for parentheses pairs to be coloured so that they’re easily identified, and make the editor width as wide as legally possible.

2 Likes

This is very welcome! I’ve got old formulas I don’t dare touch because they were unreadable with all the nested functions. Looking forward to giving it a go.

1 Like

Hi @Jordan_Scott1,

There seems to be a lot of bugs with the new formula editor, which makes it extremely difficult to create or edit formulas.

For example, the biggest problem that I continually experience is that if you try to add a function name to the left of an already-existing function, the new formula editor will completely erase the function that was previously there. This makes it ALMOST impossible to work with the new formula editor.

I can create a video if you need more details on this, but can you please escalate this issue to the engineering team ASAP?

Thanks,
Scott

3 Likes

Thanks for this @ScottWorld - passed along to the team!

2 Likes

Hi @Jordan_Scott1,

I created this video for Sara in tech support — this could be very helpful for your team:

2 Likes

@Jordan_Scott1 I think there might be a bug in this that I don’t see reported above. The attached screenshot shows a working multiline formula, but which flags 5 ‘Missing opening parenthesis’ errors. If I flatten this formula into a single line, the errors disappear. Is this a bug, or am I doing something wrong?

The formula is:

IF(
  OR(
    IS_AFTER({Assets Ingested Date},{Last Attempted Delivery to KDP})=1,
    {KDP Distribution Status}="FAIL",
    AND({Assets Ingested Date}!="",{Last Attempted Delivery to KDP}="")
    ),
    "TRUE",
    "FALSE"
)

image

I think this is the same type of bug that I reported seeing.

If the formula saves and produces the correct result, ignore the wavy red underlines.

1 Like

Thanks very much @kuovonne

I now see a resize drag handle for the formula editor. I can now see 16 lines at a time with over 100 characters per line. The editor doesn’t auto-size correctly yet, but this is a vast improvement.

:confetti_ball:
Thank you Airtable!

I am still seeing red wavy underlines where they don’t belong.
Is the formula editor based on Monaco, the same as the Scripting editor? Can you fix the grammar file it uses for Airtable formulas?

4 Likes

Thanks so much, Scott - this has been submitted to the team!

3 Likes

I spent some time fighting the new formula editor. It works, but… not as smoothly as I’d hoped. I think perhaps instead of re-inventing the wheel, just take lead from apps like Notepad++ that make editing text easy with zero fuss.

1 Like

I still fight the formula editor on occasion. But I think that Airtable is on the right track with improving the formula editor. The formula editor has some nice features that we cannot get elsewhere:

  • autocomplete for field and formula names
  • syntax checking
  • integrated documentation for formula field parameters
  • it’s right there

The previous version of the formula editor had the autocomplete and integrated documentation. It didn’t have multi-line support and a wider line length.

Most of the current issues with the formula editor relate to

  • using the keyboard for moving the cursor, versus picking an autocomplete choice, versus controlling indent level
  • what text to add/remove when using autocomplete
  • incorrect syntax checking

Once those issue are cleared up, I think that the formula editor is going to be awesome.