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

17 REPLIES 17
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.

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 🙂

I forget single quotes EVERY time. Thanks!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,925)