Help with a function condition

[Jesus_Corral]

Hi guys, I hope you are fine, I hope 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)

And I add an slice called IN of the TIMESHEET table and its function is to store all the people who have already registered with the time of entry to work

Timesheet form where employees scan a bar code to check in
1.-

2.-

Slice which shows the employees who have already made their check in

But

Here is the problem that I have

if I click one of these employees to see more details, I have a button that I added called clock out, is an action button which causes the employee to clock out and then shows me the hour of clock out

But I don´t want to do this because
the purpose of the app is that the employee just need to scan their card and automatically the app must record their entry time as well their out time.

So I thought if there is a way, a condition, a query, something that helps me to interpret the following:

if the employee being scanned in the TIMESHEET FORM has already records in the table IN which is a slice then give me the time out of this employee.

The problem is that I don’t know if you can do something like that, and in case of being possible, where I would put that condition and how I would translate it into appsheet language.

I hope you can help me. Thank you.

[Kirk_Masden]

First, I imagine that “TIME IN” and “TIME OUT” are events that may be repeated once every day. If a workday always starts and ends on the same calendar date, what you want to do should be easier. If people work nightshifts, you will probably need a way of identifying when days (shifts) start and end.

If people always clock in and out on the same day, I think that it should only be necessary to see if they have already scanned their card that day before or not. The first scan is IN and the second scan that day is OUT (assuming that don’t make a mistake and scan multiple times).

You should be able to use a SELECT() expression to see if the person has already scanned on that date. You could also use an IN() expression to check inside the SELECT() result to see if they have already scanned. If this is the first scan that day, they are punching in. If it’s the second scan they are punching out. Virtual columns could be used to determine whether or not each scan in a punch in or a punch out.

[Jesus_Corral]

Thank you @Kirk_Masden I wil going to try it, thank you!

[Jesus_Corral]

Hi kirk!
I hope you don’t mind.

Do you know how to make the expression who will interpret the follow:

select only the dates of the scanned employee

I tried this

ANY(

SELECT( 

    Timesheet[nameOfTheEmployee],        
    
    IN(  [_THISROW].[DATE], Timesheet[Date] )       

)

)

but it selects all the dates of the column DATE and I just want to Select the Dates of that certain employee, please could you help me?

[Kirk_Masden]

I can try. I’m not completely sure how your tables I structured but I’ll try to give you some ideas.

Let’s imagine that the scanner_card produces a DateTime. If there are no previous DateTimes that are smaller than a given DateTime but that have the same date, then that would be the first one for that date. First, let’s try to make an expression to do that.

SELECT(Timesheet[Date],[DateTime]< [_THISROW].[DateTime])

I haven’t tested this so I hope it’s right. This should produce a list of all of the Dates that had a DateTime that was earlier that the current row. Now, we need to see if this list has the date of the current row.

help.appsheet.com

IN()

Is item in list?

IN([Date],
      SELECT(Timesheet[Date],[DateTime]< [_THISROW].[DateTime])
     )

So, this checks to see if the date of this row is in a row with an earlier date time. If it is FALSE (no earlier time found) it is the punch in time. If it is TRUE (earlier time with the same date found) it is probably a punch out time.

Hope that helps!!

[Kirk_Masden]

Oops! You said that you only want the dates and times for the employee. In that case, I think your SELECT() expression should be something like:

SELECT(Timesheet[Date],and([Employee]= [_THISROW].[Employee],[DateTime]< [_THISROW].[DateTime]))

[Jesus_Corral]

Thank you so much @Kirk_Masden I will going to test it !

I just have an smaller doubt,

in the follow expression,

SELECT(Timesheet[Date],and([Employee]= [_THISROW].[Employee],[DateTime]< [_THISROW].[DateTime]))

What is datetime? Well I know what it means but I don’t have that column in my database, or is the Date column but with a different name?

[Kirk_Masden]

I’m not sure how the data from the SCANNER_CARD comes into your app. Is it just a date, or does it have a date and a time together? I must go to bed now. I hope I can look at this tomorrow.