While I was creating my dependency graph I noticed that I had to copy the same formulas many times and slightly altering them. And those were very long and complex formulas, so that took a lot of time and it was really easy to make a mistake.
That’s why I propose a new function - RunFormula(). This function would calculate value in current row in column passed as first argument.
But how does that differ from thisRow.Column?
This function would also accept additional arguments used to modify the formula.
This format would be similar to the one of SwitchIf - arguments come in pairs, the first one represents value to be replaced and the second value to replace the first one with.
That means you could replace certain values (such as columns, rows or tables) with different values of the same type. This would allow to use formulas in a very function-like style.
A simple example
Let’s say we have a table with fours columns - “Value 1”, “Value 2”, “Formatted value 1” and “Formatted value 2”.
“Formatted value 1” has a formula if(thisRow.[Value 1].IsBlank(),"None :(",thisRow.[Value 1]+"$"), which nicely displays data for column “Value 1”.
Now we want to do the same for “Formatted value 2”. With this function, instead of having to write if(thisRow.[Value 2].IsBlank(),"None :(",thisRow.[Value 2]+"$") you can write RunFormula("Formatted value 1", thisRow.[Value 1], thisRow.[Value 2]).
Why is it better?
All updates to formula in “Formatted value 1” are automatically applied to “Formatted value 2” (which is extremely time saving if you have more than two similar formulas) and for more complex formulas you don’t have to worry about accidentaly changing and breaking the formula (which can save a lot of debugging time).
A more complex example
At the end I would also like to link to a real-life example when such a function would be very useful, the previously mentioned dependency graph.
Please note the differences in formulas in To-Do and Suggestion box/Formula dependency sections. The second one is there to show how it would like if such a function was implemented.
Personally I’d be very much in favor of being able to define inputs and outputs on formula - upgrading them to full functions. That way we can easily break formula apart.
Ideally this would exist as a document, or even user-wide library of functions, not embedded in specific pages.
You are right that global scoped function-like formulas would be even more useful.
However, I’d imagine that in most cases those formulas would be used only for certain tables, and when working on a big project it would create a lot of name clutter.
That’s why I propose to use something like a “formula table” (which would resemble a class). Each “formula table” could contain multiple functions, which would be called Table.Function(). This would significantly improve readability of such functions.
Even the interface/mechanism behind them can be similar to the already implemented table functionality.
Each function would be a different row, and the columns would be as follow: Name - used in referencing this function Output - this the column that actually holds the formula which result is returned Arg 1, Arg 2, Arg 3… - pseudo-columns which are used for holding arguments, and can be referenced from the Output formula in the exact same way as you normally reference values for given row from different columns.
Whenever a function is called, it would just fill the argument pseudo-columns with parameters and return value from Output.
Obviously there are some things that differ from the standard table implementation:
Name column can’t have a formula as it would create an enormous amounts of problems
Each Output needs to have a row-independent formula, otherwise you will always end up with multiple functions with exactly the same functionality
You shouldn’t be able to reference argument pseudo-columns from outside Output formula, because that would be weird
Formula execution order is crucial - you need to make sure that you’ve finished calculating the Output formula before you start calculating another formula, as it may also use the same function. (Or you don’t even have to fill those argument columns, you can only “simulate” it, which would allows for a parallel execution of multiple instances of the same function.)
You can condense the numbered argument columns ( Arg 1 , Arg 2 , Arg 3, etc) down into a single column with a multi-pick list, same for the output. I’ve done the same with a small game jam project I did where functionality was generated based off of a Coda document.
Sorry, I don’t have anything public. I joined late (last day) and used it more as an excuse to stress test Coda / see if the workflow would work. I might expose things later, but the problem is that the code is either trivial (just uses the Coda API), or it generates code that uses our in-house ECS framework.
What was very interesting was giving designers an easy way to play around with merging functionality concepts.
