Timesheet with QR Code Scanning

[Sean_Cunnington]

I’m really struggling to get my head around this one.
I’m trying to build a touchless clock-in and out system for employees in our building.
The plan is to use a card with a QR Code generated from the UNIQUEID in our Employees table (Employee ID).

My other table holds the clock in and out data, with references to the Employee ID, set to scannable. This is all very simple. I can scan the card and get a Clock In written to the database. However, when I scan to clock out, I’m creating a new row.
What I’ve tried so far:
I tried a LINKTOROW and LINKTOFORM expression, with the scanner in a separate table, which I couldn’t get to correctly update only the “Clock Out” column.
Tried having separate tables for “In” and “Out” forms, then collecting those in a third table. Could not get the data to write to the third table at all.

I’ve looked through a couple of threads here, and [tsuji_koichi] had come closest to what I’m hoping to do, but can’t see how that data is recorded. And a lot of the sample apps seem to not be working/offline at the moment.

Is anybody able to offer any insight on this? I’m sure I was heading down the right path, but probably made some rookie mistakes!

EDIT: Probably more helpful if I add the whole formula!
So action created, " Go to another view within this app" with
IF(
IN([Employee ID], JS_ClockIn[Employee ID]),
LINKTOROW([Employee ID], “JS_ClockIn”),
LINKTOFORM(“JS_ClockIn_Form”, “Employee ID”,[Employee ID])
)
Then another batch action of this + delete to remove the QR scan. With no data in the table, this works perfectly, to create the form as needed. However it’s not deleting the scan from the table.

[Grant_Stead · Accepted solution]

My preference is to treat each and every scan as a new record… instead of a clock in, and a clock out on one record… This will let you basically “bump/scan” to your hearts content. Then in the back end you create the rules around what is the first in, last out, etc. That’s how most badging systems work.

[Sean_Cunnington]

Hey Grant,
Thanks for your response. I’m very guilty of getting carried away with AppSheet and over-complicating everything, then pigeon-holing myself along that train of thought! Thank you for clarifying how it should work. This gives me some clear direction now.

[Grant_Stead]

That method has it’s own heartaches… you’re just moving them to the backend…
But all you collect is a datetime and personID
Then at some point you need to mathematically decide what is the “first clock” and what is the last clock
You also will probably need assumptions and flags for missing clocks…
If you have day and night shift it get’s even crazier…

[Sean_Cunnington]

EDIT: I’ve done it! I created a third table with the Employee ID and an In Out Value of either 1(Out) or 2(In). The rows for this table are created with actions whenever a new employee record is added (for bonus points) I created an action on this table with an IF and SUBSTITUTE formula to switch between the values then tied this action on form save of the Clock In form.

If anyone needs or would like any additional info on how this works, I’ll explain in more detail.
If it crashes and burns in a weeks time, I’ll remove this post!

[Jayps_22]

Hi Sean. My Name is JP and I am new to appsheet. Is it possible to see your solution. Been struggling with this for 2 days already. Thank you

[CarkMardilli]

This may solve my problem. Could you please explain this to me.

Thank you.

[CarkMardilli]

Hello,

Could you elaborate on this some more? It may help for an app I am creating that uses in and outs. I am curious to what the backend rules would be for the first in last out.

Thank you.