Show Only Newest Data When Values Match

[CarkMardilli]

Hello,

I have an app that tracks when students enter and exit the school.

They simply scan their student ID cards and it logs their scan as either "IN" or "OUT."

We are trying to create a view that will show either ONLY the scanned in users or scanned out users.

The students can scan in or out multiple times per day, as they leave for lunch or other activities. This creates multiple "IN" and "OUTS" linked to their student ID. This also makes it not possible to just filter by those with a status of IN or OUT as it will display their old in and out scans as well.

How do I create a view that only shows the students most recent, which would be their current, scanned in or out status?

I have attached a screenshot of my Scans table.

Please let me know if you need anymore information.

Thank you.

[Swoopy]

try

[_RowNumber] =
MAX(
   SELECT(?table name?[_RowNumber],
      [StudentID] = [_THISROW].[StudentID])
)

[CarkMardilli]

Hi Swoopy,

Thank you!

This half works, as it does list only the recent scans related to the StudentID.

Now I just need to filter it so one view shows only those with a [Status]="IN" or a [Status]="OUT" so I can have both views. I am going to give that a try. If you have any suggestions on how to do that please let me know.

Thank you for your help! I appreciate it!

[Swoopy]

For Latest IN slice:

[_RowNumber] =
MAX(
   SELECT(?table name?[_RowNumber],
      [StudentID] = [_THISROW].[StudentID],
      [Status] = "IN"
   )
)

For Latest OUT slice:

[_RowNumber] =
MAX(
   SELECT(?table name?[_RowNumber],
      [StudentID] = [_THISROW].[StudentID],
      [Status] = "OUT"
   )
)

[CarkMardilli]

Thanks for your quick reply.

Those two slice conditions still show BOTH latest INs and OUTs instead of one or the other.

[Swoopy · Accepted solution]

Sorry for my bad eyes,

[_RowNumber] =
MAX(
   SELECT(?table name?[_RowNumber],
      AND(
         [StudentID] = [_THISROW].[StudentID],
         [Status] = "IN"
      )
   )
)

[_RowNumber] =
MAX(
   SELECT(?table name?[_RowNumber],
      AND(
         [StudentID] = [_THISROW].[StudentID],
         [Status] = "OUT"
      )
   )
)

[CarkMardilli]

Thank you Swoopy!

This works perfectly! Your previous solution before you made this edit (I got to it before you changed it) also appeared to work!

You led me on the right track. I just didn't have my statements formatted properly. You did it tho! 

Thank you very much!

[CarkMardilli]

Swoopy,

After some testing it appears that you original final reply is the working answer.

This works as intended - 

AND([Status] = "IN", [_RowNumber] = MAX( SELECT(?table name?[_RowNumber], [StudentID] = [_THISROW].[StudentID], [Status] = "IN" ) ) )

With the current accepted solution it is not sorting by status correctly. When a status is changed from in to out or vice versa it is showing both as being the current status.

Do you want to repost it so I can mark it as the correct solution?

Regardless,

Thank you again!

[Swoopy]

Actually this part is wrong

   SELECT(?table name?[_RowNumber],
      [StudentID] = [_THISROW].[StudentID],
      [Status] = "IN"
   )

 it needs to be

   SELECT(?table name?[_RowNumber],
      AND(
         [StudentID] = [_THISROW].[StudentID],
         [Status] = "IN"
      )
   )