Help

Re: Pretty Printer for Airtable Formulas V0.2

1149 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Airtable_Clerk
6 - Interface Innovator
6 - Interface Innovator

This is the second release of Orc – V0.2 – prettier and greener.

If you write formulas for Airtable Bases you might want to check out this tool, packaged as an Airtable Base. ‘Orc’ , a formula ‘Pretty Printer’ will format a complex Airtable formula into a standard set of uniformly spaced code.

Orc changes this:

DATEADD({NextMonWeekMonth(2)},
IF({OnWeekdayDOW}<WEEKDAY({NextMonWeekMonth(2)}),
SWITCH({RecurrenceType},
““Weekly””,
{WeeklyDate(2)}
,
““Monthly:ondayofthemonth””,
{Monthlyondayofmonth(2)}
,
““Monthly:onacertainweekday””,
{Monthly,certainweekdayDate(2)}
,
““Monthly:onlastweekday””,
{Monthly,lastweekdayDate(2)},
1+{OnWeekdayDOW}-WEEKDAY({NextMonWeekMonth(2)})
)
+7*({WhichWeekas#}-1)-1,
‘days’)

Into this:

DATEADD({NextMonWeekMonth(2)},
  IF({OnWeekdayDOW} < WEEKDAY(
      {NextMonWeekMonth(2)}),
    SWITCH({RecurrenceType},
      ""Weekly"",
      {WeeklyDate(2)}
      ,
      ""Monthly:ondayofthemonth"",
      {Monthlyondayofmonth(2)}
      ,
      ""Monthly:onacertainweekday"",
      {Monthly,certainweekdayDate(2)}
      ,
      ""Monthly:onlastweekday"",
      {Monthly,lastweekdayDate(2)}, ,
      1 + {OnWeekdayDOW} - WEEKDAY(
        {NextMonWeekMonth(2)})
      )
    + 7 * ({WhichWeekas#} - 1) - 1,
    'days')

To use:

  • Open the Orc base as well as your base.
  • Select a formula from one of the columns in your base and copy it.
  • Paste the formula into the Orc table column ‘Input’
  • Press the ‘Prettify’ button.
  • Copy the ‘Output’ column text and paste back into your own base.

Version 0.2 Release Notes

  • Prior version required you to re-install the Orc JavaScript file. This version should work without your needing to re-install the code.

  • Prior version assumed only one row in the Orc formatting table. This version let’s you add as many rows as you wish.

  • Default formatting rules have been modified to conform to more commonly accepted formatting standards. If you prefer your code to be formatted differently you can configure Orc to your preferences. See the ‘Overview’ document in the ‘Documentation’ table for configuration instructions.

  • Code now contains rudimentary documentation.

6 Replies 6

Thank you for sharing this script. Being able to “pretty-ify” a formula with hard-to-read formatting is a good thing.

In your documentation you state that one of your reasons for releasing the code is to get feedback. I hope that this feedback will be useful to you.

  • You had trouble including the code of the script in your base in the Universe. In order to include the code of the script in the Universe, you need to enable the “Show code in public shares” toggle under the “Advanced” section of the documentation.

  • The editor displays a lot of red bars (and the corresponding wavy red underlines) for the script. The red bars usually indicate that your code contains some sort of syntax error. Even though your script still runs, people will feel more comfortable running code with fewer red bars. Although I have not read all of your code, it looks like a few let or const keywords would clear up most of the red bars.

  • The script does not act on only the single record for which the button was clicked. Instead the script acted on the first record in the view, and then produced unexpected results for additional records in the view.

  • The script produces an error when there is a field name without curly braces that contains an accented character.

  • I personally format my complex formulas using a different style from what your script produces.

image

Thank you for your feedback, kuovonne, very much appreciated! I apologize for not replying sooner, I had gotten distracted with other things and hadn’t noticed your feedback until today.

I had not noticed the “Show code in public shares” toggle. That’s a big help!

You’re right; there were a lot of red bars in the code that were easily cleaned up. My next release will be syntax error free.

The script invocation was simplistic and didn’t allow for more than one record in the main view. A bit more effort would have fixed that. This is a shortcoming that I will fix in the next release. Rather than have a separate table of examples I will simply enable the code to use whatever row that the user presses the ‘Prettify’ button in, and move all the examples into main view; much easier to use.

Not handling field names without curly braces is a flaw. I’ll see what I can do about that. I will document shortcomings that I can’t easily fix for the next release.

The upcoming release I’m working on let’s you configure the code to output your preferred formatting. It let’s you change the spacing between elements of the formula, wraps long lines according to configurable parameters, and is better documented. It requires you to edit a 'configuration ’ function, but the edits are simple. Hopefully it won’t be too onerous. I’ll produce examples and perhaps alternate configuration settings that people can select.

Thanks again for your thoughtful and helpful comments. They are very much appreciated.

Hey kuovone, could you provide a code snipit that contains field names with accented characters? I can’t find Airtable documentation on column names and I’m having problems finding accented characters that Airtable will let me use in a field name. Airtable will happily accept just about any UTF characters inside curly braces but I can’t get it to accept field names with accented characters but without curly braces in a formula. Without such a field name I can’t create a test case for it. Thanks!

I’m finding the same as you now. Airtable formulas are requiring fields with accented characters to have curly braces in formulas.

I remember testing field names with both emoji and accented characters, and that emoji required curly braces, but single-word field names with accented characters didn’t. Either my memory is faulty or Airtable quietly changed things.

Since your script requires a valid formula as input, you can probably ignore this case. (Of course, the best help would be one that could help identify errors in formulas, but that would be far more complex, and even Airtable currently doesn’t provide that.)

Thanks for your quick reply; sorry that Airtable has dropped a handy bit of internationalization but I guess it’s not an undue burden given that curly brace enclosed field names can include what appears to be the entire UTF set. It happily swallowed Cyrillic, umlauts, and accents in my tests. I can release a new version of the software now.

You can fully customize the spacing formatting to pretty much anything you prefer, as well as much of how the formatter chooses to wrap lines, but you need to do this by modifying a function (“configureFormatting”) in the JavaScript file. If folks find this to be intimidating or onerous I’m experimenting with a ‘Configuration’ table I’d add to the base that allowed you to specify what you wanted. That, and a smarter code wrap algorithm would be my primary next version features.

I know that the default formatting configuration might not be standard for most Airtable users. If you post an example of what you consider nicely formatted code I’ll use it for the default formatting. If other folks have different formatting ideas they can comment on it, but given all the great input you’ve given me I’d be happy to set your formatting rules as the default.

You can see examples of how I format my formulas in my posts on this community and on my website.

I don’t have a single format that I use. I tend to use a mixture of styles, depending on the exact formula. Here are some of my general guidelines.

  • For multi-function formulas, I like to put each input parameter on its own line, and have all input parameters at the same indent level. Sometimes I put the first parameter on the same line as the function name, sometimes not.

  • For nested IF functions, I change the alignment slightly to avoid the massive indentation.

  • For SWITCH functions, I tend to keep the pairs of input parameters again.