Answered

Get Items with ODATA Query Suddenly Failing

(0) Share

Report

Posted on by 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

Categories:

Using connectors

I have the same question (0)

All responses (11)

Answers (1)

Gristy 2,429 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
gamoraes 1,040 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Gristy 2,429 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cripp88 72 on at

Like (0)

Report

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.

Was this reply helpful? Yes No