I'm having trouble setting up an automated flow in SharePoint to update values every 15 minutes, ensuring that all items whose 'Start: Date/Time' and 'End: Date/Time' fields match the current time transition from 'OPEN' to 'CLOSED' appropriately. For example, if an event like 'Registration Classes' starts on May 31 at 12:00AM and ends on May 31 at 12:15AM, it should remain 'OPEN' until 12:15AM, when it transitions to 'CLOSED', reflecting the current Eastern Standard Time (EST).
@Inogic, I've been attempting to use your suggested filter query, but unfortunately, it's still not functioning as expected. Thus, I'd like to implement a solution based on the current time.
For example, let's consider the scenario where, as of May 30 at 5:00 PM, an event titled 'Add and Drop Classes' has been added. Its start date/time is set for May 30 at 5:30 PM, and its end date/time is May 30 at 5:45 PM. Presently, the status is 'CLOSED', and it should automatically transition to 'OPEN' when it reaches 5:30 PM. This transition should occur seamlessly since the flow is already configured to update every 15 minutes.
Hi,
As per our understanding, you're looking to transition the status to 'Closed' when the 'End: Date/Time' matches or is earlier than the current time.
So first you need to get items which have 'End: Date/Time' less than or equal to current date and Status equals to Open.
You can try with the filter query is as follows to get items that are Open and EndDate is older than the current time:
EndDateTime i.e. '@{formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ssZ', 'UTC')}' and Status eq 'Open'
then you can add a step to update Status of item to the 'Closed'.
We have tried the same and able to get the items with condition and change the status to Closed for retrieved records.
Hope this helps.
Thanks!