Linking Quiz Result Table with Submission List

[Y_Jeffry_Thurana]

So I have several tables with test/quiz results from students (names & grades), one table for one test (quiz 1, quiz 2, etc). I also have a table listing student names and their test/quiz submission record (checkboxes and list of tests/quizzes columns).

Question: how can I link test/quiz result tables with submission records so that students that already have grades for certain tests will automatically checked on the submission record?

For example: Student A has grades for quiz 1, 2, 4, and 6. Student B has grades for quiz 2,3, and 5. etc.

I want to be able to quickly see who has done which test/quiz so I can follow up with the ones that haven’t done the test/quiz.

I hope my question is clear enough. Thanks.

[Johg_Ananda]

hey @Y_Jeffry_Thurana welcome to the community. You can definitely accomplish this. I think the best thing to do would be to create a sample document and then post it in here so the community can take a look and advise you, it helps make the theoretical more concrete.

I also would recommend that instead of having a separate table per quiz, you have one table for quiz results, and then a column [Quiz Number] for each respective quiz. As a general rule, you want to have as few tables as possible, and its much easier to filter one table down to get the results than try to query multiple tables.

For example with one table, you could get all the results for one student:

_quizresults.filter(student="Jeffry").grade.average() and that would give Jeffry’s average grades across all quizes, or you could grab a list of all student results for a specific quiz:

_quizresults.filter(quiz=1)

[Y_Jeffry_Thurana]

Hi @Johg_Ananda, thanks for the insight. I know the less table the better. But there’s reason why I have so many tables. Each table is imported from Google Sheet, which is the result from Google Form. In every quiz, there are several students who don’t submit.

I thought of copy and paste those several table data to one table with multiple columns of quizzes (as you suggested), but with slight differences of names in each table and hundreds of student names, it becomes a very tedious job. It’s much quicker to import each quiz result to different table.

Or is there a simple way to import this data?

My end goal is to have one place to see which students don’t have grades for which quiz so I can follow up.

I’ll create a mock up document to give clearer picture and get back here.

[Y_Jeffry_Thurana]

Here’s the link of the mockup document:

Any insight would be helpful. Thanks.

[Johg_Ananda]

Re Quiz Results - yes for each table add a third column [Students Name], [Score], and then [Quiz Number]. Set all the rows in this table to be 1, or 2, or whatever, then paste into your Master Quiz Table, which has the corresponding three columns. Then you can easily filter to each quiz.

Name normalization is another issue, but you’ll have that regardless, and its going to be easier to deal with in one table rather than many.

For students attendance, if you have one master table with all of the results, and another table with the names, you could have columns for each quiz as you have, with a formula something like:

if(quizresults.filter(name=thisrow.name and quiz=1).count>0,true,false)

That would report true if there was a result for that student on quiz 1 and would easily scale to all students; you can adjust for each quiz by changing the quiz= part of the formula.

[Y_Jeffry_Thurana]

Thanks for the answer @Johg_Ananda. I learned a lot!

So, I add one column [Quiz Number] create one master table for the quiz and copy paste all there, create a view of that table in the Attendance section. Then I tried your formula.

The result is all “false”. Then I tried to create variations of the formula and always get “false” no matter what. The only time I get “true” is when I change the condition to if(A,true,true). LOL.

Here’s the variations that I tried:
for Quiz 1 row
if(quizresults.filter(thisRow.Name=thisRow.Name and quiz=1).Score>0,True,False)

for Quiz 2 row
if(quizresults.Name=Attendance.Name and quizresults.quiz=2,True(),False())

for Quiz 3 row
if(quizresults.Name=Attendance.Name and quizresults.quiz=3 and quizresults.Score>0,True(),False())

I’m starting to grasp the concept, but the formula realm is beyond my understanding. Please help. (Otherwise, I think I’ll just give up and do it manually… LOL)

Thanks.

[Johg_Ananda]

You are welcome. Here is an example I created to show you what I mean. I set the [Student] column in Quiz Results to be a lookup of the Student Attendance table so they are linked. Let me know if this makes sense to you:

I set up the [Quiz 1], [Quiz 2], [Quiz 3] columns on the left the way I explained in my earlier post. However, I realized that the “better” (more scalable) way to do this is to create an array for each student that pulls in all the quiz values. Then you can query the array in various ways. I gave some examples, you can show their average score, the number of quizes attended, and can even check if they did the quiz by modifying the formula a bit to reference the array.

This allows you to sort by average, quizes attended, etc.

[Y_Jeffry_Thurana]

Thanks @Johg_Ananda for all the help. I’m doing it and it works like a charm.

But just like you predicted, I have issues about names. Some are using initials for part of their names, some are using nicknames, so they are different from the official names on the Attendance table.

The more reliable way is to use the combination of Student’s Class and their class numbers.

Can we use two different components (class and number) as the lookup to replace the “Name”?

[Johg_Ananda]

I think so, but I’m not sure what you mean. I need an example.

The way I have solved this for my workers is assign everyone a PIN code have them use that … names are very messy.

[Y_Jeffry_Thurana]

Thanks for putting up with me. PIN or other unique identifier is a great idea. But I can’t assign PIN code as the classes have already started and students have submitted several assignments. Maybe for later.

Anyway, I have updated the mockup to give you general picture. Every student belong to a class and have their own class number. So there are the combination of two unique identifiers. The easy way is to separate the quizzes to several different tables based on classes and use the class number as identifier. But in the spirit of unifying everything under one table, maybe you have another insight?

Here’s the mockup table:

Sample Quizzes and Attendance Linking

[Johg_Ananda]

Hey I can’t see the formulas in that published version FYI.

I’m not sure what you “have done … manually”, or what you’re trying to extrapolate.

“Every student … have their own class number” ? Are you saying there is a unique class number for every student so if n students = n class numbers? That doesn’t make sense to me.

I’m not clear :-/

[Y_Jeffry_Thurana]

Sorry to keep on troubling you @Johg_Ananda. I mean, let’s just say I teach 23 classes, each class has 30 students. I want to use the combination of the class and student ID number in that class as the identifier.

For example, there’s John from Math 3 class. His ID number in that class is 14. There’s also John from Social 5 class, and his ID number in that class is 10. So to be able to tell which John is which, one has the combination of “Math 3” and 14, the other is “Social 5” and 10.

[Johg_Ananda]

Yes ok this is a common issue. For example if you have bank accounts, you have the bank name, and the account number, but neigher work well for the Display Column so it is useful to create a new column which combines both, for example ‘Bank of America 1234’ or ‘Chase 5678’.

In your case: concat(studentID, " - ", class)

Might work - and then set this to be the Display Column. This should create unique identifiers, and you can modify per your needs.