Processing data in multiple phases

[Yann_Salmon]

I am trying to convert from Google Forms / Google Spreadsheets an app that we use to collect students’ wishes pertaining to the optional course they want for the next semester (either SI or INFO)

As it stands, the form allows each student to specify their name and email, and the course they wish along with the certainty of their choice. They have 4 choice, combining SI/INFO and sure/unsure, and a 5th “I do not know” choice. They may change their mind during, by submitting the form again.

So I have a data table in Grist, named Reponses, with each of their inputs and the corresponding timestamps. For a given student (identified with their e-mail address), only the most recent entry is relevant, so I created a table view, named Dernieres, linked to the data table with a grouping by Email. This view has the Email as first column, the most recent timestamp for this Email as second (column Maj with formula MAX($group.Timestamp)), and the relevant data as other columns (eg. $group.Choix[$group.Maj.index($Maj)]).

Now I want to compute statistics from this intermediate data : how many students want each course, and how many are sure.

My problem is : I cannot seem to refer to Dernieres in formulas (eg. to build a graph, or another table view). Grist seems to consider data tables differently from computed tables, with is confusing to me because this is neither the case in a spreadsheet or in SQL (a SQL view can be used in the FROM clause of another view).

Is there something I am missing ? Or maybe there is an entirely different and better way to achieve what I want in grist ? I do not want to freeze the intermediate data, because I want the statistics to update as students change their minds.

[natalie-grist]

Hi there!

It is possible to pull data from summary tables into formulas. It is not possible to make a summary table of a summary table. I do think I found a good solution for you though. I tried to set up this example document as close to the structure that you described. See the example here: Community #7673 - Grist

The most important thing with this structure is that there is a separate Courses table. This is specifically useful for the last step, where we count students who are interested in enrolling in a course.

In the Responses table, the course column should be a reference column, pointing to the Courses table.

This may require you to update your formula in the Courses column of the Responses [ by email ] summary table that shows the most recent response from each email address. The formula I used was;

Responses.lookupOne(email=$email, timestamp=$Most_Recent).course

We use a lookupOne function to find one record in the Responses table where the email matches the email in this record and the timestamp matches the value in the Most Recent column for this record. Note that this formula returns the record listed in the course column of the Responses table. We change this column in the summary table to a Reference column so we can return information about that record - specifically, we want the Name of the course.

Finally - we can count the records in this table to find how many students are interested in each course. We will write this formula in the Count column of the Courses table.

The formula here is;

len(Responses_summary_email.lookupRecords(Course=$id))

Responses_summary_email is the table ID for our summary table, Responses [by email]. Grist formulas will make suggestions as you type so if you want to pull data from a summary table, start typing the name of the table, then you’ll see suggestions listed. Summary tables are named first by the underlying data table (Responses), then specify summary and then list the group by columns. Our summary table is grouped by email.

We want to use lookupRecords because we want to find all records where criteria matches. lookupOne() returns a single record. lookupRecords() returns a list of records.

We then provide our match criteria, Course=$id. We want to know all of the most recent responses where this Course was selected.

Finally, we need to count the number of records returned. To do this, we use len().

With this structure, as new responses come in, the most recent for each student will update and then the counts for courses will update as well.

Please let me know if you have any follow up questions.

Thanks,
Natalie