Help filter SharePoint ODATA for date range booking

[Schwartz_Cory]

Hi,

I am struggle to figure out the logic to get the results I need. We are trying to start a car booking feature, and I am trying to prevent people from booking a car that has already been booked. 

Here is the UI for the user:

As you can see, Jan18-20 was booked and should not have been. Here is what I have been doing for testing but not sure what I should change to get my desired results.

In my flow, I am using a get items with an ODATA filter. If the list returns empty, that should mean it is available to book. If the list returns a value, then that should mean it is not available to book.

I've spent too much time on this and could really use the help.. 

Thanks in advance 🙂

[creativeopinion · Accepted solution]

@Schwartz_Cory 

Manual Trigger

I'd recommend creating a new Manually triggered flow so you can quickly run tests and build your flow out. I'm assuming (I can't tell) that your flow trigger is When a New Item is created. By using a manual trigger you don't need to leave Power Automate to trigger your flow. You can trigger it right from inside the flow. 

I would also recommend adding a Compose action to store a vehicle value you can use for testing purposes. Once your flow is ready to go, you can replace the hard coded value with the appropriate dynamic content. 

Compose the Date Inputs (optional)

This step is optional, you could just insert the dynamic content from the flow trigger directly into the Filter Query. However, by using Compose actions instead, you don't need to edit the Filter Query, just insert the appropriate Dynamic content into their respective Compose actions. 

I've also grouped the Compose actions inside a Scope action to quickly collapse them.

Compose the Filter Query

I've made an adjustment to the the Filter Query. This query will check:

• The new start date is before or on the existing end date, AND
• The new end date is after or on the existing start date.

Return Count of Items

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. I have an Approved item in my SP list with a Start Date of Jan 17th - Jan 19th. I ran the flow with a Start Date of Jan 18 and End Date of Jan 20th.

The Filter Query returned one item.

The best part of this flow is that you can quickly test different start/end date selections to ensure the Filter Query is correct. 

Now you can add your Condition check.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this tutorial I cover:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

[v-jefferni]

Hi @Schwartz_Cory ,

The Odata filter query you wrote was a bit inaccurate. To check if there is an overlap, you need use below condition:

('StartDate' gt EndDate or 'EndDate' lt StartDate)

Best regards,