I have built a timesheet app in PowerApps with SQL Server as the DB to use as lookup and record data from the mobile pront end used by our technicians.
In the app I have used various controls and status to ensure that only today roster can be used. This works very well for the regular day to day rostered time between 04:00 and 18:00. (for example I use a form lookup as: LookUp('[dbo].[PSTSheet]', EmployeeId_1 = Value(TextInput23.Text) && Tjobno = Value(TextInput22.Text) && Text(tdate,"[$-en-US]yyyy-mm-dd") = Text(tdate, "[$-en-US]yyyy-mm-dd")) This returns a status for me to test that the timesheet data has been entered today.
I now find that we are starting to roster some work from around 22:30 to 04:00, which now presents me with a different problem. The company wants this to be one time block however my app tests for today() for the start of the rostered time. The issue become relevant when they go to sign off at 04:00 and SQL SERVER finds no record for today in the table. (based upon the above LOOKUP)
Yes we could roster it in two blocks 22:30 to 23:59 and then 0:00 to 0:400, but technicians are not likely to stop work at 23:59 to finish one roster and start a new roster for the new day. So this is not a preferred option.
I still want to preserve the lookup to ensure that only roster entries within the last 24 hours are checked.
Any ideas or thoughts on how to overcome a roster across 2 days would be grateful.
have you considered working with the DateDiff function and base your evaluation on the minutes or hours units ?
depending on your business rules, you may have to do a combination of evals, based on the current time of the day, but if in facts, the real constraint is "within the last 24 hours", then eval just the difference.