FLOW (Get Items) Filter SharePoint list on Date

[Shash]

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.

[v-xida-msft · Accepted solution]

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:

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:

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

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

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

[Shash]

Thanks Kris, it worked.

[RKushner]

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!!!

[dsforman12]

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"
}

[DavesTechTips]

Hello

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

Let me know if you don't come right.

[KarlChristopher]

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

[MiraGhaly]

@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

[gizmoburdine]

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

[RobertPC77]

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

[KarlChristopher]

Hi you need to ' before and after the utcNow function

[DavesTechTips]

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.

[RobertPC77]

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

Thank you all!

[jamesefraserbb]

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

[brandontate]

Was there an answer to this? 

My current situation is doing the same thing...

Thanks,

Brandon 

[ScottShearer]

@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.

[Alejandrogav118]

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 🙂

[AM0002]

I forget single quotes EVERY time. Thanks!