Organizing several select list columns

[Joanna_Lamb]

I have a table that’s based on google form responses. I’d love to be able to create some sort of template or organize the data in a way in which I could see all the areas in which my student is excelling and struggling. I have several columns in which there is a select list with options such as Never, Sometimes, Often, Etc. And I have several other columns with select list options such as, On grade level, Below Grade level, etc. I would want to see for each student a list of all the ON GRADE LEVELS, NEVERS, ALWAYS, etc.

For example it would look something like this

Below Grade Level: (list all the classes below grade level)
Student Always: (List skills student does always)
Student Often: …

[Ryan_Martens2]

Hi Joanna, you will need to turn on link sharing in your doc in order for us to see it:

Then I and others in the community would love to help you out

[Joanna_Lamb]

Thanks! Just did that. Hopefully it works now

[Ryan_Martens2]

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:
   
2. and every other column after like this:
   

(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:

Selecting column dynamically

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.

[Ryan_Martens2]

Hi Joanna,

This might be overkill, but look at the extra two pages I put in your test doc (I figured showing the structure in the doc would be easier than trying to explain it.) The basic idea is this: instead one or two tables, you have four tables:

• Student Evals
• Areas / Skills: reading, writing, participation, etc
• Evaluation Levels: the evaluation of that area/skill, eg “at grade level”, “above peers”, etc
• Evaluation Responses: lastly, a table whose sole purpose is to link those three together.

In this case, the google forms integration must be reconfigured to input data in this format, or a format easily converted.

Finally, the “Summary” page shows what you were originally asking for.

Again, this may be overkill if you just want a simple summary, but this will allow you to effortlessly add different categories of questions in the future, or different axes for evaluation etc. You can do a whole lot more with the data in this format including making charts of student progress over time sorted by category, or generating specific statistics, or anything else you can think of.