Hi all! Here's what we're trying to do, and what we tried that isn't working:
What we're trying to do: (example table: https://airtable.com/appKiBFGhHOgFeXf2/shrrTAISY39Rx8mL5)
- We run an exam, and have two tables:
- test_takers - each person who is taking an exam
- exam_instances - each instance of an exam, 1:1 linked to test_takers.
- In other words, a single test taker could take an exam multiple times, generating multiple exam_instances
- We would like to store the status of the exam (e.g., not started, started, passed, failed) in two places:
- In exam_instances - each instance will have a single exam_status
- In test_takers - each test taker will roll up the "maximum" of those statuses. For example: if someone starts the exam twice and passes the second time, so one entry in exam_instances has the status 'started' and the other has the status 'passed', the entry in test_takers linked to each of those exam_instances should be only 'passed'.
- In other words, it doesn't matter how many times someone takes an exam, if they pass then it should display passed in the test_takers table (there are more stages/statuses, I'm simplifying for the example)
- How we tried doing this:
- 1. We changed status to a numerical value
- 2. We created a third table "status_lookup" with two fields: status_number (primary key) and status_text (select), assigning each status to a number (e.g., 'passed' is 3)
- 3. In exam_instances, we created a LOOKUP field on status_lookup, to assign the appropriate number
- 4. In test_takers, we would then ROLLUP on exam_instances, to roll up the status_lookup number as MAX([VALUES])
- Unfortunately, step 4 does not work: because the number is a primary key, you can't take the 'max' of it
- We could convert the primary key from the lookup to a number, but then we can't look up the status_text in the status_lookup field. In other words, what we really need is a VLOOKUP-type function that let's us look up status_text based on a number field
Here is a link to an example table to show what I mean: https://airtable.com/appKiBFGhHOgFeXf2/shrrTAISY39Rx8mL5
What is the best way to achieve this? Thanks for any advice!
