Re: Editor for complicated formulas

Michael_William · ‎Mar 28, 2018

Does anyone use an external tool for viewing/creating/editing complex formulas? Is there a tool that you like to copy and paste your complicated formulas into? Notepad++, Visual Studio? I know you would lose the linking to existing fields and the function lookup, but I’ve been struggling with some of my really long nested if’s.

Thanks,
Mike

W_Vann_Hall · ‎Mar 28, 2018

Yes: Notepad++ — but I expect any editor with automatic indentation and paren matching would do.

I also have Np++ set for a four-character tab step and to replace tabs with spaces; this latter setting allows me to copy-and-paste indented code into Airtable formula and aggregation function configurations and have it maintain indentation if (when) I want to copy/paste it back to Np++ from Airtable.

The functionality I lose by not being able to select existing fields or functions is far more than outweighed by the assistance it provides when debugging formulas. (Actually, Np++'s standard autocomplete feature helps obviate much of the loss; if the full name of the field or function already exists somewhere on the currently active Np++ tab, the program pops up an unobtrusive selection box as soon as the first character matches.)

Evidently, it’s not on this PC, but I recently noticed at some point I had started to create a user-defined language file for Airtable. (What I was trying to achieve, I don’t know; I simply remember seeing an ‘Airtable’ option at the bottom of the ‘Language’ menu.) I suspect I initially hoped to create an online function reference to substitute for the one built into the Airtable editor but stopped once I realized Np++ wouldn’t support such a thing.

In practice I’ll often pop back over to the Airtable window and enter the first few characters of the function I want to look up into the formula configurator; once I’ve refreshed my memory, i return to Np++. Since I always replace everything in the configuration window with whatever I’ve just finished offline, I don’t care if Airtable’s copy of the old formula becomes garbled.[1]

Let me close with a formula I recently admitted in public to having written; it’s from a demo base I created as part of a ‘Show and Tell’ post on ‘pretty-print’ routines. While part of me still recoils from the thought of actually having done this on purpose, the code does work as intended, and I’ve yet to find any way substantially to improve it:

IF(
    StringValue,
    IF(
        LEFT(StringValue,1)='-',
        '-',
        ''
        )&
    '$'&
    IF(
        FIND(
            '.',
            SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),'-','')
            )=1,
        '0',
        ''
        )&
    IF(
        MOD(
            IF(
                FIND('.',StringValue),
                FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                ),
            3)=0,
        IF(
            FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))!=1,
            LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''),3),
            ''
            ),
        LEFT(
            SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''),
            MOD(
                IF(
                    FIND('.',StringValue),
                    FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                    LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                    ),
                3)
            )
        )&
    IF(
        LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))>IF(FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-','')),6,3),
        ','&
        MID(
            SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''),
            IF(
                MOD(
                    IF(
                        FIND('.',StringValue),
                        FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                        LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                        ),
                    3
                    )=0,
                4,
                MOD(
                    IF(
                        FIND('.',StringValue),
                        FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                        LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                        ),                    
                    3
                    )+1
                ),
            3
            ),
        ''
        )&
    IF(
        IF(
            FIND('.',StringValue),
            FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-','')),
            LEN(
                SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-','')
                )
            )>
            IF(
                FIND('.',StringValue),
                9,
                6
                ),
        ','&
            MID(
        
        
                SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''),
                IF(
                    MOD(
                        IF(
                            FIND('.',StringValue),
                            FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                            LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                            ),
                        3
                        )=0,
                    7,
                    MOD(
                        IF(
                            FIND('.',StringValue),
                            FIND('.',SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))-1,
                            LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(StringValue,'$',''),',',''),'-',''))
                            ),
                        3
                        )+4
                    ),
                3
                ),
        ''
        )&
    IF(
        FIND('.',StringValue),
        IF(
            FIND('.',StringValue)<(LEN(StringValue)-2),
            '.'&
            ROUND(
                VALUE(
                    '.'&RIGHT(
                        StringValue,
                        LEN(StringValue)-FIND('.',StringValue)
                        )
                    )*100,
                0
                ),
            RIGHT(StringValue,3)
            ),
        '.00'
        )
    )

(Be sure to scroll the inner window, too; you don’t want to miss a line of such elegant code…)

Putting aside for the moment the possibility a ‘no’ answer might be a good thing, would it even be possible to develop such a formula using the standard Airtable editor — or, at least, to do so and still retain one’s sanity?
.__________

My workflow goes something like this: Right-click field; select ‘Customize field type’; click into formula editor textbox; Ctrl-A; Ctrl-C; mousedown to Np++ icon in taskbar and select; either (1) select Np++ tab to use or (2) click ‘New file’ icon; Ctrl-V; edit formula; Ctrl-A; Ctrl-C; mousedown to browser icon in taskbar and select; click into formula editor textbox; Ctrl-A; Ctrl-V; click ‘Save’ button; test. Rinse. Repeat.

