Help with maxrow() function

[Jesus_Corral]

APP FUNCIONALITY
The main function of my app is to scann a certain employee with a scanner field and the app has to give me the ENTRANCE HOUR as well as the EXIT HOUR in order to calculate the hours worked.

The thing is that the user has to scan a card that will be assigned in a field called SCANNER_CARD, and the app automatically has to show me the hour depending of the entry type , (ENTRANCE HOUR or EXIT HOUR)
I know that I can do the time funcionality with timenow() and other funcionts
but I don’t know how to automate this process.

What data do I have?

TABLES

Employees:

Table that has the following columns:
NUMBER (ID)
NAME (TEXT)

Timesheet :

Table that has the following columns
ID_TIMESHEET
SCANNER_CARD (References the NUMBER of the employee that is on employees
table)
DATE-DAY (DATETIME)
ENTRANCE HOUR (TIME)
OUTHOUR (TIME)

What have I tried?

I have tried to use the MAXROW() function to identify the last value of the DATE-DAY column so that I can compare it to the penultimate value of the column DATE-DAY as well

!!!ATTENTION!!!
I have tried to use the MAXROW() function to identify the last value of the DATE-DAY
but from the employee who was scanned not all data in general.

I don’t know how to implement this pseudocode in appsheet expression I’ve been researching the documentation for over two weeks and I can’t figure it out, please help me:

(assuming I already scanned the employee)

bring me the maxrow of the column DATE-DAY of the employee scanned
and

bring me the penultimate column of DATE-DAY of the same employee scanned

Please, I hope I’ve made myself clear. Thankyou

[Jesus_Corral]

mucho texto

[Steve]

Assuming you’re trying to do this from the employee’s row in the Employees table, the expression to get the employee’s latest Timesheet row is:

MAXROW(
  "Timesheet",
  "DATE-DAY",
  ([_THISROW].[NUMBER] = [SCANNER_CARD])
)

To get the DATE-DAY column value from the employee’s latest Timesheet row:

ANY(
  SELECT(
    Timesheet[DATE-DAY],
    (
      [_THISROW]
      = MAXROW(
        "Timesheet",
        "DATE-DAY",
        ([_THISROW].[NUMBER] = [SCANNER_CARD])
      )
    )
  )
)

I would encourage you to change the clock-in and -out columns to DateTime rather than Time to more easily handle spans that cross days.

[Jesus_Corral]

Thank you Steve, you are the best

[LeventK]

@Jesus_Corral
ahaha…you’re damn right, @Steve is definitely an expression monster