I generate a lot of CSV files. These hold student assignment grades and each student is identified by a unique username. Therefore I might have five separate CSV files, one per assignment, with username as the first column. For organizational purposes it makes sense for me to have multiple documents to start with, and I’d like to import them all into Grist then… Link them or something like in a relational database. I just can’t figure out how to do this. I can make reference columns but that doesn’t seem like what I want. I want more like separate tables linked with a key like in a relational database. I must be misunderstanding something. Any help would be appreciated.
Hey @Michael_Stachowsky !
Reference columns sound like what you want. They create a link between data in your document. In your use case, we want to relate all of these grades back to a single student so you’ll need a student table that contains some info like their username. Then, relate each grade back to a specific student.
I initially set up an example where each assignment is in a separate table, to match your current process but I quickly realized this is not ideal in Grist. Keeping all assignments in a single table will work much better - but don’t worry, importing the data will still be simple!
Here’s how it would look with all assignments in separate tables: #7684 - Grist
You’ll see on the first page that you’d need to include multiple widgets in order to see a student’s grades for multiple assignments. Sure, it works but it just feels clunky.
I’d recommend keeping all assignment data in a single table, with a column specifying the assignment. I set this up in an example here: https://public.getgrist.com/n8Mr817iv2Fm/Community-7684-single-table/m/fork
You’ll see that all grades are in a single table. There’s an assignment column to specify the name of the assignment for that set of grades. It’s easy to add this information when importing data - I’ll include an explanation at the end.
Because all of the assignments are in the same table, you can create useful views and summarize data more easily. For example, you can link a view of the Grades table to the students table so when you select a student, you can see all of their grades at once.
If you wanted to see the average grade for each assignment, you can create a summary table of the Grades table, grouped by Assignment. By default, all numeric columns are summed.
Update the formula in the Grade column from
SUM($group.Grade) to AVERAGE($group.Grade)
Now, you can see the average grade for each assignment!
When importing data, you don’t need to include the Assignment data column in the data you are importing. We can add a default value to this column during the import process. When importing, you would select the Grades table as your destination table. You’ll notice that the Assignment column is currently empty.
Click the dropdown to map the Assignment column. Select ‘Apply Formula’.
Enter the default value as a string within double quotes,
"New Assignment". After entering, you’ll see the Assignment column populated with the default value for the records in the import file.
After importing, the new assignment grades will be in the All Grades table, linked to the correct student.
I hope this helps get you started! Please let me know if you have any follow up questions. Beyond this, you can certainly get more complicated and start adding weighted grades for assignments and such. Happy to help as you continue building out your document!
Here are some help center articles that may be useful for the topics covered above-
Reference Columns: Reference columns - Grist Help Center
Linking Widgets: Linking widgets - Grist Help Center
Summary Tables: Summary tables - Grist Help Center
Thanks,
Natalie