QR code Time Clocking

[jpj142]

I've searched the boards here and have found some info that has been helpful, but I have not found the solid answer to my problem. 

I've built a time clocking app for my company for large projects. The one issue I am facing is the compiling of time.  Currently I have it set up like a badge system. Each employee has a unique QR attached to their name. I have 4 forms attached to slices for clock in, lunch out, lunch in, and clock out. 

The issue I am running into is that ideally I would like to reference the entry id(UNIQUEID) and be able to just edit and existing row.  For example, if Emmett Jackson is already clocked in and he scans his QR on the clock out form, I would like that scan and save to update the "clock out" column in the same Entry ID row where he is currently clocked in. See the screen shot of the google sheet below. 

Is it possible to do what I'm wanting to do?

[SkrOYC]

It is possible, you would need to use some expression to get the last row in which the user has clocked in and also hasn't clocked out, right?

But I also think that as many things there are different ways to do this so take any advice as a mere opinion.

For example, you could have a new column called something like "TypeOfTime" and replace all of the rest with "Time", so that they select if they are clocking in or out and then Time holds the actual value.

Now, based in your actual schema, the usage of MAXROW() and maybe SELECT()/FILTER() is the way to go IMO

MAXROW() - AppSheet Help
FILTER() - AppSheet Help
SELECT() - AppSheet Help

[jpj142]

@SkrOYC  I really appreciate the response. I can't quite wrap my head around how to use those expression in conjunction with each other. While you are very close to what I am asking I should have added  another detail. 

When I use my "Clock Out for Lunch" Form and scan the QR code (which references a specific employee. It brings up their name and show the pictures on file) , I would like to be able to update their existing record row with the "Lunch Start".   

The end goal is to have a supervisor pull up the "Clock In" form and scan everyone's QR code that is clocking in and it logs their time in the "Clock In" column. Then when it is time for lunch, the supervisor will pull up the "Clock out for Lunch" form and scan each employee's QR code as they sign out for lunch.  I would like this second QR scan to be able to reference each employees entry that has a status of "Clocked In" and update the "Lunch Start" field for each corresponding employee.  

This process would then be repeated again when clocking back in from their lunch break and then once again when they clock out for the day. 

I am trying to keep the data in line so that each Entry ID contains the data for those 4 time clocking events.   

Hopefully, this makes sense. I have it currently clock ins, lunch starts, lunch stops, and clock out correctly for each employee, but each QR scan creates a new row. Again, I am trying to have only one row for each day for each employee.  

Any help is appreciated. 

[SkrOYC]

In your case I'd create another table where you just have one form.

This table receives the input from the supervisor but as a "request" for update or add. Then a bot or action makes sure to:

1. Add a new row to your table if the type of data is "Clock in"
2. Edit the row with new data if the type of data is another one, Lunch, clock out, etc

It's kind of hard to explain it completely since I'm short on time right now but I hope you get it

[Austin_QREW]

Hi @jpj142,

We have a Timesheet App that uses similar features take a look at it here and see if it uses the functionally that you are looking for. If not let me know and I can work on a solution that is better tailored for your use case. 

We also have a free webinar you can watch to get a walk through of this app. 

Hope this helps. Let me know if you have any questions or your looking for a bit of a different feature set for your app. 

Thanks,

Austin from AppSheet Training 

[jpj142]

@Austin_QREW thank you for the response. I have looked at that app and previously watched the webinar. While it's awesome it is not quite what I needed. We work on large projects managing crews of worker that may change from project to project. 

We will have 1 or 2 supervisors scanning in the 50+ workers. We need the QR code scans to be done by the supervisor so that we can check the workers in and out. We do not want the workers to clock themselves in and out. 

Specifically, I am trying to see if there is a way that I can scan work A into "clocked in" status and record their time. Then when they clock out for lunch, scan the QR code again to move them to a "Lunch Out" status (or something similar) and record their time out for lunch on the same row of data as the clock in information. I would need to repeat that for "lunch in" and "clock out" at the end of the day. 

Hopefully that makes sense. 

[SkrOYC]

Follow up questions:

1. Will they always have lunch or maybe someone would have just clock in and out in a day?
2. You need to know in which "state" is each worker inside the app or it's not needed?
3. The QR Code is just the key value for that specific worker?
4. I saw you have Team Member inside your schema, it's there as a dereference expression to the workers table? Which one is the foreign key? ID?

[jpj142]

@SkrOYC thanks for your response. Here are the answers to your follow up questions:

1)  99% of the time yes, they will take a lunch. However, it is not 100% of the time. That is why I wanted the ability to scan a form connected to "Clock Out". This way a supervisor can skip the lunch scanning. 

2)  It is not necessary to know within the app the current status of the worker. 

3 ) The QR code is a reference to the specific employee. 

4) Team Member is a reference to another table that pulls the associated QR code information....if that makes sense. ID is not an established key. 

[SkrOYC]

Thanks.

4.1) What data is inside the QR Code exactly?

[jpj142]

Each QR code contains the following:

Name, ID, Job Position, Photo

[SkrOYC]

Ok, how you parse that info inside AppSheet? It's separated with commas or similar?

[jpj142]

The Team member Column in the table is a reference column to the row in the Personnel Roster, which contains all the employee information. I included the screen shots of the setup.  

I apologize if I'm not understanding what you're asking. 

[SkrOYC]

Thanks.

As I see, you are using the worker name as the data inside the QR Code.
That's actually not advisable at all since you can have two people with the same name an so on.

I suggest you to change the ID/key to a proper UNIQUEID() or even UNIQUEID("UUID") expression on initial value. Then change the QR code generation to this value, that will be almost unique for each worker. You can do that by using another initial value expression on an image column with this:

CONCATENATE(
 "https://quickchart.io/qr?text=",
 [_THISROW]
)

You can even add an image inside the QR code to give it a more professional look.

Then on your time clocking form you make the Ref field Scannable.

All of this was not directly related to your question but it's one of the basics of your database so I just wanted to make sure you got this right.

I would create a new table as suggested before with this schema:

Column Name	Column Type	Comments
KeyClocking	Text	UNIQUEID() as Initial Value
ClockingType	Enum	With values Clock In, Launch and so on.. the ones from your columns
WorkerKey	Ref	Scannable and ref to the workers table
Time	Time	TIMENOW() as Initial Value

Then I would create some actions with a grouped one that is selected to run on each form save.
The inner actions would check if a row is already on your table for this specific worker today, if it's not, it would create one with a Check In. Another one would check if the row exists and there is a Clock In, to edit that row with the corresponding value on the corresponding column depending on the ClockingType

[jpj142]

@SkrOYC  That's really good info....I think I'm almost there. I just am struggling to understand how I should structure the grouped actions and inner actions within those groups. I can't quite get the structure of the actions that you tried to describe. Could you please maybe break it down a bit more for me?

[Teja1112]

Hey Austin, I am new to appsheet, and I am planning to create a similar app but instead of timesheet and timecards based on USEREMAIL(), I need to be calculated with Employee ID with a 6-digit number. I have tried it, but it is not working.

SUM(
SELECT(TimeCard[Calculated Time],
AND(
[Employee ID]=USEREMAIL(),
[WeekNum]=WEEKNUM(NOW()),
[Day]=1
)
)
)

[Steve]

Please start a new topic for help with this.

[DaveWillett]

Sorry for butting in, it would be great to look under the hood on this once working, would that be possible to give access to a copy demo?

Many Thanks

[jpj142]

Certainly!  Once I get this thing functioning I will share it. The solution I'm building is ultra specific to the property restoration industry, but if nothing else, maybe it helps spark another idea for someone. I will stay in touch.