A column can have both a formula for value and display

At the moment a cell is either driven through formula, and is uneditable, or is editable.
This particularly becomes a hassle if you want to display or style the value in some way while still having it be editable. Formatting gives us some control, but only limited:

One use case is where the community has hacked around the issue of wanting to display a hierarchy beyond a single layer deep (using Grouping). The solution there was to have a Parent and Name column so you could edit both values, and then another column that offsets the Name value based on the depth of the parent hierarchy:

In the above example, only Parent and Name are editable, which means I need to display Name if I want to quickly add in rows or edit them. If the Name column could have a style or display formula that does this, and double clicking would give you the editor for the underlying value, this would be handled.

1 Like

This is actually probably a lot more work than it seems. I am not certain how formulae are implemented under the hood, but this would require at least identifying the dependencies of the formula and recursively traversing these dependencies to find the source where they are driven from. For example finding which columns a formula depends on (which could be across tables), then finding which columns those columns depend on, recursively until you hit the source, which may or may not be driven by user input. You would then need to identify all these sources that are user inputs, expose them for user input, and update the values cascading down the dependency chain.

This is not only potentially very costly performance-wise but also impossible in terms of UI. What happens if a column is the average value of another column for all the rows of another table, how do you open all the maybe hundreds of fields for that column as inputs? What happens if a formula depends on dozens of other user input columns, which ones to you expose for user input? etc.

One approach would be to allow formulae to be parameterized as functions, meaning they can access the global scope of the system (e.g. references other tables, etc.) but also rely on named input parameters that will be supplied when the function is called. This would be identical to making formulae into the programming equivalent of “functions” e.g. myFormula(firstParameter: firstType, secondParameter: secondType, ...). So formulae of this type would have a definition (same as what formulae already have), but also an additional section for the “call”, where you would call the formula and supply its arguments for the parameters (this could itself be a “pseudo” formula environment where you can again access the global scope to construct and supply the arguments).

In this model, some of these named parameters could be flagged as exposed for user input, such that when you click on the cell for that column it pops open the input boxes for those parameters. Eventually it might be feasible to automatically identify which parameters simply pull from another column, and automatically make those exposed for user input with a bidirectional mechanism that updates the value in the source column.