Volatile input columns — or — Variables in formulas

As Coda users want more and more complicated calculations in Coda, a pressing need for some temporary key-value storage arises. In conventional programming we use variables for that, e.g.

var result = /** some complicated filter **/
if (result.size > 0) {
  result.doSomething()
}

to avoid calculating the result twice.

In Coda the closest analogies are:

  • in column formulas — you can extract pieces of calculations and store them as separate columns whose value you can then reuse.

    In simple cases that’s okay, but with things like working around the CurrentValue context problems, the only working solutions become unapologetically complicated.

    Simple or not, such extra columns that exist solely for the purpose of breaking down a calculation into steps still take up extra doc space.

  • in actions — you can store intermediate values in dedicated cells, like I show in the Iterator tables approach.

    This works, but it also generates a ton of unnecessary edit operations that still get sent to the Coda servers and all the collaborators. Coda simply doesn’t know that I’m using the cell as a temporary storage — it thinks I want to edit its value for 1000 times per minute for real. Because why else would any sane person call ModifyRows() on it?

I see two solutions to this.

  1. Add variable support in formulas.

    This is ideal but tricky. The problem is formula syntax. In action formulas we could write something like:

    Tasks.FormulaMap(RunActions(
      SetVariable("Current task", CurrentValue),
      Projects.Filter(
        CurrentValue.Tasks.Contains(GetVariable("Current task"))
      )...
    ))
    

    i.e. we can code multiple steps in an action formula. But we cannot do that in column or canvas formulas — those can only contain one function that takes inputs (which could also be functions) and produces an output, so no way to call things like SetVariable() within such formula.

    Knowing the limitations of Coda, I’m not ready to advise any solution on this front. Maybe the rest of the community can chime in.

  2. Make it possible to designate specific columns as in-memory, i.e. not stored to the doc.

    Coda already does this with volatile formulas — i.e. those that directly or indirectly rely on User(), Now(), or Today(). These are only calculated in-memory, in a user’s browser. I suggest to extend the same capability to any arbitrary columns that I mark as volatile.

    It will not be a complete solution, but it could be a non-breaking, relatively easy to add capability that would solve some of the problems listed above. At the very least, it will not bloat the doc size nor spam Coda servers with so many meaningless edit operations for values that are only needed temporarily.

5 Likes

Could you do something like a “SetTemp(number, value)” that would then let you call Temp1, Temp2, etc, but only within that specific call of the formula? That way you would avoid the issues of document-wide variables, the use would require no assumptions about carrying over values, and you could just place a restriction on its call.

(This may be stupid, do not hesitate to point out if it is! It is just something that I have thought would help me with the kinds of issues I have run into and @Paul_Danyliuk has helped me solve!)

I’ve just discovered that Airtable has scripting (like, real JS).

If Coda supported something similar, that would solve all the issues in the world (of Coda).

Everyone would have tools for their expertise level:

  • Basic users would set up filters and buttons via the dropdown UIs.
  • Mid-level users would write formulas and actions.
  • And power users who need more would have access to fully featured scripting.

Creating named functions (like MyFunction(arg1, arg2, arg3)) and actions (like AddAllCombinationsOf(rows1, rows2)) via a JS editor with some API exposed (in-browser access to cell values, not the remote API calls) would be a dream come true.

8 Likes

Curious to see where we currently stand on this topic now that the Pack Studio is a thing?

1 Like

The topic is of course obsolete for two reasons :slight_smile:

  1. For in-memory variable storage, we now have WithName(). It solves most of the things I mentioned in my original post.

  2. And of course there’s custom packs now. It’s not the scripting I had in mind (I thought there’d be client-side scripting — an alternative to the formula language that’s executed right in users’ browsers) and using packs comes with some calculation delays, but it’s a very powerful new capability for sure.

2 Likes

Thanks Paul! Does this make Iterator tables obsolete as well?

Hi Nate! I am part of the newly formed Coda Success Team, here to help customers scale and optimize in Coda. It is nice to e-meet you!

I would love to learn more about your use case for iterator tables and what the best solution might be. You can set up some time for a call by clicking this link.

1 Like

I still use iterator tables sometimes because they give better visibility and debuggability into what’s happening. You cannot run through your single complicated WithName-loaded formula step by step but you can do that when you have it as an iterator table broken down into simpler steps.

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.