Edit: I actually far surpassed the formula above as my example of what not to do when programming Airtable: I created an IF() statement with 468 branches and extremely long options, so the total length of the formula exceeded 34,000 characters. It failed, giving me #ERROR no matter what I tried. Finally discovered Airtable seems to have a hard limit of 353 branches per formula. This is something else I’d never have found — nor never attempted, for that matter — without Notepad++.

Mike_McCarthy · ‎Apr 01, 2018

What do people use for complicated math formulas? Back in the day, there was a very expensive software to which my school provided access. I’m looking for something a broke person can afford.

An example of a math formula is shown using this link: Quantum mechanics formulas

Thanks,
Mike

Andy_Lin1 · ‎Apr 02, 2018

You might want to create a new thread with this request, since it’s quite different from what the original poster is asking for.

But to answer your question directly: LaTeX is perhaps the most obvious place to start (Wikipedia pages that display math equations use the same syntax), and it’s absolutely free (there are paid suites like Scientific Workplace that offer a nicer interface and more powerful management tools).
Microsoft Word had an equation editor bundled in since 2007 (if not earlier), but it was patched out in January 2018 due to a security concern. (There is a third-party patch that restores it though.)
If you’re feeling particularly lazy, you can also grab screenshots off Wolfram Alpha.

Mirka_Mandich · ‎Apr 17, 2018

Made a new thread for a request!

Ptt_Pch · ‎Apr 22, 2018

I’ve got a tiny and silly question about texts editors and formulas in Airtable :slightly_smiling_face: .

I bought Coda (I’m on Mac) few months ago for something completely different than Airtable (mostly doing HTML, XML and CSS).

I was thinking I could use it to write formulas but also to track the ones I already used, the ones I could try and the ones that works. :slightly_smiling_face:

So, my tiny and silly question is :
When writing an Airtable formula on a text editor, what syntax do you use to highlight the formula ? So it looks clean, easy to read and stays clear if modified :slightly_smiling_face:
What works best ?

I know writing formulas is not coding, so the syntax used is trivial but I’m really curious about that :winking_face:

W_Vann_Hall · ‎Apr 22, 2018

Frankly, I don’t. :winking_face:

As you note, Airtable syntax is very basic, so beyond maintaining current indentation level and automatic pairing of (/)s and {/}s — which for most editors can be enabled without need of an active syntax — I don’t bother. (And I usually have automatic pairing disabled, as well, preferring to use the editor’s post facto pairing highlighting to check the formula before pasting it into Airtable; something about automatic pairing interrupts my flow.)

As I noted elsewhere (maybe earlier in this thread, actually), at some point last year I must have begun creating a user-defined Airtable syntax for use in the [Windows-only, alas] editor, Notepad++, as it remains an option in my NP++ install. IIRC, I abandoned the effort once I realized such definitions did not support the functionality that inspired the project in the first place: namely, the provision of context-specific reference to the order and meaning of function variables. Truly, that is the one thing I sorely lose by composing formulas offline.

Ptt_Pch · ‎Apr 23, 2018

As I’ve read many of your very interesting posts and topic in the past few days, I thought you might say that :yum: .

Thinking about my question yesterday evening while playing with Airtable formulas in Coda, well I discovered that I won’t need highlights very long :winking_face: .
Because you’re right, the Airtable syntax, once you’re beginning “to get it” is simple. (I haven’t done DB in years so I’m very rusty, but I’m learning :winking_face: ).
But I can’t deny the fact that they actually help me to write and read my formulas even though I can see now how ridiculous my question was :yum: . And I assume it plainly :slightly_smiling_face: .

Concerning the auto-pairing, it has its advantages and inconvenients.
And I do understand that it can broke your flow as at some point, yesterday, it broke mine too while I was still testing Coda to write Airtable formulas.

Anyway, thank you for your honesty and enlightenments :slightly_smiling_face: .
This helps me a lot to see things differently and go out of my own beaten paths :winking_face: .

Airtable_Clerk · ‎Dec 17, 2020

I take advantage of Airtable’s preservation of white space and line breaks in formulas to copy/paste between Airtable and VS Code. (https://code.visualstudio.com/docs)

VS Code is free, multiplatform, well supported, and has many third-party plugins and extensions.
Since Airtable formula syntax isn’t radically different from JavaScript, I tell VS Code that the formulas are JavaScript and it does color coding/highlighting and parenthesis matching; great for debugging.

To document the logic in large bases, I copy/paste each formula in a complex table into a single file. I then document each field as if I could submit the whole file back into Airtable. I add JavaScript comments for readability before each field, place the field name into the final line of the comment with an equals sign as the last character in the line.

// My field name=

The resulting file let’s me quickly see the relationships between fields. I can quickly move back and forth in the file. It’s a bit of a pain to keep the file up to date, but when I update complex formulas, I usually need to copy them into VS Code anyway to quickly find syntax errors.