Is there an easier way to create Airtable formulas programmatically rather than typing SQL-ish strings?

The formula editor in Airtable has improved a little since I started using Airtable years ago. You can now expand it, and there DOES seem to be some matching of opening and closing parentheses. Nevertheless, I’ll pass to you the tip that was given to me years ago: Write your formulas in a proper text editor like Sublime Text (Windows) or BBEdit (MacOS), copy, and paste into the Airtable formula editor for validation and saving.

If someone has an even better idea, I’m eager to hear it.

ADDED HOURS LATER: I crossed out my description of how much the formula editor has improved. I suspect @kuovonne and I see (and appreciate) the same improvements. I am not quite ready to agree with her assessment (“improved A LOT”) but I’m willing to strike through my own. :slightly_smiling_face:

I find that Airtable’s formula editor has improved a lot since I started writing formulas. I used to never write formulas in the formula editor. Now I almost always use the built in formula editor.

Welcome to the Airtable community!

When writing formulas to use with filterByFormula I always start by writing a formula in a formula field until it works the way I want it to. Then it is just a matter of string manipulation to get the variables in.

At that point, I use backticks across multiple lines to write the formula. Otherwise dealing with all the quotes is too tedious for words.

const filterConditions = `
AND(
  OR(
    AND(
      FIND('${userId}', {uploader}), 
      FIND('${teammateId}', {recipient})
    ),
    AND(
      FIND('${teammateId}', {uploader}),
      FIND('${userId}', {recipient})
   )
  ),
  IS_AFTER(
    {createdAt}, 
    DATETIME_DIFF(TODAY(), -14, 'days')
  )
)
`

Hey @Alex_Long1

This isn’t specific or applicable to the Airtable API but is more about formula writing overall.

I spend a ridiculous amount of time writing formulas, so I figured I’d chime in at the tail end of this thread.

I’ve tried many different approaches to figure out what method best works for how I like to write them.

Airtable’s formula editor has greatly improved since I first created a workspace.
With that being said, I still find it to be lacking just enough to keep me from fully buying into it.
I wouldn’t take that as a negative. I just ask a lot of the formula editor.

I’ve found that writing my formulas in Visual Studio Code best fits my style of working through things.
The overall reason I prefer to write in an IDE comes down to documentation.

I write a lot of documentation.
Whenever I write a challenging or unique formula, I write documentation around it that includes things like:

• Context for its creation, such as the forum thread, screenshots, etc.
• The rubberduck summary of what it does.
• The technical summary of how it works.
• Things that would break it, as well as any other methods that could be taken to achieve a similar result.
  • When it comes to the forums, I save other people’s formulas that took different approaches and document those differences and contexts.

It all goes into a private repository that’s just full of use case documentation, formulas, designs, and schemas.
Now that I’m writing this out and staring at it, I should probably just open source it all lol.

My workflow is overkill, but for anybody else in the near future that’s curious to see how other people handle the process of building formulas, this might be a good insight into finding a method that works best for you.

It may also be that I started writing formulas before you. We probably see the same improvements but I am comparing to when the formula editor was little more than a single line text field where you could not type multiple lines at all.

I can now type multiple lines and bracket matching works for me. Keyboard navigation hot keys are still not as intuitive as I would like, but if I don’t have a code editor open already, dealing with the native formula editor’s quirks is still faster than using a code editor.

Using Adam’s trick for automatically enlarging the formula editor helps too.

Your method of writing formulas sounds awesome but also very time consuming. I’m guessing that you spend at least as much time documenting your formulas as you spend writing them.

I admire your output, and your explanations of your formulas are great. It sounds you have built a great resource.

For me personally, I found that at one point it was faster and easier for me to recreate most formulas versus looking them up. And any other formulas tended to be so specialized that it was unlikely that I would ever use the exact same formula again.

Plus, I decided to not return to a career in technical writing in part because I realized that I don’t enjoy writing documentation as much as I used to.

Instead, I decided to take a different approach. I decided to develop a writing style that works really well for me. My style tends to use a lot of lines, but it also has greatly sped up the time it takes me to write, debug, and re-read formulas.

Thanks for the ideas everyone. In the end I just selected all of the latest rows of the table I was interested in and then ended up doing the filtering in javascript which was 10x easier. I’ll probably just stick with that approach as much as possible.

Glad to hear you found a solution that works for you.

However, if you plan on continuing to use the REST API, I encourage you to continue trying out filterByFormula. Once you get the hang of it, it is particularly useful when your tables get large.

out of curiosity, what do you do for your new career?

Working with Airtable is my new career!
I used to be a technical writer for the software industry.