Help

Re: Trig Functions Formula?

2644 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Bo-Fone
4 - Data Explorer
4 - Data Explorer

It’s 2020 halfway through. Are we getting trig functions in formula field? I need to have the ability to instantly calculating values when some fields are populated (no script block manual triggering).

28 Replies 28

Ho @Bo-Fone and welcome to the community!

Okay - I predict you will see triggered (automated) script blocks before you see formulas to support this. That leaves you with one out (in the meantime) - server-side javascript calling into Airtable via the API and updating fields. And by “server-side” I mean something like Google Apps Script or NodeJS with Google Apps Script getting my vote because it’s the least costly and fastest time-to-production route.

I’ll make another prediction, trig functions will eventually appear in Airtable but not as native formulas; instead, as Scripts in Blocks when script blocks can serve as the development environment for extensible formulas.

What trigonometry functions are you looking for?

I definitely wouldn’t hold your breath on this one. There have been ZERO improvements to formulas in Airtable since March 2016. It is one of the top areas of Airtable which is in desperate need of a major upgrade, and yet it has gotten no love, no improvements, and no attention from the Airtable team in over 4 years.

In addition to the major overhaul that is needed for the formula functions themselves, even the formula box itself has problems: it chokes on curly quotes, it doesn’t tell you where the error in your formula is, it condenses long formulas that were once nicely spaced out with spaces & carriage returns into one indecipherable blob of text, it has no color coding, etc.

You’re either going to have to:

  1. Turn to JavaScript to help you with this, but scripts are not automatically triggered like formulas are.

or

  1. Turn to an external website to help you with this, and use an automation tool like Integromat or Zapier to help you automate the process.

:winking_face:

Nice predictions, Mr FRENCH, isn’t it ?

:slightly_smiling_face:

I don’t believe the glu-factories can assist in this requirement because their integrated scripting environments don’t support trig functions (last I checked). And this is why I said “server-side javascript”; it has to be a script environment capable of performing the math computations that @Bo-Fone wants to do.

I have a hunch that this upcoming feature will make this process a little easier:

My data shows that no less than 71 feature requests in the community will be impacted in a favorable way by this feature. But that’s really the tip of the iceberg. Imagine all of the complicated Zaps and Integromat machinations that could be rendered needless with this capability. Surely not every external process can be displaced, not can many of them be displaced easily - but a fair number will vanish over time and move closer to “home”.

:thinking: … I came up with 72… there must be an error in my math.

:winking_face:

Indeed. ElasticSearch is generally spot-on with counts.

Bo-Fone
4 - Data Explorer
4 - Data Explorer

Thank you all for that insightful information! I will try out the new beta features. And in the meantime, I will just open my browser 24/7 and run script block non-stop to achieve this function. Until Airtable dislikes me by doing that or they make some updates. :shushing_face: :smirk:

This is not true. Several new formula functions have been released since then. Off the top of my head, the SWITCH and SET_TIMEZONE functions have been release within the past year. There are more that I don’t remember.

In some cases it tells you where the error is. For example, I have seen it tell me when I have a typo in a field name.

It does preserve the spaces and carriage returns when you cut and paste into an external editor. Those white space characters are still there, the input box just doesn’t handle them well.

Color coding would be nice. A bracket matcher would also be nice. But then you’re talking about developing a whole new editor. For scripting block, Airtable was able to use the Monoco editor with JavaScript grammar. However, Airtable formulas use a proprietary grammar with a vocabulary that can be expand at any moment. Creating an editor for formulas might be significantly more difficult than creating the editor for Scripting block.

There are many aspects of the formula editor that could use improvements, but I feel the situation isn’t as dire as you describe.

BTW, my thoughts on code editors has evolved over the years. They are also heavily influenced by a programmer that I really respect who prefers an editor without color coding, syntax highlighting, grammar checking, bracket matching, a debugger, etc.

@kuovonne

You’ve given an exception to the rule, which is a very nice & welcome exception.

But in 95% of cases, it doesn’t tell you where the error is. Like all other formula editors, it should tell you exactly where the error is — even a character number would be helpful.

It never preserves carriage returns under any circumstances.

To start with, you’re never allowed to type in carriage returns into the formula editor, so you’re required to use an external editor if you want to use carriage returns while constructing your formula.

Next, let’s say you just spent a bunch of time typing up this beautifully-formatted formula in your text editor:

IF(
{Registrant Name},

IF(
OR(Individual="",{Registered Event}="",{Email}="",{Register/RSVP}=""),
"Yes","No"
),

"No Name"
)

Now, try pasting that beautiful code into Airtable, and then SAVE your formula.

