I’m trying to get a value from a table based on a row and a column combination. The following example works fine since there ins a row with “Session” value in the Concept column and then the command returns de value of the CO3 column of that row:
Table.Filter(Concept="Session").CO3
What I need es to get the column selected dynamically. I mean, depending on the value of a selector, it should return the value of the column CO3, or CO4, or any other.
How can I convert to formula the “CO3” part?
Have a look at the Switch() and SwitchIf() formulas.
Unfortunately they don’t provide de functionality I require. I need a much more flexible option since the table and combinations are quite large.
We don’t yet have programmatic access to columns. If that’s what you want, common solutions I’ve seen are to restructure your schema or use switch statements.
It would be nice to be able to programmatically access columns. However (for any reader who will find it helpful) in most cases it really is much better to change the schema. Compare and contrast the “matrix” schema of attendance to the “database record” schema below.
You can replicate the display of the first schema by grouping the second schema, and the second schema is much more powerful when it comes to access by formula and even moreso when you start doing graphs and visualizations.
For the additional consideration of future readers:
In my experience I find that the correct decision here is driven by use case.
In most of my use cases (~70%), restructuring the schema would generate too much additional overhead which introduces a whole other set of problems. In those cases I often go with switch statements that blend programmatic design and hard coding (something I vigorously try to avoid, but which in these cases is better than doing nothing).
In the ~20% of the cases where restructuring the schema solves the problem, the schema usually needed to be restructured anyway, and the desire for programmatic access to columns merely brought to light the larger schema issues.
In the remaining ~10% of cases, there is no viable solution.
Per unique user, how that ratio skews will depend on which types of uses cases predominate your work in Coda.
You can also see these percentages at work here in the forum, where posters never really get a satisfactory answer to this common question.
Yes.
I have a use case right this moment that needs this access. There’s no way the schema can be restructured, and there’s no way switch statements will work. So I’m going to see if I can hack a solution using the other tools in Coda. But it would be easier to just programmatically access the columns, which after all, are just a list of objects on a table that already have unique machine IDs and unique human-user IDs.
Our of curiosity, Ander, what is that use case where the schema can’t be restructured and there’s no way switch statements will work?
As for @Giovanni_Sades’ case, I’m assuming based on the names of the columns (CO3, CO4, etc) that all those columns are the same data type and together serve one purpose. In that case (which may be different than other cases) it can be combined into two columns: one column for the value, and another column specifying “CO3” or “CO4” etc.
And if you do find a hacky way to access the columns (maybe through ParseJSON() or through cross-doc actions…) please share in this thread, I’d be interested!
(1) There have been several of these cases over the years. I’ve just built workarounds or gone without the desired functionality, and moved on. I don’t recall their specifics.
(2) I’m sure your proposed solution for Giovanni is solid.
It might very well be the way to go in his use case.
I added that comment because this is such a common question here in the forum, and I’ve grappled with it in my own projects repeatedly, and there are always two solutions offered in the forum to address it, and after much experience with this particular problem I’ve accrued enough data that I felt it was time to bump this conversation to the next level, which to my mind is this:
“Given that these are the two widely recognized solutions to this particular problem, what does it actually look like in practice to implement them, over many various implementations?”
And so I shared the rough data set of my experience. The data sets of others will certainly skew differently than mine.
In general I promote correct schema as the cure for most problems in Coda, because it’s the foundation upon which everything is built. Wonky foundation == wonky build out.
But this issue of users wanting programmatic access to columns is not truly solved via schema restructuring – at least in my experience. From a data structure standpoint, schema restructuring does technically solve the problem. However, from a human user standpoint, it will often gin up more data structure than the user actually wants, or has the time and resources, to manage and maintain, just to accomplish one programmatic task, which is often the decisive factor for the user.
It’s an interesting problem that many users run into (it’s got to be one of the most frequently asked questions in this forum). And yet, there’s no clean answer to it.
Programmatic access to column values cuts against the grain of how relational db’s are designed. I’ve never seen this functionality in any other app (though it may well exist). But there’s clearly demand for it.
Coda is already successfully reimagining the UX for working with relational data (thank you!). I think they could tackle this problem at some point. I think the Coda engineers could write this algorithm and expose its results via formula and UI. And then users could have their cake and eat it too!
(3) I never incorporate those kinds of wild hacks into my business. It’s bad practice and I strongly advise against it. I meant that I would try to build a solution that did not require that level of access to the columns.
This is a very good point. And another example of where database theory and user-friendliness are in conflict with one another in Coda. Not bad thing, just a reality — and one that Coda deals with very very well. So it will be interesting to see what happens in this particular case.
I am still genuinely interested to hear about one of your examples of when a schema restructuring is insufficient, or undesirable in some way, if ever you recall one 
I’ve got a good example for you.
I have a table of templates and a button to append those templates to Canvas columns in a table. What I’d like to be able to do is apply different templates to different columns based on what column they are intended for. Without programatic access to columns this doesn’t seem possible and, as far as I can tell, there isn’t a way to restructure the table to allow for it. This means I would need entirely separate buttons for every column in order to allow importing templates into that column.
Interesting. How many canvas columns do you have on that table? Would it be too unwieldy to use a Switch or SwitchIf formula?
A switch case has been my workaround, but it’s rather tedious to maintain and may become unwieldy past a small number of columns.
An example I’ve got –
I’ve got an export from a separate service that has ~40 columns for “Objective” based on Objectives added (think of the rows as a Product Feature, and columns are a matrix tie-out to the Objectives they are tied to)
We constantly add and delete objectives, so the columns when I export keep changing. I’d like to be able to loop over the columns so I can change the schema to the ways suggested above, but I would have to do that with 40 SwitchIfs and keep up with it, versus just doing a For loop and capturing any column with “Objective” in it
I’ve made a suggestion here that I believe would solve this