cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shash
New Member

FLOW (Get Items) Filter SharePoint list on Date

I have question on the filter, the SharePoint list Filter Query I want to check if DueDate is greater than today, where DueDate is date column.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Shash,

 

Could you please share a screenshot of your flow's configuration?

Do you want to filter items of your SharePoint list with the OData query in Filter Query field of "Get items" action?

 

I have made a test on my side and please take a try with the following workaround:2.JPG

Within Filter Query field of "Get items" action, type the following formula:

DueDate gt 'utcNow(...)'

The utcNow(...) is a WDL expression, which is wrapped with single quotes. Within right panel, select Expression tab, type the following formula:

utcNow('yyyy-MM-dd')

 

The flow works successfully as below:3.JPG

 

 

In addition, on my side, the DueDate is a Date and Time type column and the Date and Time format is Date only:4.JPG

If the Date and Time format of your DueDate column is Date & Time, please take a try with the following workaround:5.JPG

 

More details about the WDL expression in Microsoft Flow, please check the following article:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

16 REPLIES 16
v-xida-msft
Community Support
Community Support

Hi @Shash,

 

Could you please share a screenshot of your flow's configuration?

Do you want to filter items of your SharePoint list with the OData query in Filter Query field of "Get items" action?

 

I have made a test on my side and please take a try with the following workaround:2.JPG

Within Filter Query field of "Get items" action, type the following formula:

DueDate gt 'utcNow(...)'

The utcNow(...) is a WDL expression, which is wrapped with single quotes. Within right panel, select Expression tab, type the following formula:

utcNow('yyyy-MM-dd')

 

The flow works successfully as below:3.JPG

 

 

In addition, on my side, the DueDate is a Date and Time type column and the Date and Time format is Date only:4.JPG

If the Date and Time format of your DueDate column is Date & Time, please take a try with the following workaround:5.JPG

 

More details about the WDL expression in Microsoft Flow, please check the following article:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks Kris, it worked.

Man Embarassed This isn't working for me. Is it possible that MS has broken another thing that used to work??

 

Here's my Filter Query and error message:

 

ExpiryDate le fx addDays(utcNow('yyyy-MM-dd'),10,'yyyy-MM-dd')

 

I know that addDays(utcNow... is working from the error:

 

The expression "ExpiryDate le 2018-06-22" is not valid. clientRequestId: 32c616f1-365e-4b8d-b0e7-1913c661f5b5 serviceRequestId: f766709e-8023-5000-d589-a40ee4e3f486

 

I have also reverified that ExpiryDate (the field name, not the label, but I have also tried with that, Expiry_x0020_Date) is a date with format Date Only.

 

Many thanks!!!

bump.  also receiving the same error message.  

 

{
  "status"400,
  "message""The expression \"Created gt 2018-08-01T08:54:02.0000000\" is not valid.\r\nclientRequestId: 61e52dea-2ef9-4aa5-af86-47351aa81dec\r\nserviceRequestId: 5f89809e-40cd-6000-0c69-c797a2814523"
}

Hello

 

If you still get this error, please remember the single quotes around the expression.

 

Let me know if you don't come right.

2018-08-06_18-54-13.jpg

image.png

Yep, I can agree that the dateTime format in sharepoint does not work and returns a 400 error code.

@KarlChristopher

Actually the problem will be in the field name so try to get all items and check the field name mine was End Date and realized it is ODATA_EndDate

So I spent a day beating my head against a wall on this.  When using Flow ODATA filters you need to use sharepoint internal field names.  I don't know exactly how these are created but when you have field names in sharepoint with spaces and other characters it will change the internal field name.

'Expiration Date' for me became 'Expiration_x0020_Date0'

to get this name I had to go to sharepoint and edit the column and then look at the url to get the internal field name

https://sharepoint.com/_layouts/.......................%7D&Field=Expiration_x0020_Date0

 

I hope this helps someone else

I'm having trouble with the same thing... see my Flow below and feel free to provide help.

 

3.png

Hi you need to before and after the utcNow function

Hi @RobertPC77, As KarlChristopher suggested, you are missing some single quotes around the date value. The below video discusses the topic in quite a lot of detail: https://youtu.be/m_j_xN7-LjU

 

Twitter: @DavesTechTips

YouTube: https://www.youtube.com/davestechtips

**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Great Suggestions! and the video was very helpful as well!  I'll now try to get the rest of it going.

 

Thank you all!

Saving others copy-typing the string from the screenshot in the accepted solution, for the "If the Date and Time format of your DueDate column is Date & Time" section:

 The expression is:

utcnow('yyyy-MM-ddTHH:mm:ssZ')

A similar use case is to query on a specific date (converting it to datetime) rather than today, which can be achieved with this expression (for 1 May 2019 in this example)

formatDateTime('2019-05-01','yyyy-MM-ddTHH:mm:ssZ')

 

Was there an answer to this? 

My current situation is doing the same thing...

 

Thanks,

Brandon 

@brandontate 

Can you post a new question on this please.  This is a long thread where a number of issues have been raised.  For clarity, it would be best to start fresh with a new post.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

had exactly same problem, in myone we changed the field name from Date to Fatigue_Date on sharepoint. However when using power automate on the GET ITEMS output on the flow history, the response from from API still had Date as the column name.

 

Thats bizarre, but hey your comment helped find the real Column name 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,956)