Combine columns dynamically

1

Hey,
I created this formula that allows me to control an output combining multiple columns, controlled by another table that sets which columns to show and in what order.

All good until here:
Name builder 2024-04-23

Now, I want to be able to add the name of an existing row that was not initially in my formula. Here’s the challenge: I want to replace the current formula listing each item with a formula that could count and check all rows on the ColumnOrder table, find/identify that column in my main table, and add its value.

In a nutshell, add and remove columns without editing the formula.

In the gif above, you can see what I want to do: I added a new row and put the name of the column to find. That’s it.

My formula on the OUTPUT column:

Join("_",
  ColumnOrder
    .Sort(true, ColumnOrder.Order)
    .Filter([on/off]=true)
    .FormulaMap(
    SwitchIf(
      CurrentValue.ColumnName = "A", thisRow.A,
      CurrentValue.ColumnName = "B", thisRow.B,
      CurrentValue.ColumnName = "C", thisRow.C
    )
  )
)

I guess would need to replace the SwitchIf() by something else, which I can’t figure out.

Is it possible?

2

As far as I can tell, you already have the list of rows you want to pass to the Join() in your FormulaMap() object – so simply add Currentvalue.ColumnName in the FormulaMap and you should be good to go!

1 Like
3

Nope, not possible. You cannot modify the formula itself dynamically.

The only context where this is possible with hacks is buttons — i.e. it’s possible to construct a button formula and convert it into an Action object that you can then set onto a button. But in a live recalculation scenario like yours, there’s no way to do this.

P.S. There is technically a way to add a column to your ColumnOrder table that would list all the values from the respective columns, and then concatenate that… but that’d be a massive performance problem. You could do it if your Table won’t grow past 100-ish rows, but after that it’s just not gonna be worth it.

1 Like
4

Thanks, Paul. Yeah, that idea could work, but I will surely have issues with performance, as you said.

To be clear, I don’t want to update the formula dynamically. I’m looking for a way to find a column with the same name as my row value and retrieve its value.

If this is possible, in my specific case, it would identify the column ‘D’ and provide me with the value from that column in the OUTPUT column.

Apparently, it is not possible to find columns by name, right?

5

Yeah, there’s no such introspection capability. “Finding column by name” would either involve getting values through a pack (hence slow) or a ‘black magic’ button with that constructed formula.

1 Like
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.