cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TarenBhutch
Frequent Visitor

Get Items - Filter Query by choice and date columns

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')}

 

TarenBhutch_0-1642727792868.png

 

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

 

Thank you 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
MarvinBangert
Super User
Super User

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

View solution in original post

1 REPLY 1
MarvinBangert
Super User
Super User

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

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (3,541)