cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cripp88
Level: Powered On

Get Items with ODATA Query Suddenly Failing

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

flow1.PNGflow2.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Cripp88
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

@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

View solution in original post

11 REPLIES 11
Gristy
Level 10

Re: Get Items with ODATA Query Suddenly Failing

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?

Community Support Team
Community Support Team

Re: Get Items with ODATA Query Suddenly Failing

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
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

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:
 
flow issue 3.PNG
 
It was after 9/18/2019 that I had issues, so this looks like it is linked. Any idea?
Super User
Super User

Re: Get Items with ODATA Query Suddenly Failing

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


Did I answer your question? Mark my post as a solution!

Gustavo Moraes, o astronauta brasileiro do Flow!


Cripp88
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

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

 

flow issue 4.PNG

Gristy
Level 10

Re: Get Items with ODATA Query Suddenly Failing

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

Community Support Team
Community Support Team

Re: Get Items with ODATA Query Suddenly Failing

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
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

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
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

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
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

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.

 

flow issue 5.PNGflow issue 6.PNG

Highlighted
Cripp88
Level: Powered On

Re: Get Items with ODATA Query Suddenly Failing

@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

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 403 members 5,782 guests
Please welcome our newest community members: