How to select a value of a column

Hi guys, I hope you are fine, I’m hoping someone can help me with this please :slightly_frowning_face:!

What the application is about?
Automated application for recording employee check-ins and check-outs by barcode scanning

Explanation
I have 2 tables

Employees
Number (id)
Name (text)

Timesheet
SCANNER_CARD (References to the Number of Employees table)
Time in (time)
Time out (time)
Date (date)

The process is this :

1.- I have an EMPLOYEE VIEW in which I can add, delete, edit an employee.
2.- I have a TIMESHEET VIEW which is where employees will be scanned for check-in and check-out

Menu

The situation is this:

I couldn’t find a way for the application to give me an entry or exit time depending on the employee being scanned

View of the timesheet form:
timesheet form

  • Here for example an employee has been scanned and automatically gives me the check in (TIME IN field)

timesheet form name

But

How can I define a function or query that allows me to identify whether the employee being scanned displays the time of check-in or check-out? ( to proceed with the calculation of hours worked)

I don’t know how to translate the query into appsheet language

please if anyone can help me I’d be very grateful, I’ve had this problem for over two weeks, please :exploding_head:.

When the employees are checking out, does your app create a new referenced record for the employee or are they editing their initial check-in record? How does your app work on that aspect?

1 Like

they are editing their initial check-in record LeventK, because I want to have the check-in and the check-out in the same row.

OK. You can use below initial values in [Time In] & [Time Out] fields and also turn on Reset on Edit without any expression:


[Time In] Initial Value


IFS(
    NOT(IN([KeyColumn], TableName[KeyColumn])),
    TIMENOW()
)

[Time Out] Initial Value


IFS(
    IN([KeyColumn], TableName[KeyColumn]),
    TIMENOW()
)
3 Likes

Thank you so much for your help LeventK, but I’m a little confused
What do you mean by [KeyColumn]?

[KeyColumn] and TableName are meant to be generic placeholders. You would update the expressions with the names you use in your specific app scenario.

  • TableName = the table in your app - I believe you said was named “Timesheet”.

  • KeyColumn = the name of the column set as the key in the “Timesheet” table.

2 Likes

This why I use _ROWNUMBER rather than KeyColumn: no interpretation needed, no confusion, use verbatim.

2 Likes

Wait! What? You use _ROWNUMBER as the key column? Is that in all your tables?

1 Like

No, no. I wasn’t clear. @LeventK suggested this:

IFS(
    IN([KeyColumn], TableName[KeyColumn]),
    TIMENOW()
)

but then had to explain KeyColumn. Had he instead suggested:

IFS(
    IN([_ROWNUMBER], TableName[_ROWNUMBER]),
    TIMENOW()
)

no further clarification is needed.

2 Likes

Ah ok. Got it!

1 Like