Next time you open up your formula, Airtable has obliterated all the carriage returns in the formula, so that it now looks like this:

IF({Registrant Name},IF(OR(Individual="",{Registered Event}="",{Email}="",{Register/RSVP}=""),"Yes","No"),"No Name")

If you try to copy this and then paste it into an external editor, it does not revert back to how it used to look. The carriage returns are gone forever.

By the way, I used an extremely simple example there that you can still sort of read okay in Airtable. That formula above is not very complicated.

But imagine a “Nested IF” formula that has many, many more lines than that. Makes it very difficult to read as a human. And then, upon copying out of Airtable and pasting into a text editor, you don’t get your carriage returns back.

So then, in your text editor, you have to manually put all your carriage returns back again, if you want to read a cleanly-formatted version of your formula.

Not dire, just frustrating. (I bounce back & forth all day long between FileMaker & Airtable, and there are vast differences in usability between the 2 products.)

But yeah, I feel like the need for more functions — and the improvement of existing functions — might be a bigger issue than the formula editor.

Although they really go hand-in-hand — the more functions we have accessible to us (and we already have a small handful of good functions), the more we will need to depend on carriage returns & telling us where errors are located.

Yeah, I don’t really need any of those things, if the formula box would give us THE 3 BIG THINGS:

  1. Telling us where the error is located.
    and
  2. Preserving carriage returns.
    and
  3. Recognizing/transforming curly quotes.

Those are the big 3.

And then, we really need better formula functions.

Here’s a list of all the functions in FileMaker Pro. If they pulled just 5% of the functions from FileMaker Pro, it would make Airtable much more usable.

p.s. @Jason — this post summarizes my top feature requests for formulas in Airtable! :slightly_smiling_face:

[quote=“ScottWorld, post:12, topic:31745”]And then, upon copying out of Airtable and pasting into a text editor, you don’t get your carriage returns back.

I do get my carriage returns back. It’s puzzling that you don’t.

@kuovonne I’ve tried pasting into many different apps on my Mac, and the carriage returns never come back. I’ve tried like 5 different apps. Which app are you pasting into, where the carriage returns are coming back?

@ScottWorld I’m on Windows 10. The carriage returns paste into any app for me: notepad, Atom, MS Word, etc.

Wow! Very interesting! It definitely doesn’t work on Mac. I’ve tried in every Mac app that I can think of — Microsoft Word, Apple Pages, TextEdit, BBEdit, Sublime Text Editor, and many other text editors as well.

There must be some difference between Airtable’s behavior on Mac vs. Windows.

p.s. I should mention that I’m using the web version of Airtable, not the Airtable desktop app.

I’m not sure that it’s a Mac vs Windows difference. Carriage returns in formulas (when pasted after copying from another app, BBEdit in my case) are maintained on my Mac. This is also in the web version, though I don’t think it makes a difference which version is used, as the desktop version is just running a bare-bones web browser from what I’ve been told.

Very strange. Yes, BBEdit is my primary text editor on the Mac, too. I just tried this right now, and it didn’t work for me.

This is what I did:

I copied my 20-line formula from BBEdit (with carriage returns separating the lines) and then pasted it into the formula field on Airtable. Upon saving the formula, all carriage returns were removed in Airtable. Then, I copied the formula from Airtable and pasted back into BBEdit — all carriage returns were still missing.

I’m just using command-c and command-v to copy and paste everywhere.

I’m also using Safari as my web browser.

Are you doing something differently than I am?

If you’re using Safari as your browser everyone on the planet is probably doing something different because nobody uses Safari. Oh wait, now two people use Safari and I know both of them. :winking_face:

Levity aside, I don’t see any mention in your tests concerning how you actually invoke a copy.

There are actually two copy buffers in all modern browsers; some actually employ three. This is why it’s possible to see two (or more) different outcomes depending on a menu-selected copy vs a keyboard-invoked copy. To truly test any of your outcomes, you must explore the outcomes based on which buffers are used.

Bear in mind, some browsers attempt to sanitize anything copied, thus treating a keyboard copy (Ctrl-C/Cmd-C) as if you were copying from a plain text field (like the URL bar). Whereas, the OS-level copy (using a menu selection) doesn’t have this constraint and for good reason. There are also potential sanitization nuances with right-click-copy selections.

And then there’s the non-browser software apps (i.e., anything that is compiled). These each may support multiple copy buffers and employ different copy-sanitization practices as they see fit.

Test these operations requires a 3D matrix. Good luck hunting. :slightly_smiling_face:

@Kasra, do you have any idea why other people are able to copy and paste formulas with carriage returns, but I am not? I have nothing special installed on my bare-bones Mac with just basic copying & pasting abilities.