Organizing several select list columns

4

Hi Joanna, unfortunately in Coda it is not possible to programmatically create a list of columns or iterate through columns, which means that making a formula to—as in your example—list all classes below grade level, is not trivial.

There are though at least two possible solutions that I can see, but both of them take work. The first is simple, tedious, and fragile. The second is more complex yet more robust.

In either case, I would highly suggest turning the select-list columns into a table lookup like so:

  1. for the first column, you can do it like this:
    image
  2. and every other column after like this:
    image

(I would suggest putting all possible answers into that lookup table, even if they are referencing different questions—eg “yes, no, below grade level, sometimes, etc”).

Then…

Option 1: Hard Coding

You can write a formula for each of grade levels, skill levels, etc something like the following:

List(
  If([Choose Student].[Academic levels \[Reading\]]=thisRow, "Academic levels [Reading]", ""),
  If([Choose Student].[Academic levels \[Writing\]]=thisRow, "Academic levels [Writing]", ""),
  If( etc, etc...
  ...
)
.Filter(IsNotBlank(CurrentValue))

(check out my edits to the test doc you shared)

The problem with this option (other than the fact that it will be tedious to type out the whole thing because you have so many columns) is that as soon as you make any change in the number of columns or names of columns, you have to mirror that change in the formula (which is why I said it was fragile.)

Option 2 is to completely redo the schema. This is a lot more work, more complicated, but in the end you will have a much stronger database more resilient to future changes and better suited for further analysis.

I will post here again tomorrow with that one, because I have to sleep now, but if you want a headstart, check out this thread:

1 Like