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.
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.
Thank you @Kirk_Masden I wil going to try it, thank you!
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?
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.
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!!
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]))
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?
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.
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |