test scoring formula

[hhspiel]

Hoping someone can guide me with setting up a formula to score tests that are reported on another table.

Here's the current setup:

Table 1 - record of all test takers by session ID, including fields for all of their question responses.

Table 2 - question key  - records of all questions used in exams including a field for the correct answers.

Table 3 - scoring - how can I set this up to pull response data from table 1 and cross-reference against the correct answers on table 2 and then display correct or incorrect, 1 or 0 etc for each right or wrong answer?

Table 3 would then also have separate fields that would tabulate the raw scores for each section and covert them accordingly.  

It's a relatively easy formula on a spreadsheet, so I thought it would be easy here as well, but it looks like Airtable doesn't support combining formulas with lookups?  

There are over 100 responses in each test session, so I'm hoping I don't have to replicate each of those columns as lookups to be able to score them??

I can chance anything about Table 2 or 3, but can't change how Table 1 is setup.  

Appreciate any help or guidance! 

[Zollie]

---

it looks like Airtable doesn't support combining formulas with lookups

---

Airtable does support using lookups in Formulas, however in your case, your lookup won't be a single value, but multiple. Airtable Formulas currently work best when they act upon individual values, rather than a combination of them. So yes one of the solution avenues is to split question and answers out into individual field values so that you can calculate them cleanly with formulas.

But given your current schema, scripting is another path forward. Scripting tools make use of JavaScript, which has plenty of tools available to convert between data types (such as making a comma separated string into an array), retrieve values in multiple tables, and/or run the necessary calculations. Learning JavaScript is worthwhile and multi-purpose, but if your time is limited, you might considering hiring a developer to help.