Get Items with ODATA Query Suddenly Failing

[Cripp88]

Hi all,

I have a Flow that runs daily and uses several SharePoint Get Items actions, each with a different ODATA query. The actions have been returning data as expected for around a month, but now have started to not pull data for the last two days.

The queries reference a date column in a SharePoint list. These are from today's run, all from the same run:

• Short_x0020_Date eq '9/13/2019' works and pulls all items with this date
• Short_x0020_Date gt '9/15/2019' pulls data from 15th Sep 2019 to 17th Sep 2019
• Short_x0020_Date eq '9/20/2019' returns nothing

The actions do not fail, and provide outputs, and the rest of the Flow runs as normal.

There is data in the list, all dates in this field are of the same format. I have items with Short_x0020_Date of:

• 9/15/2019
• 9/16/2019
• 9/17/2019
• 9/18/2019
• 9/19/2019
• 9/20/2019

[Gristy]

Hey

The first thing i always do is try the OData query via the web browser using the rest endpoint, have you had success there with your queries?

[v-litu-msft]

Hi @Cripp88,

Did you test that gets the items without any filter query and could find the items with '9/20/2019' in the output?

I am not sure, maybe it is a time issue. If you could give more information about the formatDateTime() expression, it's better to assist.

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

[Cripp88]

Hi @v-litu-msft,

Thanks for the advice, I tried it without the query filter and it pulled all items (up to the threshold). The expression is:

formatDateTime(addDays(utcNow(), -1), 'M/d/yyyy')

As you can see from the above screenshots it produces the correct format to match the data.

One thing I have just noticed, is that if I filter the column in web view, I don't see values after 9/18/2019, despite them being there:

It was after 9/18/2019 that I had issues, so this looks like it is linked. Any idea?

[gamoraes]

Hi @Cripp88 .

The OData from dates requires that date format be - yyyy-MM-dd

The correct expressions is:

• Short_x0020_Date eq '2019-09-13'
• Short_x0020_Date gt '2019-09-15'
• Short_x0020_Date eq '2019-09-20'

Best Regards

[Cripp88]

Hi @gamoraes,

Thanks for your reply. I don't think this matters as I've just run a test and obtain the same results using the different date format.

I would imagine that this is due to the underlying date value to be the same regardless of the format.

In the screenshot below, when testing with the new format, I can obtain data before 9/18/2019 but not after (sorry about the resolution):

[Gristy]

Hey

In that last screenshot you are using eq (Equals). If you use Equals you need to match the time as well as the date, as all SharePoint fields have time whether they are selectable by the user or not.

Your description of the last screenshot is different though saying you should be querying using ge i.e. greater or equal.

Cheers

Chris

[v-litu-msft]

Hi @Cripp88,

I think the issue may be caused by the SharePoint list that could not get the items of some date column's value. Did the 

It's not a good solution (if the data in the SharePoint list is important and numerous) that you could copy the SharePoint list via copy-list-in-sharepoint-to-office365-tool. Or you can create a similar SharePoint list to test it.

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

[Cripp88]

Hi @Gristy,

The values being saved to the list in Short_x0020_Date are date only, so the timestamp will be 00:00:00, right?

When using OData query, without supplying a timestamp, this should also default to 00:00:00. So the lookup and match values should be the same.

This Flow has been working for some time, so why would time suddenly become an issue?

It can't be a coincidence that it stopped working on 9/19/2019 and 9/19/2019 onwards isn;t shwon when manually filtering in web view.

[Cripp88]

Hi @v-litu-msft,

Did some of your post get missed off?

Anyway I am going to create a test List and add some dates in and query it, I'll report back in an hour or so.

[Cripp88]

Hi,

Having set up a test List with the same column format and inputted the dates I am having issues with, executing the Odata query in exactly the same format returns data.

By my reckoning, either it's an unknown ceiling with querying a SharePoint List of a particular size, or it's a bug.

[Cripp88 · Accepted solution]

@v-litu-msft 

Adding the filter column as an indexed column fixed the issue.

I followed the first answer on the below post:

https://sharepoint.stackexchange.com/questions/214795/when-sharepoint-list-with-5000-items-queried-w...

I think this issue is similar to this one (only difference is Get Files rather than Get Items):

https://powerusers.microsoft.com/t5/Building-Flows/Filter-OData-in-SharePoint-Get-Files-action/td-p/...

Anyway, it doesn't seem to be mentioned in documentation or well known amongst support on this site, so could be worth adding to the SharePoint OData docs.

Thanks