When building an asset reservation system in SharePoint Online, you may have a need to prevent duplicate reservations on a SharePoint calendar. There are several popular solutions for this requirement, but I prefer to use Power Automate with the 'Get Items' action and a filter query.
Most reservation systems I have seen in SharePoint today basically use two lists -- one for capturing each reservation request and another list for the reservation calendar. Each request form submission triggers a Power Automate flow that uses the 'Get Items' action with a filter query to check existing items on the reservation calendar that contain the same values for one or more columns submitted in each request, before scheduling the new request.
I initially struggled with defining the appropriate logic for the 'Get Items' filter query, but I eventually determined the key point to remember is the fact that each new request will always have a start and end date. Therefore, the filter query only needs to retrieve the existing calendar items that currently exist BETWEEN the proposed start and end dates of each new request. Here is the formula I recommend for the 'Get Items' filter query.
StartDateandTime (from Calendar) le (less than or equal to) Return Date (from Request) AND EndDateandTime (from Calendar) ge (greater than or equal to) Departure Date (from Request)
Of course, the next step in the flow (after the Get Items action) should be a Condition that uses an expression to see if the output of the previous 'Get Items' action contains one or more existing items in the calendar list. See below. If there are any duplicate items identified, you can then define the next action in your flow.
length(outputs('Get_items')?['body/value']) 'is greater than or equal to' 1
Hope this helps anyone looking to prevent duplicate items on a SharePoint calendar.
