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: …
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:
for the first column, you can do it like this:
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”).
Option 1: Hard Coding
You can write a formula for each of grade levels, skill levels, etc something like the following:
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:
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:
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.