HELP - Pre-Post Assessment: Comparing Individual Student Progress and Competency Evolution

Hm, you mentioned you already have all their student data in the table.  I’m assuming you have some sort of unique identifier (student # or email or something?) as well as the date of the assessment like so?

If so, you could try grouping on the unique field (I grouped on email), which would give you something like this:

This works as a visual thing, but if you need to track metrics e.g. how many students improved on Competency 1 or something, then you’d need to have a Students table like so:

And this would let you create formula fields to compare their progress:

To do so, we would create a new table called ‘Students’ and create a linked record field to it, and then:

1. Click the header of the field that contains the unique identifier
2. Hit CMD/CTRL + C
3. Click the header of the linked field
4. Hit CMD/CTRL + V

This’ll create one record for each student:

And after that, we would create lookup fields to display the pre and post values for each Competency by sorting by the Date and either displaying the earliest or latest one:

And then the formula field would compare the values for us:

IF(
  {Competency 1 (from Submissions) - Post assessment} = {Competency 1 (from Submissions) - Pre assessment},
  0,
  IF(
    {Competency 1 (from Submissions) - Post assessment} = "Always",
    1,
    IF(
      AND({Competency 1 (from Submissions) - Post assessment} = "Sometimes", OR({Competency 1 (from Submissions) - Pre assessment} = "Rarely", {Competency 1 (from Submissions) - Pre assessment} = "Never")),
      1,
      IF(
        AND({Competency 1 (from Submissions) - Post assessment} = "Rarely", {Competency 1 (from Submissions) - Pre assessment} = "Never"),
        1,
        0
      )
    )
  )
)

I’ve set it up in this base for you to check out and you can duplicate it into your own workspace so that you can play with it!