New formulas for columns

Suggestion Box
1

Goal

Dynamically select a column from a table

Issue

The current possible approach forces you to repeat yourself into a coupled mess:

Select Options Formula

List("Population", "Area", "Density", "GDP", "Elevation", "Temp")

Dynamic Column Formula

Switch(
  [Select],
  "Population", thisRow.Population,
  "Area", thisRow.Area,
  "Density", thisRow.Density,
  "GDP", thisRow.GDP,
  "Elevation", thisRow.Elevation,
  "Temp", thisRow.Temp
)

If you want to add a column you need to modify information in at least three different places, very prone to error and a nightmare to maintain, not ideal!

Proposition

Add new formulas:

  • Columns(Table)
  • ColumnName(Column)
  • ColumnType(Column)
    …

With these formulas we could make a truly dynamic solution - the above example could become:

Select Options Formula

Columns([Table]).Filter(ColumnType(CurrentValue) = "number").FormulaMap(ColumnName(CurrentValue))

Dynamic Column Formula

Columns([Table]).Filter(ColumnName(CurrentValue) = [Select]).First()

Notes

Working example of the current solution

Columns(Table)

Would return a list of Columns, equivalent to:

List([Table].Population, [Table].Area, ...)

Would probably require the engine to be smart about how it represents a column, I have no idea how it currently works under the hood. I’m afraid it will process all cells in the column for each column, when all you might want is the name of each column

Doc Explorer Pack

@Scott_Collier-Weir’s pack could help with the Select Options Formula, but not the Dynamic Column Formula I believe

Additional formulas

If this was to be implemented then there are additional similar formulas that could be considered, as well as thisColumn - That would be amazing!

4 Likes
2

Another use for this would be to perform operations that involve many different columns, such as finding the maximum across 50 columns, or the latest modified column across 70 columns.

A challenge for these kinds of formulas is that you have to manually hard-code dozens of column names, which is a nightmare to maintain.

One possible solution would be for the proposed column formulas to run against views (similar to how you can open row details to a specific view, in order to get different detail layouts).

For example, if we have a table with 300 columns, we can create a view of that table with only the 50 columns we want to use in the formula (let’s call it “SelectedColumns”).

Maximum across columns

Columns(SelectedColumns).Max

Latest update in the selected columns

Columns(SelectedColumns).Modified().Max

This way, the functionality would be governed by which columns you choose to add to a view, which is way easier to maintain than manually hard-coding column names.

1 Like
3

I love this and really hope Coda makes it possible to properly address columns from formulas.

I just want to emphasize the importance of thisColumn for identifying the column in which a formula is running. Right now a formula can address its row with thisRow, but it cannot know anything about its column context. I have already needed thisColumn.name() in a few projects.

1 Like
4

Originally coming from Excel I know the temptation of putting variables in columns, but I cannot remember now any case where improving the data model didn’t solve my need for getting the column with a formula.

Why don’t you just use a data-model like in this example with a data point per row ? With grouping you can simulate the original layout if that’s more convenient for visualization or data input.

2 Likes
5

Oh yeah you’re right! And come to think of it, I haven’t felt the need for this suggestion in a long time, as I’ve gotten more familiar with how Coda works

I wonder if it applies to all the related posts :thinking:

3 Likes
6

Actually @Rickard_Abraham , there’s one use-case that I come up frequently with where column formulas would be helpful.

I usually build ‘kind-of forms’ with detail views and buttons disabled by complex sets of validation rules. Some of these rules are often checking if some fields are empty and I end up doing stuff like this to let the user know why the button is still disabled.

list(
  if([Field_1].isblank(),"Field_1",""),
  if([Field_2].isblank(),"Field_2",""),
  [...]
  if([Field_n].isblank(),"Field_n",""),
).filter(currentValue.isnotblank()).join(", ").let(EmptyFields,
if(
  emptyFields.isblank(), 
  "✅",
  format("❌ The following fields cannot be empty: {1}", EmptyFields)
)

if the column formulas existed I could reduced the first part to something like this:

list([Field_1],[Field_2], [...] , [Field_n]).forEach(
  if(currentValue.isblank(),currentValue.ColumnName(),"")
)

So you have my vote too!

3 Likes