Iterate row contents (cells)

I have two tables, one with priorities, one with items being prioritized.
Priorities looks like:

Priorities
|Name|Value|
|P0  |0    |
|P1  |1    |
|P2  |2    |

The second table looks like

MyTable
|ItemName|PrioAvg|PTeam1|PTeam2|PTeam3|
|Item   1|       |P0    |P2    |P1    |
|Item   2|       |P0    |P2    |P2    |
|Item   3|       |P2    |P1    |P0    |

I would like to use a formula like the following in the “PrioAvg” column so I don’t have to call out each column by name every time I create a table and need to process sets of neighboring cells.

average(
  filter(thisRow.Cells, Priorities.Contains(CurrentValue))
  .foreach(CurrentValue.Value)
)

Unfortunately “thisRow” only returns the key/first cell.

Ignore the implementation details; they’re not important - values could be extracted by string manipulation or whatever. The issue is that I need to iterate on cell contents for the given row and do whatever transform to them I’d like without calling each out by name.

It’s sad to see this explicit calling of columns across our docs, with hundreds of tables and dozens of columns. It’s the data manifestation of carpal tunnel syndrome.

I’m also not interested in adding all the priorities to a single column to iterate on a single cell’s contents. It’s ugly and makes visually browsing the table a nightmare.

The issue here is trying to translate Excel paradigms to Coda or other databases. It is not a good practice to have data in column headers: ‘Pteam1’, ‘Pteam2’ and ‘Pteam3’ should be values in a column.

You can have your table with a proper structure on a hidden page and still use grouping to visualize the data in the way you like.

Take a look at my examples in these posts:
Is it possible to filter columns just like rows? - Ask the Community - Coda Maker Community
New formulas for columns - Suggestion Box - Coda Maker Community

Hope this helps,

Pablo

3 Likes