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

[Alex_Long1]

Having to type formula strings when using something like Node.js to interact with the API is super difficult and tedious, especially with more complex queries. Since the formulas are strings the editor can’t provide any syntax correction tips about missing parenthesis or valid/invalid uses of various functions like FIND, AND, etc. Is there any easier way to create these queries? I put an example query I’m trying to create below which I’m completely failing to get working, although I have no idea why because there’s zero syntax feedback. I made it by joining an array of indented strings to provide myself with a little debugging sanity but that only helps so much.

  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'))",
    ")",
  ].join('')

[WilliamPorter]

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:

[kuovonne]

WilliamPorterTech:

The formula editor in Airtable has improved a little

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.

Alex_Long1:

Is there any easier way to create these queries?

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')
  )
)
`

[Ben_Young1]

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.

[kuovonne]

WilliamPorterTech:

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.

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.

[kuovonne]

Ben.Young:

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.

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.

[Alex_Long1]

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.

[kuovonne]

Alex_Long1:

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.

[Dustin_Good]

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

[kuovonne]

Dustin_Good:

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.