Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Rolling up a numerical lookup with MAX

1167 1
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.

Darin_Bellisari
6 - Interface Innovator
6 - Interface Innovator

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