Help

Rolling up a numerical lookup with MAX

Topic Labels: Base design Formulas
473 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Darin_Bellisari
6 - Interface Innovator
6 - Interface Innovator

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!

2 Replies 2
Sho
11 - Venus
11 - Venus

Hi @Darin_Bellisari ,

I assume you want to keep the color of the select field.
Not easy to do this. With automation, although this would be possible.

How about considering a formula?
You may want to use emoji instead of color.

Hi Sho, the color isn't important, but at least bringing over the text is. If we roll up the number, we lose the text and I can't see a way to do a lookup on the number