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!

2 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