Daily Checklist / Calculated Column

Matt Capp

Aug '24 • Stuck or Need Help

Hey all,

Working on building a daily checklist for co-workers on Power Apps. A quick rundown of the SharePoint List setup:

List name: Master_Checklist

Task (Title Field)

StartTime (Date and Time)

HoursAvailable (whole Number)

EndTime (Calculated Column, =StartTime+(HoursAvailable/24) (this seems to be working as intended)

I'm working on making a 'refresh' button which finds all the tasks where the EndTime has past (EndTime<=Now()) and updating their StartTime so that the tasks become available again, with StartTime at the beginning of the shift.

First, I've never used a calculated column before, I usually just use a Date and Time field, and set the dates manually. Using this field type in my filter, I have to run EndTime through DateTime(Year(EndTime), Month(EndTime) ect...) which gives me a delegation warning (not worried as it is in a local collection and the task list is less than 100 items)

Second, after I build my collection 'TasksToUpdate' I'm using ForAll to lookup each record in the collection and Patch to update the StartDate. I think Filter logic is sound, but I keep getting an error: 'Status: 400, fetching items failed, possible invalid string in filter query' I don't have any errors in my code and App Checker has no errors on this screen.

The only kind of sketchy part of my code is:

ForAll(TasksToRefresh, Patch(Master_Checklist, LookUp(Master_Checklist, ID=ThisRecord.ID), If(Hour(ThisRecord.EndTime)>Hour(Now()), {StartTime: DateTime(Year(Today)), Month(Today()), Day(Today()), Hour(ThisRecord.StartTime), Minute(ThisRecord.StartTime), 00})

I'm fairly confident in the rest of the code, but I'm not the 'If(Hour(ThisRecord.EndTime)...' portion is working correctly because of some weirdness from calculated columns.

Any help is appreciated!

3 comments