How to select a value of a column

AppSheet AppSheet Q&A
, ,
1

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

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

3X_0_d_0d032113308b0befb7caa2e759c2342b74df7962.png
3X_0_d_0d032113308b0befb7caa2e759c2342b74df7962.png359×284 13.5 KB

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:

3X_c_b_cb6adbbc814736924a3852c74f6df1919985312e.png
3X_c_b_cb6adbbc814736924a3852c74f6df1919985312e.png355×358 7.88 KB

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

3X_c_7_c76562532f7b86f1279b2dc9c797487823abb22c.png
3X_c_7_c76562532f7b86f1279b2dc9c797487823abb22c.png356×419 11 KB

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 .

2

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
3

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.

4

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
5

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

6

[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
7

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

2 Likes
8

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

1 Like
9

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
10

Ah ok. Got it!

1 Like