Making a punch clock app for Staff Clock In/Clock ...

Vhyc18 · 10-20-2021 12:13 AM

I’m currently making an app that tracks staff attendance. The idea behind this is to issue each staff member with a QR Code and to have the Front Desk scan the QR Code with the app.

My app needs to fulfil the following requirements:

Allow Clock In/Clock Out with QR Scanning only
View who is present in the office
Allow multiple Clock In/Clock Out per day

While I am able to fulfil the first two requirements, I am having trouble with the third part. Currently my setup only lets me Clock In/Clock Out each staff member once per day.

My setup is as follows:

3 tables, namely Master_List, Clock_In, and Clock_Out
Master_List is ReadOnly and contains all Staff details
Clock_In contains the fields for [ID], [Date], [IDDate], [Time In], [Time Out]. [IDDate] is a concatenation of [ID] and [Date] to provide a unique field to generate a VLookUp for [Time Out]
Clock_Out contains the fields for [ID], [Date], [IDDate], and [Time Out]

For Views I have set up the following

“Clock In” Form where scanning a QR Code automatically logs the Staff’s ID, Date, and Time In into the Clock_In table
“Clock Out” Form where scanning a QR Code automatically logs the Staff’s ID, Date, and Time Out into the Clock_Out table

From that, the Vlookup will provide the corresponding [Time Out] value for the Clock_In table.

My question is as follows:

How do I make it so that the app can allow for Multiple Clock In/Clock Out per day without relying on the Vlookup table?

Thank you.

Ryan_Mortimer

This definitely seems like a really cool usage of AppSheet! I have built something similar, so I might be able to help out here.

From just quickly reading this, am I correct in assuming that this seems to be an issue with not allowing duplicate [KEY COLUMN]?

Also, is there a specific reason why you have clock in and clock out on separate tables as opposed to as a single row?

My thoughts are that if you condensed this to two tables (MasterList & Check In - Check Out) you could:

Pre-fill the information where the username/user is tied to a reference column that has an initial value of USEREMAIL() (which would need to be added to your Master table.

You could then use dereferences to auto-complete/auto-full the other information required from your Master table.

To view who is currently present, you could then utilise a slice that is filtered by ISBLANK([TIME OUT] in combination with an expression such as [Check In Date] = TODAY() - this would provide you a list of everybody who is present in the building, that has also checked in today.

The above could quite easily be achieved a number of different ways, including in your current data structure, but I feel this would be more efficient.

Also, can you clarify why you are using LOOKUP as opposed to references?

Vhyc18

Thank you for the response.

My reasoning for using two separate tables was so I could record the Time In and Time Out for each individual using a scanned QR code via the Forms View, as opposed to using Action Buttons with the other Views. I used VLOOKUP as I was more familiar with that method in my other work involving Excel, which may not be the best fit for Appsheet.

I’ve tried your advice and tried making a table with both “TIme In” and “Time Out” columns, using “ID” as a Key column. However, now my issue is that I am unable to edit the “Time Out” column using the QR Scan function of the Forms View.

Ryan_Mortimer

I am not in front of my computer right now, but I have a few follow up questions so I can better understand the structure and application.

Functionally, how does the QR code scanning work (from start to finish) for both check-in and check-out?

ie. Are users scanning the QR Code from the AppSheet App or an alternative QR scanning app, are users required to sign in to AppSheet, etc.

If the Key/ID column change seemed to solve the multiple entries per day issue - are you still experiencing an issue with QR code to sign out?

Also, it’s always good to understand that everybody uses tools like AppSheet differently and that sometimes / a lot of the times, there is not necessarily a right or wrong way to do things.

Especially if you are pushing the boundaries of immediately accessible functions in AppSheet (there are plenty of examples for this).

I tend to look at it more like “is there a better way to do this”.

Vhyc18

Sorry for the late response.

1. Functionally, how does the QR code scanning work (from start to finish) for both check-in and check-out?

My intention is to use Appsheet to handle the QR Scanning part.

2. If the Key/ID column change seemed to solve the multiple entries per day issue - are you still experiencing an issue with QR code to sign out?

I’m facing a different issue with that altogether actually. Now (using Google’s QR Code example), I get the error of “There is already a row with the key ‘1111223333’”.

I think since assigning the ID as a Key limits multiple entries duplicate entries, it can’t fill in the “Time Out” column for that particular ID.

Ryan_Mortimer

Have you managed to get the scanning of the QR code to trigger the automatic “Clock In” as of yet? This would be really cool to see.

Secondly, if you condense your IN and OUT to one table, then you need to EDIT a row, rather and Create a new row. You could always set your ID/KEY to something that combines the ID and current time or something.

It is actually quite hard to imagine how everything is setup without seeing it in action

Vhyc18

Have you managed to get the scanning of the QR code to trigger the automatic “Clock In” as of yet?

I’ve managed to do that. But since I currently use the Staff ID as a Key, I can only enter that person’s entry once.

You could always set your ID/KEY to something that combines the ID and current time or something.

The reason why I choose to not do this is because I want to make sure that each Clock In corresponds to another Clock Out. If I set ID/Time as a Key, I could end up with a situation where someone Clocks In multiple times but only Clocks Out once in the same day.

Ryan_Mortimer

Unfortunately, the only way that I can think of doing this easily is by removing the auto feature and using the action button instead.

Assuming that this is the final data structure and the o try we variables are non-negotiable (which is totally fine by the way!) I think you may want to look down the path of.

Generating a “number” that is assigned to each sign in and sign out
Have an ID that is a Concatenation of that number and the staff ID
Have that “number” increase sequentially (generally not recommended) based on
IF the last row in this table that matches has a sign out, then add 1, if not, then don’t add the 1 so it will sign out instead

That last part is based on if the QR code in and out is the same.

If there are seperate QR codes for in and out, then really it would just be looking for if there is another row and then + 1 on the sequential.

Another thought I am having, are users scanning a QR code on a central device (such as one printed on an ID Card, but are scanning this on a “terminal”

OR

Have you printed out QR codes that staff are scanning on their own devices?

I will keep thinking about it though and see if I can come up with something.

Vhyc18

Have you printed out QR codes that staff are scanning on their own devices?

I intend to use this feature instead of having users scan a QR Code. Of course, I haven’t done anything yet, this is still a testbed for the future, so I may explore having users scan a QR Code if this method of having the front desk scan the employee’s QR Code doesn’t work so well.

Ryan_Mortimer

Let us know how you go, I will keep thinking in the mean time.

I think that if it is an assisted service (as in, someone is executing the scans rather than self serve) you have a lot of options that will help you around the issues that you are experiencing.

I may try and rebuild a similar solution just to work this out for myself!

Fredyrg

Would you mind sharing a templet of your app?

Making a punch clock app for Staff Clock In/Clock Out