Get Items - Filter Query by choice and date columns

[TarenBhutch]

Hi,

I am relatively new to writing queries in automate.

I would like to create a scheduled flow to check a SharePoint Online list each morning. This will check all items within the list to determine if the status is open and the review date is tomorrow’s date. If these match, then an email is sent to the user’s email.

In Filter Query I have added the below:

Status eq 'Open' "and" Review Date eq @{addDays(utcNow(),-1,'yyyy-MM-dd')}

I believe I haven’t written the formula correctly and would like your help understanding how to do this.

Thank you 🙂

[MarvinBangert · Accepted solution]

Hey @TarenBhutch 

This is what I can see from your code what probably will not work:

1. Always use the SharePoint internal field names, not the display names. You can find the internal field names, when you open the settings of your SharePoint list, select the column and check the URL, in the end there should be something "...&Field=<Your internal field name>". As always in SharePoint: Try avoiding special characters and spaces, this will make your odata query easier. If you have a space in your internal name like 'Review Date' you will probably see %20, but this doesn't work in Power Automate, so you would need _x0020x_ so your filter in the action would be 'Review_x0020x_Date'.

2. The 'and' doesn't need any double quotes, this will probably cause an error. You can just write and in your query.

3. The date also needs some single quotes like: eq 'addDays()'.

4. using eq (equals) within a string is fine, because we only have one value that matches. Using eq within date is quite difficult, because even if you only have see a date like 2022-01-21, in the background there is also always a time, usually saved as 00:00:00 if you select "only date". Your formular would look like this:

utcNow() = 2022-01-21T10:42:39Z, addDays -1 = 2022-01-20T10:42:39Z, if you compare this to the Date column it would be: Review Date = 2022-01-20T00:00:00Z

The query would look like:

2022-01-20T00:00:00Z eq 2022-01-20T10:42:39Z (so it's not matching and you will not receive this item)

You need to use ge (greater or equal) gt (greater than) lt (less than) le (less or equal).

So you formular would be

2022-01-20T00:00:00Z ge 2022-01-20T10:42:39Z (yes, 00 is greater than 10 am, so this would met the requirements).

5. For better overview, you can use brackets to split your code into the different queries you want to combine

The filter uses the ODATA query opertions from the SharePoint REST API, you can find some more information here: Use OData query operations in SharePoint REST requests | Microsoft Docs

Also another good article is this: Power Automate SharePoint Get Items Filter Query + 12 Examples - EnjoySharePoint

I would use the following code for the filter (without testing it):

(Status eq 'Open') and (Review_x0020x_Date ge '@{addDays(utcNow(),-1,'yyyy-MM-dd')}')

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel