Answered

Adjust LookUp on QR Scan and Patch

(0) Share

Report

Posted on by scottf1

Hi there!

I'm still new to PowerApps and working on a QR scanner to use as a time clock for our team. Thanks to the help of the wonderful people on this forum we have got this working 😀

At present we issue a QR code to everyone which splits into Name;WorkArea;ID on scan and using one button in PowerApps to "Clock In" and "Clock Out" everyone.

One issue I would like to learn how to work around is what happens if someone clocks in or out multiple times in a day e.g. they have scanned in to start the day then scanned out and then have had to come back to work. At present when someone "Clocks In" they create a new Sharepoint record - when they "Clock Out" the LookUp works brilliantly and records the time in "Clock Out Time Stamp". However if they scan the QR Code again after a time already exists in the "Clock Out Time Stamp" on the same day it will just flat out overwrite today's record with an updated time and I need it to create a whole new entry in Sharepoint.

I've tried a few things but I'm lost as to how to get this to happen around everything else...if anyone could point me in the right direction that would be extremely helpful!

Screenshots of columns in Sharepoint with examples - OnScan of the BarcodeReader current formula below. Any help would be massively appreciated and thanks in advance to anyone who takes a look!

With(
{
_Title:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),1
)).Value,
_Area:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),2
)).Value,
_HoddyID:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),3
)).Value
},
With(
{
_Exist:
LookUp(
'Ahoy Staff 2024',
Title = _Title && 'Work Area' = _Area && HoddyID = _HoddyID && DateOnly = Today()
)
},
If(
!IsBlank(_Exist.ID),
Patch(
'Ahoy Staff 2024',
{ID: _Exist.ID},
{'Clock Out Time Stamp': Now()}
),
Patch(
'Ahoy Staff 2024',
Defaults('Ahoy Staff 2024'),
{
Title: _Title,
'Work Area': _Area,
HoddyID: _HoddyID,
DateOnly: Today(),
'Clock In Time Stamp': Now()
}
)
)
)
);
Navigate(Successimo);
Set(
startTimer2,
true
)

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

elseb 774 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

You could check if user the record doesn't exist or if both date columns are populated on the last created record for the user operating the app, and if that comes back true create new record, else update the record with clock out date.

Seb

Was this reply helpful? Yes No
scottf1 25 on at

Like (0)

Report
Copy link

Link copied!

Hi @elseb,

Thanks for that - just looking for some suggestions on how I actually write that formula?

Thanks!

Was this reply helpful? Yes No
elseb 774 Moderator on at

Like (0)

Report
Copy link

Link copied!
Patch button:

If( IsBlank( LookUp( colClockins, employee_id = Value(TextInput3.Text) && IsBlank(date_out) ) ), Patch( colClockins, Defaults(colClockins), { employee_id: Value(TextInput3.Text), date_in: Now(), is_clockin: true } ), Patch( colClockins, LookUp( colClockins, employee_id = Value(TextInput3.Text) && IsBlank(date_out) ), { date_out: Now(), is_clockin: false } ) )

my database:

ClearCollect( colClockins, { employee_id: 1, date_in: DateValue(""), date_out: DateValue(""), is_clockin: true }, { employee_id: 1, date_in: DateValue(""), date_out: DateValue(""), is_clockin: false } )

The employee_id is taken from text input called:

TextInput3

You can replace the source of the employee_id or whatever you use to track the person with your data, and adjust the code to your needs but the above is tested and works.

Hope this helps

Was this reply helpful? Yes No
scottf1 25 on at

Like (0)

Report
Copy link

Link copied!

Hi @elseb ,
I've spent a few days trying to get this and can't get this to work unfortunately.
Part of this issue seems to be that the Patch button is also the same button I have splitting the QR code before we start patching and LookUp and it just starts putting blank entries into Sharepoint.
Any other thoughts?
Thanks!

Was this reply helpful? Yes No
elseb 774 Moderator on at

Like (0)

Report
Copy link

Link copied!

have you used the code I wrote for you to test the functionality? Just copy and paste what I gave you into fresh screen and see how it works.

The important thing with any even remotely complex tasks is to break it down and make small adjustments, If you not entirely sure what my code does test it first and analyse then replace section by section with your data.

Was this reply helpful? Yes No
Verified answer

scottf1 25 on at

Like (0)

Report
Copy link

Link copied!

Hi @elseb

I certainly tried using your whole code with no success - however, what I did find from your code is exactly what I needed ( a modified version on mine of &&IsBlank('Clock Out Time Stamp') and your suggestion of making small adjustments. Everything is working how I wanted it. Thank you so much for the help - I really appreciate it!!!!!!! Code below -
With(
{
_Title:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),1
)).Value,
_Area:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),2
)).Value,
_HoddyID:
Last(FirstN(
Split(
First(BarcodeReader1_1.Barcodes).Value,
";"
),3
)).Value
},
With(
{
_Exist:
LookUp(
'Ahoy Staff 2024',
Title = _Title && 'Work Area' = _Area && HoddyID = _HoddyID && DateOnly = Today() && IsBlank('Clock Out Time Stamp')
)
},
If(
!IsBlank(_Exist.ID),
Patch(
'Ahoy Staff 2024',
{ID: _Exist.ID},
{'Clock Out Time Stamp': Now()}
),

Patch(
'Ahoy Staff 2024',
Defaults('Ahoy Staff 2024'),
{
Title: _Title,
'Work Area': _Area,
HoddyID: _HoddyID,
DateOnly: Today(),
'Clock In Time Stamp': Now()
}
))

)
)
;
Navigate(Successimo);
Set(
startTimer2,
true
)

Was this reply helpful? Yes No